A few months back, I posted SQL Tip #1 – Indexing Foreign Key. Somebody asked me the question, can the foreign key be null? I read my post again, and realize I implied in my post that foreign key can be null. I didn’t say explicitly. Yes, foreign key can be null! Remember it is a legacy table, and the foreign key has to be allowed to have null. Also, In my post, I created a filtered index to deal with this issue of so many nulls in the foreign key to improve performance.

If you search internet, you will find lots of articles about this topic. I think this thread on stackoverflow is a good read.

You might ask why? It doesn’t make any sense. How can null be allowed in a foreign key, and how can a child exist without parent?

Here is my 2c. I try to explain from Math’s perspective.

Let us agree on this notation, {} = empty set = null

So do you agree:

  1. {} belongs to { 1, 3, 10, {} }?
  2. { 1, 3, 10, {} } = { 1, 3, 10 }?

If you agree item 1. and 2. are both true, we get:

{} belongs to { 1, 3, 10}

What does “{} belongs to { 1, 3, 10}” mean to database concept? It means:

null belongs to { 1, 3, 10}

Back to the case study of SQL Tip #1 – Indexing Foreign Key. If, for example,

  1. The parent IDs in parent table are: 1, 3, 10,
  2. A new one record is inserted into BigTableA with parent ID null.

And when the foreign key constraint is checked, null is considered to be a valid value, and belongs to {1, 3, 10} mathematically.

Please let me know your thoughts.