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 4FDE29DC9A1 for ; Tue, 28 Feb 2006 22:54:06 -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 73906-06 for ; Tue, 28 Feb 2006 22:54:10 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id 44E169DC81D for ; Tue, 28 Feb 2006 22:54:04 -0400 (AST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id D9F44381C9; Tue, 28 Feb 2006 18:53:53 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id D7C943808E; Tue, 28 Feb 2006 18:53:53 -0800 (PST) Date: Tue, 28 Feb 2006 18:53:53 -0800 (PST) From: Stephan Szabo To: Daniel Joo Cc: pgsql-sql@postgresql.org Subject: Re: how to add primary key to existing table with multiple In-Reply-To: <20060224015148.A9BB99DC9E3@postgresql.org> Message-ID: <20060228185121.O86334@megazone.bigpanda.com> References: <20060224015148.A9BB99DC9E3@postgresql.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.138 required=5 tests=[AWL=0.138] X-Spam-Score: 0.138 X-Spam-Level: X-Archive-Number: 200602/335 X-Sequence-Number: 24345 On Thu, 23 Feb 2006, Daniel Joo wrote: > I am trying to add another primary key to an existing table with two > other primary keys. I got the following error when I tried this > command: You only can have one primary key. The table you gave has a single primary key with two columns. Are you trying to guarantee that expid along is unique or that the set (probeid, tissueid, expid) is unique? You can make expid UNIQUE and NOT NULL for the first case which is similar to a primary key (although it's not a default foreign key target). In the second you can drop the current constraint and add one on the three columns. > Indexes: > > "extprobe2tissue_pkey" PRIMARY KEY, btree (probeid, tissueid) > "extprobe2tissue_probeid" btree (probeid) > "extprobe2tissue_tissueid" btree (tissueid)