Composite foreign key not verify all columns

Question

Table1 (Num [PK NOT NULL], Dat [PK NOT NULL], ...)

Table2 (ID [PK], Num, Dat, ...)

I added this FK:

ALTER TABLE [dbo].[Table2]  WITH CHECK ADD FOREIGN KEY([Num], [Dat])
REFERENCES [dbo].[Table1] ([Num], [Dat])

---

But when I try to insert into Table2 the values [ Num = 1, Dat = NULL ], SQL don't give me an error and the values are inserted.

How I force SQL Server to check all columns of the foreign key?

Answer 1

A constraint of FOREIGN KEY will never forbid you from entering NULL values in nullable columns. The constraint is simply ignored for such rows.

You can make the columns not nullable, if you want to enforce this. Based on the comments though, you probably just need to add a CHECK constraint on the table, so the two columns are either null or not null, together:

ALTER TABLE dbo.Table2 
  ADD CONSTRAINT Num_and_Dat_are_Null_Together 
  CHECK ( Num IS NULL AND Dat IS NULL 
       OR Num IS NOT NULL AND Dat IS NOT NULL
        ) ;