Re: Primary key on one or two fields

My question is whether a default value is even applicable to a primary key in today's implementations of SQL databases. I haven't considered or investigated the implications for composite keys, but...

If I remember correctly, with Oracle at least, even with a default value defined for a primary key column, an attempt to insert a null in place of a value for the primary key, the default value will fail to replace the null and an exception will be returned.

This might be an implementation specific side affect. Does anyone know the language of the SQL99 standards in relation to default values defined on primary key columns?

Another implication of defining default values, which has been stated before and barring the limitation noted above, is that if the default is constrained to a literal value, then when it is defined over a scalar primary key, it is useful for one, and only one, missing primary key value.

Regards,

Daniel Guntermann

"--CELKO--" <joe.celko_at_northface.edu> wrote in message news:a264e7ea.0308121601.7bb2a9b9_at_posting.google.com...
> >> 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.
Received on Wed Aug 13 2003 - 20:48:21 CEST