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?

  • 2
    All foreign key constraints - in all DBMS - allow inserting NULL values in nullable columns. If you want a foreign key to be checked, use not null columns. What row should (1, NULL) be referencing anyway? The (1,1), the (1,2), the (1,4) or the ...? – ypercubeᵀᴹ Nov 26 '15 at 13:58
  • 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 :( – Yitzhak Andrade Nov 26 '15 at 14:02
  • Then you need a CHECK constraint: ALTER TABLE Table2 ADD CONSTRAINT NumDatNotNullTogether CHECK (Num IS NULL AND Dat IS NULL OR Num IS NOT NULL AND Dat IS NOT NULL) ; – ypercubeᵀᴹ Nov 26 '15 at 14:04
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.