Surprising Foreign Key validations

  • 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

    )

  • 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.

  • 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'.

  • 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.
  • 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

  • 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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply