Re: Separate foreign keys with shared ID space

"Erland Sommarskog" <esquel_at_sommarskog.se> wrote in message news:Xns953819DCF3B6Yazorman_at_127.0.0.1...
> Marshall Spight (mspight_at_dnai.com) writes:
> > "Christian Antognini" <christian.antognini_at_trivadis.com> wrote in
message
> news:410affcf$1_at_post.usenet.com...
> >>
> >> A PK should have no business meaning.
> >
> > Says who? Can you justify this statement?
>
> Because most real-world look-like keys do not live up to the strict
> requirements of the primary keys in a database. They are too often
> nullable or non-unique. Person identification numbers such as SSN
> are a good example of both.

The problem I have with synthetic primary keys is precisely that they abstract themselves so far away from business logic and hence become 'meaningless' that they cease to do the job they were intended to do. If I insert a new record into the PERSONS table, using only a sequence number generator to supply a new, unique ID for the row, I am pretty well guaranteed to be able to insert the new record, even for a criminal clone of Al Capone. If I use the SSN as a primary key field, however, it is highly likely that my insert will (correctly) fail whenever someone is trying to do a bit of identity theft. Sure, I could use a synthetic primary key, and then add a unique constraint to a field such as SSN... but then I am merely doing to the SSN manually what the declaration of it as a primary key would have done in the first place. Meanwhile, there's a second index that I now have to bear the costs of maintaining instead of just one.

For me, a primary key should absolutely embody a business rule ("no two people can share a SSN", for example), since a primary key is a *logical* way of uniquely referring to every row in the table, and logic and business rules are close companions. If I want a non-nullable, meaningless and guaranteed unique *physical* reference to my rows, I have the ROWID for that. Synthetic keys seem to me to merely replicating the same sort of functionality that the ROWID gives one in any case.

There are arguments both ways, of course. And some of the 'big names' are definitely fans of synthetic keys (Steve Adams springs to mind), which should give me pause. But I still don't like the extra unique checks needed when synthetics are employed. And it seems to me that, in any case, there is no way the original bald statement should be accepted without qualification.

Regards
HJR Received on Sat Jul 31 2004 - 17:40:47 CDT