Re: Alternative to serial primary key

On Fri, 7 Jul 2006 08:30:57 +1200
"David Clarke" <pigwin32(at)gmail(dot)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(at)druid(dot)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.