Re: Separate foreign keys with shared ID space

From: Hans Forbrich <forbrich_at_yahoo.net>
Date: Sat, 31 Jul 2004 18:58:23 GMT
Message-ID: <jZROc.2236$T_6.1453@edtnps89>

Marshall Spight wrote:

> "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?

A PK should be selected to uniquely identify an entity. Ideally, and by formal definition, the PK is invariant.

All to often a unique attribute of the entity, such as empno (or SSN/SIN or name or email address,) is used as the PK. Attributes generally have a business meaning. Such attributes can change, although some change very infrequently.

Selecting an attribute as the PK can cause DBAs (and/or developers and/or businesses) headaches when attempting to change the PK. This is especially true when taking the offline archives into account.

All of the examples are not invariant, although they tend not to change often. In some cases using the suggested PK is against the law (eg: SIN in Canada, has privacy implications).

Hans Received on Sat Jul 31 2004 - 13:58:23 CDT