Foreign Keys Are Allowed to be Null
Published: Sep 11, 2016
Something that came up the other day when talking to a developer was that they were unsure as to whether or not they could have a foreign key relationship to a table when they didn’t necessarily have a foreign key value for all records.

Well the simple answer is that yes, you can.

The reason this was questioned is because they understood that entering a value into a foreign key column would be checked against the available values in the primary key… but a primary key won’t allow null and therefore surely a foreign key would check the null, find it didn’t exist, and then reject it.

This sounds perfectly reasonable and therefore definitely isn’t a stupid question so I thought I’d include it here.

However what actually happens is that as long as you have the foreign key on a nullable column then SQL Server will only perform the check when the value you are entering is a non-null value.

This can be simply demonstrated by running the following script:

use tempdb
go
if object_id('tempDB..bobFK') is not null drop table bobFK
if object_id('tempDB..bob') is not null drop table bob
go
create table bob
(
      
id int primary key clustered
)
create table bobFK
(
      
id int identity,
      
bobID int null,
      
constraint pk_bobFK primary key clustered(id)
)
alter table bobFK add constraint fk_bob
      
foreign key (bobID) references #bob(id)
go
insert into bob select 1
go
-- succeeds...  SQL allows null key
insert into bobFK select null
go
-- fails...  SQL will perform a CHECK when not null
insert into bobFK select 2
go
if object_id('tempDB..bobFK') is not null drop table bobFK
if object_id('tempDB..bob') is not null drop table bob
go


Therefore if you come across someone asking the same question you can prove to them that foreign keys can be null and you don’t have to have a value for each foreign key record.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron