Re: Primary Key

Martijn van Oosterhout wrote:
> On Fri, Nov 23, 2007 at 09:33:13AM +0000, Peter Childs wrote:
>
>> I tend to agree that primary keys should be single fields if they need to be
>> referenced but should also be natural if at all possible. ie use car number
>> plates rather than some serial int.
>>
>
> Car number plates are unique over time? I didn't think so...
>

It's worse than that.

If we presume that the plate is a key to a vehicle, then we immediately
run into problems as a vehicle can, over time, have several plates
(lost, stolen, changed to vanity...) and a plate can belong,
sequentially, to several vehicles (especially when vanity plates are
transferred to new cars).

And when you have your char(6) plate-number column, they run out of
numbers and switch to 7-characters requiring changes to all tables that
used the plate as a key. Or you realize that ABC123 could be
ABC123-California, ABC123-Nevada or ABC123-New York (I'm assuming that
AAA999 is a valid format in those states).

Although I haven't seen it much, recently, semi-trucks used to regularly
have with numerous plates - one for each state in which they operated.
And some states such as Texas allow you to have the same amateur-radio
plate number on multiple vehicles.

I won't argue that there are no reasonable natural keys. But I have sure
seen plenty of cases where what appeared to be a natural key was
discovered, generally at a very inopportune time in the development
process, to be not-so-natural after all.

Cheers,
Steve