X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0CF4C9DCC68 for ; Thu, 9 Feb 2006 16:10:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 24859-04 for ; Thu, 9 Feb 2006 16:10:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ll.mit.edu (LLMAIL.LL.MIT.EDU [129.55.12.40]) by postgresql.org (Postfix) with ESMTP id C2A2C9DCC15 for ; Thu, 9 Feb 2006 16:10:45 -0400 (AST) Received: (from smtp@localhost) by ll.mit.edu (8.12.10/8.8.8) id k19KAjLL010669 for ; Thu, 9 Feb 2006 15:10:45 -0500 (EST) Received: from UNKNOWN( ), claiming to be "sty.llan" via SMTP by llmail, id smtpdAAALma4ct; Thu Feb 9 15:10:18 2006 Date: Thu, 9 Feb 2006 15:10:18 -0500 From: george young To: pgsql-sql@postgresql.org Subject: Re: unique constraint instead of primary key? what Message-Id: <20060209151018.58bec92d.gry@ll.mit.edu> In-Reply-To: <1139502179.764894.35960@o13g2000cwo.googlegroups.com> References: <20060208210455.5d6f5bf9.gry@ll.mit.edu> <1139502179.764894.35960@o13g2000cwo.googlegroups.com> Reply-To: gry@ll.mit.edu Organization: MIT Lincoln Laboratory X-Mailer: Sylpheed version 2.0.1 (GTK+ 2.8.0; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.087 required=5 tests=[AWL=0.086, UNPARSEABLE_RELAY=0.001] X-Spam-Score: 0.087 X-Spam-Level: X-Archive-Number: 200602/108 X-Sequence-Number: 24118 On 9 Feb 2006 08:22:59 -0800 "BigSmoke" threw this fish to the penguins: > If my tables have one or more UNIQUE constraints/indices, I still add a > "id SERIAL PRIMARY KEY" field to most of my tables. This makes > referencing easier and faster. It also improves consistency, which is > never a bad thing in my opinion. In this schema overhaul I'm trying to *eliminate* arbitrary "id" columns like this. They may sometimes improve performance, but certainly obscure the meaning of the data -- naive [read-only] users accessing through excel are confused by this sort of thing. > As far as I know, though, PRIMARY KEY does the same thing as UNIQUE NOT > NULL in PostgreSQL. > > The reason that PRIMARY KEY can't be NULL and _has to be_ UNIQUE is > that it is the primary means of identifying a given record in a table. > If you don't have PRIMARY KEY that is UNIQUE and NOT NULL, how are you > going to identify (or reference) individual records? PostgreSQL won't > allow you to reference more than one row for what I came to believe are > very good reasons. Actually, I don't really see a problem here. E.g. when I want data from a step that has no parent run and no parent opset, I could say: select * from steps where run is null and opset is null and step='mystep'; I don't understand what you mean by "PostgreSQL won't allow you to reference more than one row". If the above query returns 10 rows, what's wrong with that? Nothing *requires* a PRIMARY KEY at all, anyway. -- puzzled... George -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL)