February 4, 2011 at 2:06 am
Please run the code sample below.
I have a foreign key on two nullable fields. In the primary key table, I insert values (1,1)
I then test which values I can insert into the FK table.
I can insert (1,1), and (null, null). So far so good.
I cannot insert (5,5) as there is of course a FK violation.
I'm a little surprised that I can insert (1,null) or (null,1).
But I'm positively alarmed to see that I can insert (null,4) or (3,null).
I'm sufficiently humble to think that SQL is most probably behaving as it should be, and that there's some perfectly reasonable explanation to it all.
Do any of you know what the explanation might be - and ideally a link to where I can read about it would be great.
Thanks,
David McKinney.
use sandbox
go
if exists (
select 1 from sys.foreign_keys as fk where fk.name ='FK_MyTable_MyLkup')
BEGIN
ALTER TABLE dbo.MyTable
DROP CONSTRAINT FK_MyTable_MyLkup
END
GO
if object_id('dbo.MyLkup') is not null
drop table dbo.MyLkup
go
if object_id('dbo.MyTable') is not null
drop table dbo.MyTable
go
CREATE TABLE MyTable
(
pkmytable int identity(1,1) primary key ,
fkfield1 int null ,
fkfield2 int null
)
CREATE TABLE MyLkup
(
constraint PKMyLkup primary key (pkfield1, pkfield2) ,
pkfield1 int ,
pkfield2 int
)
ALTER TABLE MyTable ADD CONSTRAINT
FK_MyTable_MyLkup FOREIGN KEY
(
fkfield1,
fkfield2
) REFERENCES MyLkup
(
pkfield1,
pkfield2
)
insert into dbo.MyLkup
( pkfield1, pkfield2 )
VALUES ( 1, -- pkfield1 - int
1 -- pkfield2 - int
)
GO
insert into dbo.MyTable
( fkfield1, fkfield2 )
VALUES ( 1,1
)
insert into dbo.MyTable
( fkfield1, fkfield2 )
VALUES ( null, -- fkfield1 - int
null -- fkfield2 - int
)
insert into dbo.MyTable
( fkfield1, fkfield2 )
VALUES ( 1, -- fkfield1 - int
null -- fkfield2 - int
)
insert into dbo.MyTable
( fkfield1, fkfield2 )
VALUES ( null, -- fkfield1 - int
1 -- fkfield2 - int
)
insert into dbo.MyTable
( fkfield1, fkfield2 )
VALUES ( 4, -- fkfield1 - int
null -- fkfield2 - int
)
insert into dbo.MyTable
( fkfield1, fkfield2 )
VALUES ( null, -- fkfield1 - int
3 -- fkfield2 - int
)
insert into dbo.MyTable
( fkfield1, fkfield2 )
VALUES ( 3, -- fkfield1 - int
3 -- fkfield2 - int
)
February 4, 2011 at 2:33 am
You are correct in your assumption that SQL Server is doing what it should.
Have a look at this link http://msdn.microsoft.com/en-us/library/ms175464.aspx
Just under the first diagram, you will find the explanation you were looking for.
February 4, 2011 at 3:28 am
Thanks, captain. Exactly the link I was looking for.
I'm not thrilled by this behaviour but at least it's documented and 'by design'.
February 4, 2011 at 8:23 am
David McKinney (2/4/2011)
Thanks, captain. Exactly the link I was looking for.I'm not thrilled by this behaviour but at least it's documented and 'by design'.
Behaviour is there to deal with unusual situations.
As a rule of thumbs, all participating columns in a PK/FK relationship should be defined as Not Null - what's the purpose of doing referential integrity if underlying data model doesn't care about it?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 4, 2011 at 9:09 am
Instead of NULL, create an 'Undefined' record holding key 0. Makes life much easier. In my warehouse I have set up the keys to start at IDENTITY(0,1) for this reason. Immediately after the CREATE I INSERT a 0 record for Undefined moments.
Hope you can use this in your solution.
-------------------------------------------------------------------------------------------------
My SQL Server Blog
February 4, 2011 at 9:18 am
Thanks, Paul, Jonathan.
I think I will go down the Unknown / Not applicable route. i have on a couple of occasions in the past had a nullable field involved in a fk relationship )but this is the first time where there were two fields involved). With one field, it's not problematic, neither is it nonsensical. The field is either not present or when it is present must have a value contained in the fk table.
I like the identity (0,1) idea 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply