Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

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
if object_id('tempDB..bobFK') is not null drop table bobFK
if object_id('tempDB..bob') is not null drop table bob
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)
insert into bob select 1
-- succeeds...  SQL allows null key
insert into bobFK select null
-- fails...  SQL will perform a CHECK when not null
insert into bobFK select 2
if object_id('tempDB..bobFK') is not null drop table bobFK
if object_id('tempDB..bob') is not null drop table bob

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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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


© Copyright 2020 SQLTraining Ltd.