need help in foreign key in sql server

Question

3.00/5 (1 vote)

See more:

SQL-server-2000

I define a primary key on column and I define foreign key in the another table. If I enter null value in the foreign key column it is showing the error.How to allow the null value in the foreign key?

Posted 27-Apr-13 4:19am

kalisiddayya

Updated 27-Apr-13 9:58am

RedDk

v2

Add a Solution

Comments

Zoltán Zörgő 27-Apr-13 10:50am

Why haven't you posted "the error" too?
Anyway: you can have foreign key field with null values, that's no problem. So check the column itself: have you allowed nulls?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PrissySC · Answer 1 · 2013-04-28T01:30:00

I recently had the very same problem in linq. I had another column that I could declare unique. I allowed the column to have null values, and thus, I could use it as a foreign key.

Often, you tend to grab the primary key, but when you do so, you must realize that the foreign key and the column in the secondary table cannot be null. The two must match in all respects.

CHill60 · Answer 2 · 2013-04-28T02:06:00

*Nodding to Zoltán Zörgő*
It's almost certainly down to your table definition ... best demonstrated with an example ...

SQL

-- This version will FAIL if there is a null in FKID
create table parent
(
	ID int IDENTITY(1,1) NOT NULL,
	SomeData varchar(20),
	CONSTRAINT PK_parentID PRIMARY KEY CLUSTERED (ID)
)
-- Insert some sample data
insert into parent values('parent 1')
insert into parent values('parent 2')
insert into parent values('parent 3')

create table child
(
	ID int IDENTITY(1,1) NOT NULL,
	SomeChildData varchar(20),
	FKID int NOT NULL,
	CONSTRAINT PK_childID PRIMARY KEY CLUSTERED (ID), -- Primary key on this table
	CONSTRAINT FK_ChildToParent FOREIGN KEY (FKID) REFERENCES parent -- Foreign key to parent
)
--insert some sample data
insert into child values('child 1.1', 1)
insert into child values('child 1.2', 1)
insert into child values('child 2.1', null)

Output

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'FKID', table 'child'; column does not allow nulls. INSERT fails.
The statement has been terminated.
(0 row(s) affected)

Whereas ...

SQL

drop table child -- note the order I have to drop the tables
drop table parent

-- This version will WORK if there is a null in FKID
create table parent
	(
	ID int IDENTITY(1,1) NOT NULL,
	SomeData varchar(20),
	CONSTRAINT PK_parentID PRIMARY KEY CLUSTERED (ID)
)
-- Insert some sample data
insert into parent values('parent 1')
insert into parent values('parent 2')
insert into parent values('parent 3')
create table child
(
	ID int IDENTITY(1,1) NOT NULL,
	SomeChildData varchar(20),
	FKID int,
	CONSTRAINT PK_childID PRIMARY KEY CLUSTERED (ID), -- Primary key on this table
	CONSTRAINT FK_ChildToParent FOREIGN KEY (FKID) REFERENCES parent -- Foreign key to parent
)
--insert some sample data
insert into child values('child 1.1', 1)
insert into child values('child 1.2', 1)
insert into child values('child 2.1', null)
select * from parent
select * from child

Output

ID  SomeData
1   parent 1
2   parent 2
3   parent 3

ID	SomeChildData	FKID
1	child 1.1	1
2	child 1.2	1
3	child 2.1	NULL

Here is also a link to the MSDN documentation http://msdn.microsoft.com/en-us/library/ms189049(v=sql.110).aspx[^]

need help in foreign key in sql server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0