1

One of the differences between a candidate key and a primary key is that "candidate keys can contain nulls." I have been unable to find a more precise definition of what this means. One explanation I have seen is that in a CK with only one attribute, exactly one value can be NULL. Presumably this is because that NULL can uniquely identify a tuple/row. Is this correct?

What I have had even more difficulty finding is what happens in a candidate key that contains more than one attribute:

  1. I think I saw one reference state that each attribute can contain only one NULL.
  2. However, by definition of CK, it seems there can be any number of NULLs as long as the set of attributes in the CK still uniquely identifies a single tuple/row.

Which one is correct, or are they both wrong?

2
  • "only one NULL" In Standard SQL a PK constraint means UNIQUE NOT NULL & a column in a UNIQUE constraint can contain multiple NULLs. In SQL Server a PK constraint means UNIQUE NOT NULL but a UNIQUE constraint allows at most one NULL in a column. (One can use INDEXes to enforce the constraint/restriction of multiple NULLs.) Could this be what you are asking about? Because that has nothing to do with CKs or SQL analogues of relational CKs or PKs or other relational terms & is about conditions described by SQL terminology without regard to how the same terms are used relationally.
    – philipxy
    Apr 3, 2022 at 20:55
  • "CK" has no single meaning in writing involving NULLs. (Relational or SQL--where tables are not even relations.) So either give definitions or ask about a particular presentation. But we can't read the minds of authors of poor presentations. Or ask re how you are 1st stuck in some task involving a "CK"--but then what did the task assigner mean? What to do with null values when modeling and normalizing? Does an empty SQL table have a superkey? Does every SQL table?
    – philipxy
    Apr 3, 2022 at 21:12

1 Answer 1

3

It all gets a bit tricky because of three-valued logic. Comparing NULL to anything returns "unknown", not "same" or "different" but "unknown". This applies even when comparing two NULLs.

How are we supposed to tell if we are about to create duplicates in a candidate key? Well, you have duplicates if the value you're trying to insert matches any value currently in the table. However, if what we are trying to insert is NULL you can only ever get the answer "unknown".

One way to address this is to define that candidate keys may not contain nullable columns. That's legitimate. Another is to extend the definition of duplicate to "new value equals existing value or new value is null and an existing value is null." This is also legitimate. Neither is right or wrong, they are just two differing definitions which have been given the same name.

At the start of the relational model Codd said each table must have a primary key and this is special. Later this was relaxed to say each table could have many candidate keys and none were more special than any other. By then, however, the idea of a special primary key was baked into the collective consciousness and several popular RDBMS products. And so we have today the condition you describe.

For example, in Microsoft SQL Server a primary key constraint may not contain a nullable column. However a unique constraint my contain one or more nullable columns. Both constraints may be referenced by a foreign key definition.

In conclusion, the distinction between a primary key and any other candidate key is artificial and retained because of the early history of relational databases. Whether or not to allow nullable columns in a key is implementation dependent. It depends on how worked up you want to get about 3-value logic or if you accept that a NULL can be treated as being the same thing as another NULL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.