Post reply

FOREIGN KEY constraints and NULLs

  • March 14, 2006 at 12:34 pm

    #112379

    Hi,

    I have two tables, T1 and T2.  A relationship is defined such that a foreign key on T2.col1 references T1.col1.  T2.col1 is nullable.

    My understanding is that if I try to insert a null value into T2.col1 then this will raise a FOREIGN KEY constraint error.  However, on testing this is not the case, the null is successfully inserted even though, of course, no value matches in the parent.

    Any comments appreciated

    Thanks.

    Regards,

    PK

     

  • March 14, 2006 at 12:46 pm

    #626626

    I think that your understanding is wrong this time - NULLs are fine in FK constraints - at least in SQL Server (BOL confirms).

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • March 14, 2006 at 2:13 pm

    #626641

    NULL just means that there in no corresponding data in T1.

    If you have AddressId in table Customers NULL in this field means that there is no address recorded for this customer.

    _____________
    Code for TallyGenerator

  • March 14, 2006 at 2:32 pm

    #626648

    if the column where you defined the Foreign Key Constraint was defined as not required (meaning can accept nulls) then NULL is perfectly legal. If you want to prevent that, then define the column as NOT NULL (required).

     

    Cheers,

     


    * Noel

  • March 15, 2006 at 2:32 am

    #626699

    Thanks for all responses.

    PK

     

Viewing 5 posts - 1 through 4 (of 4 total)

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