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 682A19FA0EC for ; Thu, 6 Jul 2006 17:31:03 -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 17463-06 for ; Thu, 6 Jul 2006 17:30:59 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.175]) by postgresql.org (Postfix) with ESMTP id BF81E9F94F3 for ; Thu, 6 Jul 2006 17:30:59 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id a2so2829532ugf for ; Thu, 06 Jul 2006 13:30:57 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Ol++Wt/UpLUHdwVN5tdt7vmkLahtSRbHy5PuuLxRmd0hzNHotfcEa7RweMRJD8FpFMN0wXJ/xSvoWPO4VW2khPFxVjESGUe47XKWusGdX7NNz4c+QQaLQ91C4nVrG6qqmMtPkplCGaKzdp+g1NylVhg2FaJ9E6x2IwZM3vEKMh0= Received: by 10.67.19.13 with SMTP id w13mr1160127ugi; Thu, 06 Jul 2006 13:30:57 -0700 (PDT) Received: by 10.67.23.14 with HTTP; Thu, 6 Jul 2006 13:30:57 -0700 (PDT) Message-ID: <12b7ac1e0607061330o34adbd6ds9bd624d3889c7fe7@mail.gmail.com> Date: Fri, 7 Jul 2006 08:30:57 +1200 From: "David Clarke" To: "operationsengineer1@yahoo.com" Subject: Re: Alternative to serial primary key Cc: "Aaron Bono" , pgsql-sql@postgresql.org In-Reply-To: <20060706183920.46008.qmail@web33314.mail.mud.yahoo.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20060706183920.46008.qmail@web33314.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/70 X-Sequence-Number: 25644 On 7/7/06, operationsengineer1@yahoo.com wrote: > i agree. all my primary keys are abstract - even > though some don't have to be. iow, i'm comfortable > using serials as my primary key even when i don't > absolutely need to. 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 for a good FK. Plus I feel I would be remiss in not exploring an alternative to the serial key. To recap, yes there is only a single column, yes it is varchar. I need to do a lookup on the address column which is unique and use it as a foreign key in other tables. Using a serial id would obviously work 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 from the data which is impossible with a serial key. I believe the risk of collision using md5 is effectively zero on this data and I can put a unique index over it. I'm kind of new to sql so apologies if this is a naive approach. Thanks to all for responses. Dave