SQL Server Performance

NULLs in Foreign Keys

Discussion in 'Getting Started' started by lcerni, Mar 9, 2008.

  1. lcerni New Member

    We have several foreign keys that allows for NULLs. When we open up a table in SQL Studio Management and alter
    one of the columns with a value that is not allowable (not part of the primary key), it accepts the value,
    when it should not. It appears that when there are nullable columns in a foreign key that the foreign key
    doesn't work; it is bypassed. I am guessing that this is part of the functionality of SQL Server. Is there a
    way to get around this. I am told that there is no way to make all the columns for foreign keys in this
    database NOT NULL. Thus, is there a work around to get the foreign key to work when one or more of the columns
    is NULL?
  2. satya Moderator

    As it states the good practice is to keep NULLs to a minimum in any database schema. However there's nothing fundamentally wrong with having a nullable Foreign Key constraint.
    So talking about relationship here such as referential integrity and nullability are inter-realted issues to each other. NOT NULL means that there must be a value relevant to that data type. And referential integrity means that if an attribute has a value, it must be defined in another table. http://www.akadia.com/services/dealing_with_null_values.html a good one to talk about using nULLS.
  3. Adriaan New Member

    If you can't use an FK (which might also be because SQL Server is very particular about avoiding circular references, even if the columns in question are not the same) then you might look at creating triggers to mimic the FK behaviour.
    If it would be an FK with cascading deletes and/or updates, you also have to create a corresponding trigger on the reference table.
    You also have to create (a) corresponding trigger(s) on the reference table.
  4. dschaeff New Member

    I am not reproducing that behavior. I have a FK on a table and a row where that value is null. When I try to update that value to a value that does not exist in the primary key table I get an error as I should. I do not think you should have to resort to triggers for this functionality and see nothing wrong with having nulls in a column used in a FK.
  5. lcerni New Member

    Do you have multiple columns in your FK with NULLS? I have a composite FK with 4 columns and for those 4 columns within the FK they can all be NULL.
  6. lcerni New Member

    Someone suggested doing something like this (I will work with a FK of 2 columns to make it easy):
    ALTER TABLE [dbo].[orders] WITH CHECK ADD CONSTRAINT [CK_orders] CHECK
    (([client_id] IS NOT NULL) OR ([client_id] IS NOT NULL AND [contract_id] IS NOT NULL) OR ([client_id] IS NULL AND [contract_id] IS NULL)))
    This is my FK. How would I incorporate the part (([client_id] IS NOT NULL) OR ([client_id] IS NOT NULL AND [contract_id] IS NOT NULL) OR ([client_id] IS NULL AND [contract_id] IS NULL))) into it. Can you have a FK and a constraint at the same time?
    ALTER TABLE [dbo].[orders] WITH NOCHECK ADD CONSTRAINT [FK_Orders_ClientContract_ClientId_ContractId] FOREIGN KEY([client_id], [contract_Id])
    REFERENCES [dbo].[client_contract] ([client_id], [contract_Id])
    GO
    ALTER TABLE [dbo].[orders] CHECK CONSTRAINT [FK_Orders_ClientContract_ClientId_ContractId]
  7. dschaeff New Member

    No, but check out this explanation of your issue with Microsoft: INF:Foreign Key Constraint Behavior of Nullable Composite Keys http://support.microsoft.com/kb/153370
    Looks like this is a "feature". If you can create, a surogate key on the primary key table and use that for your FK. That would result in faster joins anyhow.
    BTW, I see there's different and strong viewpoints out there about using nulls for FK values.
  8. lcerni New Member

    Just in case anyone ever comes across this issue...
    I came up with this code:
    ALTER TABLE orders WITH NOCHECK
    ADD CONSTRAINT CK_Orders_ClientId_ContractId
    CHECK (
    ([client_id] IS NOT NULL AND [contract_Id] IS NOT NULL)
    OR ([client_id] IS NULL AND [contract_Id] IS NULL)
    )
    It appears to accomplish what I need.

Share This Page