Tuesday, September 16, 2008

Re: [SQL] surrogate vs natural primary keys

On Tue, Sep 16, 2008 at 07:59:20AM -0700, Richard Broersma wrote:
> key. From my reading of some of the Celko books, he strongly ascribes
> to codes as primary keys. His suggestion is to use internationally
> recognized codes (if they exist) for identify items.

The problem with that strategy is that any identifier has to be NOT
NULL and UNIQUE. So if the international identifier assigner makes a
mistake (fails to assign or assigns duplicates), you're hosed.

In addition, you need to make sure that the purpose for which you are
using the identifier is also the purpose for which the identifier is
assigned. ISO, for instance, is willing to re-use country codes (even
though the specification never suggested they were). So if you expect
to use the ISO 2-letter codes over time, you may get a nasty surprise.
(For an example, in 2003 "CS" became historically ambiguous.)


Andrew Sullivan
+1 503 667 4564 x104

Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:

No comments: