Re: surrogate vs natural primary keys - Mailing list pgsql-sql

From Andrew Sullivan
Subject Re: surrogate vs natural primary keys
Date
Msg-id 20080916151743.GG201@commandprompt.com
Whole thread Raw
In response to Re: surrogate vs natural primary keys  ("Richard Broersma" <richard.broersma@gmail.com>)
List pgsql-sql
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.)

A

-- 
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/


pgsql-sql by date:

Previous
From: "Richard Broersma"
Date:
Subject: Re: surrogate vs natural primary keys
Next
From: "Richard Broersma"
Date:
Subject: Re: a simple transform