snippetsqlModerate

How to enforce a nullable foreign key?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

enforceforeignnullablehowkey

Problem

I have a relation between two tables. The foreign key table can have a row related in the primary table. There's a way to enforce the value of the FK column to be NULL or one of the values of the PK column in the primary table?

Solution

Yes, you can allow a foreign key column to be NULL, making it an optional relation.

CREATE TABLE dbo.foo(fooid INT PRIMARY KEY);

CREATE TABLE dbo.bar(barid INT PRIMARY KEY,
  fooid INT NULL FOREIGN KEY REFERENCES dbo.foo(fooid));

INSERT dbo.foo SELECT 1;
INSERT dbo.bar SELECT 1,1;
INSERT dbo.bar SELECT 2,NULL; -- succeeds
GO
INSERT dbo.bar SELECT 3,2; -- fails

If this is not what you meant, then please elaborate or re-word your question.

Code Snippets

CREATE TABLE dbo.foo(fooid INT PRIMARY KEY);

CREATE TABLE dbo.bar(barid INT PRIMARY KEY,
  fooid INT NULL FOREIGN KEY REFERENCES dbo.foo(fooid));

INSERT dbo.foo SELECT 1;
INSERT dbo.bar SELECT 1,1;
INSERT dbo.bar SELECT 2,NULL; -- succeeds
GO
INSERT dbo.bar SELECT 3,2; -- fails

Context

StackExchange Database Administrators Q#40924, answer score: 14

Revisions (0)

No revisions yet.