Surrogate keys (Was: enums)

[Leandro Guimarães Faria Corcete DUTRA <leandro(at)dutra(dot)fastmail(dot)fm>]

Rod Taylor <pg <at> rbt.ca> writes:

> The basic idea is that most of us break out schemas by creating fake
> primary keys for the purpose of obtaining performance because using the
> proper primary key (single or multiple columns) is often very slow.

This is one thing I simply can't understand.

If you still declare the natural key(s) as UNIQUEs, you have just made 
performance worse.  Now there are two keys to be checked on UPDATEs and 
INSERTs, two indexes to be updated, and probably a SEQUENCE too.

If you don't, you have just thrown away centralised, optimised integrity 
checking, and will probably have to remember to do a slower SELECT before 
updating.

Certainly decoupling presentation from storage would be nice, but even before 
that generalised use of surrogate keys seems to me a knee-jerk reaction.