0

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?

1
  • I can't use not null, because [ Num, Dat ] can be NULL. But, if Num have value, Dat must have a value also. And yes, the design/modeling of this tables is not good, but unfortunately I can not change :( – Zack Stone Nov 26 '15 at 14:02
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
        ) ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.