Re: surrogate key or not?

Hi,

> 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)?

I don't think that I'd call an invoice number a surrogate key in the first
place. Invoice numbers and their like come from business requirements, they
just happen to be highly suitable as PKs so they could be considered a
natural key.

> > 3) Mutability: Most RDBMSs are very inefficient about CASCADE deletes
and
>
> 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).
> 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.

I've never felt that it's a good idea to be dependent on the DBMS providing
cascade functionality - particularly cascading updates to PKs. I don't think
I've ever worked on a DB that used such constraints.

> 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:

I think that most of us would agree that whatever they are, surrogate keys
aren't a substitute for clean data (or quality data).

On the whole, I think that there are more important indicators of quality
(or lack of it) in your database design than the prevalence (or lack) of
numeric ID style keys. Personally, I've grown to appreciate the id approach
over the years, but my mind is always open to other ideas.

regards
iain