Re: Primary key on one or two fields

In message <a264e7ea.0308121601.7bb2a9b9_at_posting.google.com>, --CELKO-- <joe.celko_at_northface.edu> writes
>>> Nitpick: primary key's can't be null, for good reason. Substituting
>a default value doesn't improve the situation. <<
>
>I agree that I would much rather have an actual value in a key, but
>very often there are reasonable defaults that carry information.
>
>Example: the ICD codes (International Classification of Disease) have
>both 000.000 and 999.999, with 000.000 as a natural default. 000.000
>is a missing value marker that means "Undiagnosed" and 999.999 is a
>missing value marker that means "We tried to diagnose it, but don't
>know what the heck is killing you!"
>
>The use of "vacant" or "to be determined" for an open job position is
>menaingful; "refund" for an inventory item that has gone out of stock
>and the default is fired by a ON DELETE SET DEFAULT action, etc.

I don't have any objection to default values in a data field provided it doesn't have a UNIQUE restriction, as primary keys must have.

The first record to be created with a missing value gets the default assigned to it. The second record with a missing value will throw a duplicate key error. I would much rather have every such record generate an exception.

And anyway, records with unknown primary keys give me the willies.

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.