Re: Surrogate Keys: an Implementation Issue

Paul Mansour wrote:

>
> 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.
I can offer experience, but no heavy theory.

In my experience, having a surrogate key (in addition to primary key) on every table is extremely useful for the application. Its a great little way to simplify a lot of single-row code access. In other words, it makes my life easier and it does not threaten data integrity, so I use it.

However, using it as a foreign key seems to make my life harder, so I don't do that. The best example I can give us this is the system catalogs in SQL server, which exclusively use "meaningless" integers as the primary and foreign keys in tables. It makes cross-references useless in any quick visual scan, and complicates every single query. Using '5' to refer to column 'first_name' instead of the literal value 'first_name' makes life more difficult without adding value, so I don't do it.

>
> If there is no natural key, then the SPK can simply be exposed to the
> user as the PK for that table.

We make a slight variation here. Because we want the surrogate key to be a pure surrogate key, used only and always to refer to a single row of a single table, we don't even expose it as the pk for tables like ORDERS. For those tables we put in ORDER_NUM which is a sequenced column. Postgres actually makes this easy to do.

>
> Do any current commercial RDBMSs already work this way under the
> covers?

As I said, for a full-blown scenario of this kind of implementation of purely meaningless integer keys, look at the proprietary catalogs of SQL Server (not their standard INFORMATION_SCHEMA stuff).

-- 
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth_at_(Sec)ure(Dat)a(.com)