From: D'Arcy J.M. Cain <darcy@druid.net>
To: David Clarke <pigwin32@gmail.com>
Cc: operationsengineer1@yahoo.com
Cc: postgresql@aranya.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Alternative to serial primary key
Date: Thu, 6 Jul 2006 17:18:16 -0400
Message-ID: <20060706171816.9f248316.darcy@druid.net> (raw)
In-Reply-To: <12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com>
References: <bf05e51c0607060902vdd8d993u470c128abf127d3d@mail.gmail.com>
<20060706183920.46008.qmail@web33314.mail.mud.yahoo.com>
<12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com>
On Fri, 7 Jul 2006 08:30:57 +1200
"David Clarke" <pigwin32@gmail.com> wrote:
> Yes I had in fact already created my table using a serial as the
> primary key but I've been reading Celko's SQL Programming Style and
> the use of a hash on the address column as the primary key (and for
> use in FK's) meets a number of the requirements for a good key. The
> address column itself is the natural primary key but it doesn't make
Are you sure? I have a hard time imagining a situation where that
would be true. The only thing I can think of is some sort of
municipality database tracking properties regardless of who currently
owns/resides there in a situation where the address can never be
changed, e.g. "521 Main" splitting into "521A Main" and "521B Main."
Is that the situation here?
Also, you need to get into a lot more coding to handle the fact that
"521 Main Avenue" is the same address as "521 Main Av." and "521 Main
Ave" and even "521 Main."
And even given all of that, I would probably still use serial.
> and has been recommended. But having a hash function over the address
> column as the primary key means I can always regenerate my primary key
Danger, Will Robinson. The phrase "regenerate my primary key"
immediately raises the hairs on the back of my neck. If the primary
key can ever change, you have a broken schema.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
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: darcy@druid.net, pigwin32@gmail.com, operationsengineer1@yahoo.com, postgresql@aranya.com
Subject: Re: Alternative to serial primary key
In-Reply-To: <20060706171816.9f248316.darcy@druid.net>
* 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