locked
NULL Values in Foreign Keys? Best Practice RRS feed

  • Question

  • We're in the process of converting a MySQL database to MSSQL.  MySQL allows NULLs in foreign keys by default.

    What is the best practice?

     

    Can we allow nulls in our foreign keys?  Is it a good practice?

     

    Here is an example:

     

    ALTER TABLE tblTemp1 ADD CONSTRAINT tblTemp1_ibfk_1 FOREIGN KEY (e_code) REFERENCES tblTemp2 (e_code);

    tblTemp1

    ID     e_code

    1      AE

    2      NULL

    3     AC

    4     AB

    5     NULL

     

    tblTemp2

    e_code

    AA

    AB

    AC

    AD

    AE

     

    Monday, January 23, 2012 4:14 PM

Answers

  • Unless you really have a good reason, I wouldn't allow NULLs. This is one way to insure referential integrity in your database and you don't end up with a bunch of orphan records that you don't know where and how they made it into your tables. 

    My two cents. 


    Abdallah El-Chal, PMP, CIPP/IT, ITIL
    • Proposed as answer by skc_chat Tuesday, January 24, 2012 5:53 AM
    • Marked as answer by Stephanie Lv Wednesday, February 1, 2012 8:12 AM
    Monday, January 23, 2012 6:04 PM

All replies

  • Yes, you can allow NULL in the Foreign Key column. 
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Monday, January 23, 2012 4:17 PM
  • Allowing NULL is never a good practice.

    Should be avoided, if possible.

    Thanks,


    Trilok Negi
    Monday, January 23, 2012 5:36 PM
  • Hi, in my opinion, if the record in table 1 doesn't have a match in table 2 then NULL is a good option for that column (that's what NULL means, no value). You could also create a default item in table 2 (e.g., 'Unknown' or some other value) but if you don't have such requirement then stick to the first option.

    David.

    Monday, January 23, 2012 5:48 PM
  • In a real world applications, NULLs are inevitable. Just cook them properly.  If you did it on MySQL you most probably now what to do on MSSS.


    Serg
    Monday, January 23, 2012 5:53 PM
  • Yes, nulls are allowed for foreign key columns.  In fact, allowing nulls is required if the dependent table is required to support a relationship cardinality of zero to the independent table.


    Monday, January 23, 2012 5:56 PM
  • Unless you really have a good reason, I wouldn't allow NULLs. This is one way to insure referential integrity in your database and you don't end up with a bunch of orphan records that you don't know where and how they made it into your tables. 

    My two cents. 


    Abdallah El-Chal, PMP, CIPP/IT, ITIL
    • Proposed as answer by skc_chat Tuesday, January 24, 2012 5:53 AM
    • Marked as answer by Stephanie Lv Wednesday, February 1, 2012 8:12 AM
    Monday, January 23, 2012 6:04 PM
  • Obviuosly you can make use of NULL value in foreign key constraint.
    Tuesday, January 24, 2012 4:18 AM
  • Hello,

    Yes null values are allowed in FK and it may be helpful in some scenarios. Like you want some record that has no data but it has a reference to some table in that case null values help.

    Tuesday, January 24, 2012 6:03 AM
  • Allowing NULL is never a good practice.

    I don't agree with that. It really depends on the relation you are modelling.

    For example, if you ask customer data, and it is not mandatory to enter address data, then it would be perfectly good solution to have a foreign key constraint from the Customer Data table to the Countries table, where the Country ID in the Customere Data table is allowed to be NULL, which would then indicate that no address data (or in this example: Country) was provided.

    Now if you talk about a completely different example, such as an Order Lines table, then obviously, you would not want the Order ID column in the Order Lines table to allow NULL, because then you would have orphaned data.

     


    Gert-Jan
    Tuesday, January 24, 2012 8:21 PM
  • Gert-Jan,

    Mostly I agree with you, but I am suspicious of a table with too many nulls as a modelling failure. In your example above, if country is not required, it should be in a different (probably junction) table.

    Also what is the real difference between setting Address2 line of address info null or blank?


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES
    Sunday, January 29, 2012 12:38 PM
  • In your example above, if country is not required, it should be in a different (probably junction) table.

    Maybe, maybe not. Using a different table is another approach to the problem, and can be a perfectly valid choice. But so can a NULLable foreign key.

    Also what is the real difference between setting Address2 line of address info null or blank?

    Depends on the situation. In most situations there is no difference.

    I do have one example for you. In the Netherlands, in an address, you always have a house number, and optionally you can have a house number extension. If all applications use the database in the same consistent way, then blank would mean that the location does not have a house number extension, and that NULL means the house number extension was never asked (i.e. Unknown).

    And if there is a foreign key constraint, then obviously it is different too. But again, it requires a convention to establish what means what, and when to use what. It is all about choices (and personal preferences).


    Gert-Jan
    Sunday, January 29, 2012 2:01 PM