Re: Surrogate keys (Was: enums)

[Lukas Smith <smith(at)pooteeweet(dot)org>]

Michael Glaesemann wrote:
> On Jan 13, 2006, at 21:42 , Leandro Guimarães Faria Corcete DUTRA wrote:
>
> > 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.
>
> For UPDATEs and INSERTs, the "proper" primary key also needs to be 
> checked, but keys are used for more than just checking uniqueness: 
> they're also often used in JOINs. Joining against a single integer I'd 
> think it quite a different proposition (I'd think faster in terms of 
> performance) than joining against, say, a text column or a composite key.

Well this is a balancing decision. You certainly slow down inserts. You 
might also increase the stress on the table because you have to 
translate between the different keys. It also depends on the join type 
you end up doing. It also obviously depends on how large your original 
primary key is. However whatever your situation is: make sure you do not 
end up doing premature optimization.

regards,
Lukas