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 BDFD39DCD3D for ; Fri, 10 Feb 2006 17:04:05 -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 09548-03 for ; Fri, 10 Feb 2006 17:04:06 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 185CB9DCD1F for ; Fri, 10 Feb 2006 17:04:02 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 178753093C; Fri, 10 Feb 2006 22:04:04 +0100 (MET) From: Chris Browne X-Newsgroups: pgsql.sql Subject: Re: unique constraint instead of primary key? what Date: Fri, 10 Feb 2006 15:11:47 -0500 Organization: cbbrowne Computing Inc Lines: 34 Message-ID: <60d5hvezy4.fsf@dba2.int.libertyrms.com> References: <20060208210455.5d6f5bf9.gry@ll.mit.edu> <1139502179.764894.35960@o13g2000cwo.googlegroups.com> <20060209151018.58bec92d.gry@ll.mit.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.18 (linux) Cancel-Lock: sha1:ey/+yPm0ShEzourXs2BBQ374huI= To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.261 required=5 tests=[AWL=0.261] X-Spam-Score: 0.261 X-Spam-Level: X-Archive-Number: 200602/120 X-Sequence-Number: 24130 gry@ll.mit.edu (george young) writes: > 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. Actually, that's not the only people that get confused. I'm not usually considered overly naive, and there is a database that I have been using for reverse-engineering purposes of late which is filled with both "id" and "v_id" columns which *very* much obscure the meaning of the data. I'm not adverse to having some such thing; I *am* adverse to giving them such generic names. It would be nice to be certain that the "would-be primary key characteristics" that you'd probably rather use are sufficiently permanent to be satisfactory. Unfortunately, they often aren't, or aren't acceptably usable. -- output = reverse("gro.gultn" "@" "enworbbc") http://cbbrowne.com/info/oses.html "To conquer the enemy without resorting to war is the most desirable. The highest form of generalship is to conquer the enemy by strategy." -- Sun Tzu, "The Art of War"