Sql-server – How to enforce a nullable foreign key

database-designforeign keysql serversql-server-2008

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?

Best Answer

Yes, why not? Did you try it?

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.

Related Question