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?
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:58not null
, because[ Num, Dat ]
can be NULL. But, ifNum
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:02CHECK
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