X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 83C119FA0EC for ; Thu, 6 Jul 2006 18:18:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 06737-07 for ; Thu, 6 Jul 2006 18:18:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from druid.net (druid.net [66.96.28.249]) by postgresql.org (Postfix) with ESMTP id 349289F94F3 for ; Thu, 6 Jul 2006 18:18:20 -0300 (ADT) Received: from dilbert (H245.C18.B96.tor.eicat.ca [66.96.18.245]) by druid.net (Postfix) with SMTP id D48B6B4C1; Thu, 6 Jul 2006 17:18:18 -0400 (EDT) Date: Thu, 6 Jul 2006 17:18:16 -0400 From: "D'Arcy J.M. Cain" To: "David Clarke" Cc: operationsengineer1@yahoo.com, postgresql@aranya.com, pgsql-sql@postgresql.org Subject: Re: Alternative to serial primary key Message-Id: <20060706171816.9f248316.darcy@druid.net> In-Reply-To: <12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com> References: <20060706183920.46008.qmail@web33314.mail.mud.yahoo.com> <12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com> X-Mailer: Sylpheed version 2.2.6 (GTK+ 2.8.18; i386--netbsdelf) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/71 X-Sequence-Number: 25645 On Fri, 7 Jul 2006 08:30:57 +1200 "David Clarke" 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 | 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.