Surrogate keys (Was: enums)

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.