Re: Surrogate Keys: an Implementation Issue

Paul Mansour wrote:
> I'm working on a little RDBMs project, and I've been pondering the
> general arguments for and against surrogate keys, and have come to
> following conclusion:
>
> The primary key should indeed be a natural key, if one is available, at
> the model level, but at the implementation level the table should have
> a system-supplied, auto-incremented, and hidden, "super primary
> key". This super primary key, (SPK) is then used when populating FKs
> in other tables (again, hidden from the user). It is up to the DBMS to
> do the necessary conversions to insulate the user from knowledge of the
> SPK.
>
> If there is no natural key, then the SPK can simply be exposed to the
> user as the PK for that table.
>
> Thus, from the user's perspective, it appears that, say, Social
> Security Number is sprinkled across a dozen different tables, when
> under the covers its just meaningless 32 bit ints, and the SS number is
> only stored once in the Employee table.
>
> This seems to give all of the advantages of surrogate keys, (very easy
> to change, saves on space) and the advantages of natural keys
> (meaningful, etc), and conversely, none of the drawbacks of either. In
> other words, the best of both worlds.
>
> Is this a bad conclusion?
>
> Do any current commercial RDBMSs already work this way under the
> covers?

Just a quick question. What if, for whatever reason, you did NOT want "ON UPDATE CASCADE" semantics? It seems you cannot avoid them in your situation...

Damien Received on Thu Jul 20 2006 - 05:35:46 CEST