Is it possible to allow NULL values in foreign key?

_{Saturday, October 25, 2008 4:24 AM}

Is it possible to allow NULL values in foreign key?

_{Saturday, October 25, 2008 12:48 PM ✅Answered | 2 votes}

Yes you can.

Code Snippet

CREATE TABLE Customer (

CustID INT IDENTITY PRIMARY KEY,

CustName VARCHAR(40)

)

GO

CREATE TABLE Sales (

SalesID INT IDENTITY PRIMARY KEY,

CustID INT NULL FOREIGN KEY REFERENCES Customer (CustID)

)

GO

INSERT INTO Sales (CustID) SELECT 1

-- This will fail

INSERT INTO Sales (CustID) SELECT NULL

-- this will succeed

_{Saturday, October 25, 2008 1:50 PM ✅Answered}

Yes but it's not necessary to do so. To take Jacob's example:

CREATE TABLE Customer (
CustID INT IDENTITY PRIMARY KEY,
CustName VARCHAR(40)
);

CREATE TABLE Sales (
SalesID INT NOT NULL PRIMARY KEY
);

CREATE TABLE CustomerSales (
SalesID INT NOT NULL PRIMARY KEY REFERENCES Sales (SalesID),
CustID INT NOT NULL FOREIGN KEY REFERENCES Customer (CustID)
);

_{Friday, February 10, 2012 3:01 PM ✅Answered}

Is it possible to allow NULL values in foreign key?

Yes if the FOREIGN KEY column is NULLABLE. The PRIMARY KEY column must be NOT NULL.

Example - ProductSubcategoryID is an FK:

SELECT ProductName=Name, ProductSubcategoryID
FROM AdventureWorks2008.Production.Product
/*
....
ProductName ProductSubcategoryID
Tension Pulley  NULL
Rear Derailleur Cage    NULL
HL Road Frame - Black, 58   14
HL Road Frame - Red, 58 14
.... */

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

_{Saturday, October 25, 2008 12:53 PM}

Yes in a Foreign Key you can have values that are in the referenced Primary Key and NULL.

Apart from thin I will also suggest that you read about the SQL server Keys here

Foreignkey

http://www.answers.com/topic/foreign-key

Primary key

http://www.answers.com/topic/unique-key

_{Thursday, April 8, 2010 12:34 PM}

i have parent table with primary key
in child table i have a column(which is not primary key of child table) data type is int foreign key to parent table, in some situations i need to allow null values in child table.

It is giving me error while establish foreign key,
after googleing i got

1. "ON UPDATE/DELETE SET NULL".
2. define multiple constraints on a single column.

am a c# programmer, i can't able to move ahead,

plz help me

_{Thursday, April 8, 2010 12:39 PM}

Urgent help Nullable foreign key

i have parent table with primary key
in child table i have a column(which is not primary key of child table) data type is int foreign key to parent table, in some situations i need to allow null values in child table.

It is giving me error while establish foreign key,
after googleing i got

1. "ON UPDATE/DELETE SET NULL".
2. define multiple constraints on a single column.

am a c# programmer, i can't able to move ahead,

plz help me

create table #temp (id int identity, val int, val2 as isnull(val, -id) unique)

insert into #temp values (null)
insert into #temp values (null)
insert into #temp values (1)
insert into #temp values (2)
insert into #temp values (2)

select * from #temp

http://www.t-sql.ru

_{Thursday, April 8, 2010 12:42 PM}

Urgent help Nullable foreign key

i have parent table with primary key
in child table i have a column(which is not primary key of child table) data type is int foreign key to parent table, in some situations i need to allow null values in child table.

It is giving me error while establish foreign key,
after googleing i got

1. "ON UPDATE/DELETE SET NULL".
2. define multiple constraints on a single column.

am a c# programmer, i can't able to move ahead,

plz help me

r u trying to create foreign key on the child table in which data already exists?

_{Friday, April 9, 2010 6:51 AM}

Thanks for replying

Child table data = ((parent table primary key) data +null values)

_{Friday, April 9, 2010 7:00 AM}

Just make sure the FK column allows NULLs. In the following demo, the ProductSubcategoryID is NULL for parts which are not for sale.

SELECT ProductName=Name, FK=ProductSubcategoryID 
FROM AdventureWorks2008.Production.Product
/*
ProductName FK
....
Top Tube    NULL
Tension Pulley  NULL
Rear Derailleur Cage    NULL
HL Road Frame - Black, 58   14
HL Road Frame - Red, 58 14
Sport-100 Helmet, Red   31
Sport-100 Helmet, Black 31
....
*/

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

_{Friday, April 9, 2010 7:11 AM}

Thanks for replying

Child table data = ((parent table primary key) data +null values)

I did some R&D now .below are some points i found

1.if the child table contains any value for the foriegn key column other than existing values in parent table,

it throws error(referential integrity error)

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_T2_T1". The conflict occurred in database "test", table "dbo.T1", column 'C1'.

2.U can have duplicates of values in the child table as long as they are subset of parent table

3.u can have as many NULL values as possible as long as the column property is set to "Allow Nulls"

4.After creating foriegn key successfully, you can not change the NULL value in the child table to any value!!

_{Friday, April 9, 2010 7:26 AM}

Sorry boss, i m not able to understand,

my question is

i have parent table with primary key
in child table i have a column(which is not primary key of child table) data type is int foreign key to parent table, in some situations i need to allow null values in child table.

in child table i have data ((parent table primary key column values) data +null values)

_{Friday, April 9, 2010 7:29 AM}

parent is profile table, child table is connectionRequest

and my error is:as follows

'profile' table saved successfully
'connectionRequest' table

• Unable to create relationship 'FK_connectionRequest_profile'. 
  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_connectionRequest_profile". The conflict occurred in database "mp", table "dbo.profile", column 'profile_id'.

_{Friday, April 9, 2010 7:54 AM}

i wrote  query

select C.c1,P.p1 from parent P right outer join  child C  on C.c1=P.p1 where P.p1 is null

this query gives records extra in child table. it is showing all null values in both columns

plz try this

_{Friday, April 9, 2010 8:13 AM}

i wrote  query

 

select C.c1,P.p1 from parent P right outer join  child C  on C.c1=P.p1 where P.p1 is null

 

this query gives records extra in child table. it is showing all null values in both columns

plz try this

if u are sure of this then create fk like this

DROP TABLE t2
DROP TABLE t1
GO
CREATE TABLE t1(col1 int NOT NULL PRIMARY KEY)
CREATE TABLE t2(col1 int NOT NULL)

INSERT INTO t1 VALUES(1)
INSERT INTO t2 VALUES(2)

ALTER TABLE t2 WITH NOCHECK ADD CONSTRAINT fk_t2_t1 FOREIGN KEY(col1) REFERENCES t1(col1)

_{Friday, April 9, 2010 8:54 AM}

 Thanks for ur reply

we can find out

extra rows in child table by using this query

SELECT * FROM <foreign_key_table> WHERE <foreign_key_column> NOT IN
(SELECT <primary_key_column> FROM <primary_key_table>);

_{Friday, April 9, 2010 9:40 AM}

 

 Thanks for ur reply

we can find out

extra rows in child table by using this query

SELECT * FROM <foreign_key_table> WHERE <foreign_key_column> NOT IN
(SELECT <primary_key_column> FROM <primary_key_table>);

so u had invalid entries in child?

_{Friday, April 9, 2010 4:26 PM}

It means you have orphans. You need to correct the problem first. Also you can not have empty values in the FK column, only NULLs will be allowed.Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

_{Saturday, February 4, 2012 11:49 AM}

no.because a primary key cannot be null.a foreign key value can be inserted only if that value is already found in the table .

ex :table students contains 2 columns rollnumber,name(rollnumber primarykey)

table marks contains rollnumber,marks(rollnumber foreign key)

null value cannot be inserted for rollnumber in table students

so a rollnumber that is not in table students should nort have marks so null value cannot be inserted

_{Friday, April 12, 2013 6:37 AM}

http://msdn.microsoft.com/en-us/library/aa933117(v=sql.80).aspx