Re: Surrogate primary key plus unique constraint vs. natural primary key: data integrity?

On Fri, 8 Mar 2013 20:05:17 +0000 (UTC), Roy Hann <specially_at_processed.almost.meat> wrote:

>I'm a computer science person,

Ditto.

>and I will tell you (a) there's no such thing as a primary key; a key is
>a key or it's not. Preferring one candidate key over another is purely
>psychological. There is no theoretical/logical basis to introduce a
>surrogate primary key in the presence of a natural key.

Correct. However there may be a practical basis ... e.g., it may be computationally expensive to perform comparisons on the natural key and so you would prefer to work with a less expensive surrogate when possible.

>But why introduce surrogate keys at all? Even in SQL databases we can
>define a foreign key with ON UPDATE CASCADE.

Many intro DB texts use strings - typically names - as keys in their examples, while eliding (or glossing over) practical consideration of using strings as keys in real world systems. Unfortunately, there are too many people creating/administering databases who have, at best, only intro level exposure.

Ideally [and in most cases], a foreign key in a dependent table will be an internal object reference to the record in the primary table rather than a distinct copy of the key value ... but you can't count on that if you're working with older software or with a distributed database [yes Virginia, there are reasons to have the primary and dependent tables be on different hosts (or DBMS instances)].

Regardless of the internal FK representation, inserts/updates on the dependent table still involve comparisons against the referenced key column of the primary table. If your design makes extensive use of FKs in dynamic tables, then for performance reasons you want to make those comparisons as inexpensive as possible.

As always, YMMV.
George Received on Sat Mar 09 2013 - 08:24:35 CET