Re: surrogate key or not?

David,

> But, once a surrogate key is assigned to a row, doesn't it become a
> "real" data? For example, I have a bunch of invoices/receipts and I
> write down a unique number on each of them. Doesn't the unique number
> become part of the information contained by the invoice/receipt itself
> (at least as long as I'm concerned)?

Sure ... *if* it's being used that way. If, however, your table has that
Invoice # *and* a seperate surrogate key that's redundant and can cause
problems.

> Change management IMO is perhaps the main reason of surrogate/artificial
> key. We often need a PK that _never_ needs to change (because it can be
> a royal PITA or downright impossibility to make this change; the PK
> might already be printed on a form/card/document, recorded on some
> permanent database, tattoed/embedded in someone's forehead, etc).

Sure. But surrogate keys don't fix this problem; only good change management
does. This is precisely why I say "use with caution"; all too often project
leaders regard surrogate keys as a substitute for good change management and
don't do any further work.

> Meanwhile, every other aspect of the data can change (e.g. a person can
> change his name, sex, age, email, address, even date & place of birth).
> Not to mention data entry mistakes. So it's impossible to use any
> "real"/natural key in this case.

Absolutely false. It's quite possible, it's just a performance/schema/data
management issue. This also applies to my comment above.

> Okay, so surrogate key makes it easy for stupid people to design a
> database that is prone to data duplication (because he doesn't install
> enough unique constraints to prevent this). But I don't see how a
> relation with a surrogate key is harder to "normalize" (that is, for the
> duplicates to be removed) than a relation with no key at all. Compare:

You're right here, both are equally hard to normalize. What I'm criticizing
is the tendency of a lot of beginning DBAs -- and even some books on database
design -- to say: "If you've created an integer key, you're done."

Had I my way, I would automatically issue a WARNING on any time you create a
table in PG without a key.

--
-Josh Berkus
Aglio Database Solutions
San Francisco