Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts

    Adding a foreign key to an existing nullable field in SQL Server

    We have an application that uses a SQL Server 2008 R2 database.

    This has an Order table with a SalespersonId field (Order_SalespersonId) that allows nulls.

    It also has a Salesperson control table with a SalespersonId field as its primary key (Salesperson_SalespersonId).

    Currently, for every existing record in the Order table, the Order_SalespersonId field either matches a Salesperson_SalespersonId value in the Salesperson table or is null.

    We want to make the Salesperson_SalespersonId field a foreign key (FK) on the Order_SalespersonId field.

    I gather that it is OK to have a FK with null values; if so, what is the procedure to add the existing Salesperson_SalespersonId field as a FK on the existing Order_SalespersonId field?

  2. #2
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,592
    Thanks
    5
    Thanked 1,059 Times in 928 Posts
    Joe

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    Thanks for your reply. That was my understanding, and the Order table already has other fields that allow and contain nulls and have a FK, which appears to confirm this. (These fields need to allow nulls because their values are not known when the records are created but are entered later when the order is completed or may remain null if the order is cancelled, but they need to have a FK because if/when a value is entered, then it must be a valid value from the FK control table.)

    However, my developer agrees that it is possible to create a *new* field that allows nulls and has a FK but says that it is not possible to add a FK to an *existing* field that contains nulls.

    This resource appears to indicate that it is possible to add a FK to an existing field that contains nulls:
    "When a FOREIGN KEY constraint is added to an existing column or columns in the table, by default, the Database Engine examines the existing data in the columns to make sure that all values, except NULL, exist in the columns of the referenced PRIMARY KEY or UNIQUE constraint. However, by specifying WITH NOCHECK, the Database Engine can be prevented from checking the data in the column against the new constraint and made to add the new constraint regardless of the data in the column. The WITH NOCHECK option is useful when the existing data already meets the new FOREIGN KEY constraint, or when a business rule requires the constraint to be enforced only from this point forward."
    https://technet.microsoft.com/en-us/...=sql.105).aspx
    Can you confirm whether "except null" here means that when adding a FK to an existing field, the engine does not *allow* null values (as my developer suggests) or only that it does not *check* null values (as I understood)?

    However, even if the developer is correct, it appears that the "With Nocheck" option could be used to circumvent the issue? (We have already run a query to confirm that all values in the Order_SalespersonId field either already exist in the FK control table or are null, so the issue is not mismatches, only nulls.)

    Alternatively, if the developer is correct, could a procedure like the following be used to achieve the required result?
    - Create new field (Order_Temp) with Allow nulls = Y, and FK (Salesperson_SalespersonId).
    - Copy contents of existing field (Order_SalespersonId) into new field (Order_Temp).
    - Delete Order_SalespersonId.
    - Rename Order_Temp to Order_SalespersonId.
    Last edited by Murgatroyd; 2016-09-22 at 21:51.

  4. #4
    Administrator
    Join Date
    Mar 2001
    Location
    St Louis, Missouri, USA
    Posts
    23,592
    Thanks
    5
    Thanked 1,059 Times in 928 Posts
    Not sure what exactly is checked. Why not just try to modify the field? If it fails then you know you need a different approach.
    Joe

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Australia
    Posts
    676
    Thanks
    28
    Thanked 2 Times in 2 Posts
    The developer has checked and confirmed that it is possible to add a FK to an existing field that contains nulls.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •