Re: Surrogate keys (Was: enums)

On Fri, 2006-01-13 at 12:42 +0000, Leandro Guimarães Faria Corcete DUTRA
wrote:
> 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.

Indeed. Using a surrogate key is not free and that is why it would be
something the DBA would specify during table creation.

The main goal would be to give the option of using a surrogate key
without being forced to expose it to the applications using the
database. It is a feature akin to table spaces in that it can help
performance but without the application or standard users knowing why.

--