Re: surrogate key or not? - Mailing list pgsql-sql

Thnx, Josh
you are very helpful.

> There are, in fact, three very good reasons to use surrogate keys, all of
> which are strictly due to limitations of technology; that is,
> implementation and performance issues, NOT business logic.  They are:
>
> 1) Convenience:  It's very annoying to have to refer to a 4-column foriegn
> key whenever you do a join in queries or want to delete a record, as well
> as tracking a 4-element composite in your client software.
>
> 2) Performance:  INT and BIGINT data types are among the most compact and
> efficient stored in most RDBMSs.   So using anything else as a key would
> likely result in a loss of performance on large-table joins.
>
> 3) Mutability:  Most RDBMSs are very inefficient about CASCADE deletes and
> updates.   Some RDBMSs do not support CASCADE, forcing the client software
> to fix all the dependant rows.  This means that DBAs are very reluctant to
> use columns which change frequently as join keys.

> Now, you're probably wondering "why does this guy regard surrogate keys as
> a problem?"   I'll tell you:  I absolutely cannot count the number of "bad
> databases" I've encountered which contained tables with a surrogate key,
> and NO REAL KEY of any kind.   This makes data normalization impossible,
> and cleanup of the database becomes a labor-intensive process requiring
> hand-examination of each row.


The surrogate keys is a real big problem.
But i want to add another two reasons to have a surrogate keys

4) Replication:  to identify an object of ANY type (record of any table
regardless to datamodel), to store lists of deleted or modified objects
(regardless to datamodel)

5) Making a primary key: if there is no real key at all.

the sentence (5) is debatable. in theory every relation has a real key, but in
the practice we have historical datamodels without PK !!! it is impossible
but it exists.
For example:
Here in Russia we have a lot of different but identically named streets within
one city. They has absoluetly identical attributes. Historically only human
not machines work on that datamodel and they all used to call such streets by
name adding some spechial non-formal explainations, for example:
"deliver this message please to the house 35 on the Green street, that is to
the west of the center of the city."
"deliver this message please to the house 12 on the Green street, that is
shortest of the all Green streets in the town."
Another exaple is a table of user messages:
user has a number of messages they have no mean attribute and also could be
identical, so you force this user to numbering his messages, or number them
yourself.