From: Josh Berkus <josh@agliodbs.com>
To: David Garamond <lists@zara.6.isreserved.com>
To: pgsql-sql@postgresql.org
Subject: Re: surrogate key or not?
Date: Fri, 6 Aug 2004 15:42:04 -0700
Message-ID: <200408061542.04336.josh@agliodbs.com> (raw)
In-Reply-To: <41026D55.3040002@zara.6.isreserved.com>
References: <1090325769.3241.27.camel@dicaprio.akademie1.de>
<200407210001.00102.josh@agliodbs.com>
<41026D55.3040002@zara.6.isreserved.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, lists@zara.6.isreserved.com
Subject: Re: surrogate key or not?
In-Reply-To: <200408061542.04336.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox