LinLog

Well, the cat is currently laying on the book I was trying to read, so I guess I might as well blog.

Note to self: the rule in SQL is that you can have NULLs in foreign keys, but not in primary keys. Edwin asked me a question related to this the other day and I had to stop and think about it for a couple of minutes, so I figured I'd write it out to reinforce the concept in my own mind.

When you think about it, these rules make perfect sense. Since NULLs in SQL denote an unknown or inapplicable value, having one in a primary key would be nonsense, because a primary key must uniquely identify a row. Having a NULL in the key would mean part of the unique value was unknown. (Note that you can't say that a tuple with a NULL in it is unique because one NULL is not equal to another due to the 3-valued logic used by SQL.)

As for foreign keys, SQL gives the "benefit of the doubt." If you think of a NULL as an unknown value which may later become known, this makes sense. Sometimes you might have to add the values of foriegn keys in multiple operations, which would not be allowed if NULLs were blocked by the constraint.

From a logical point of view, this is kind of an interesting case as well. You can view a foreign key constraint check as a conjunction of equivalence checks. For example if T1(c1, c2) is foreign keyed to T2(c1, c2), then the constraint check can be viewed as the expression T1.c1 = T2.c1 AND T1.c2 = T2.c2. In other words, if all the key fields match on both records, the constraint is satisfied. If not, it fails. However, with NULLs, the value of one of the conjuncts would be NULL, which would render the entire expression NULL.

But what would that mean?

The DMBS can't say that it doesn't know if a relational constraint holds or not. So, given the interpretation of NULL, it makes sense to use the "benefit of the doubt" principle and defer final judgement on the constraint until all the NULLs are filled in.