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 7A8B79DCCE0 for ; Wed, 1 Mar 2006 06:22:45 -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 49311-05 for ; Wed, 1 Mar 2006 06:22:46 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from resmta03.ono.com (smtp.ono.com [62.42.230.12]) by postgresql.org (Postfix) with ESMTP id C6B069DCCDF for ; Wed, 1 Mar 2006 06:22:42 -0400 (AST) Received: from Zeus (84.120.16.81) by resmta03.ono.com (7.2.052) id 43C1E4D40047CFF0 for pgsql-sql@postgresql.org; Wed, 1 Mar 2006 11:17:11 +0100 From: "Mauricio Fernandez A." To: Subject: Re: how to add primary key to existing table with multiple primary keys Date: Wed, 1 Mar 2006 11:17:15 +0100 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0026_01C63D21.B1C9DF10" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal In-Reply-To: <20060224015148.A9BB99DC9E3@postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.632 required=5 tests=[AWL=-0.288, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44, HTML_MESSAGE=0.001] X-Spam-Score: 1.632 X-Spam-Level: * X-Archive-Number: 200603/3 X-Sequence-Number: 24349 This is a multi-part message in MIME format. ------=_NextPart_000_0026_01C63D21.B1C9DF10 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Your table has a primary key yet, so you can?t add a second primary key. You only can define one pk, however it can be composed as your table has (it now has 2 columns), so I think you need to drop the current pk and add again the new pk with your 3 columns (probeid, tissueid, expid). Review this http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html#AEN2038 Mauricio Fernandez A. Ingeniero de Sistemas Universidad Autonoma de Manizales (Colombia) -----Mensaje original----- De: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]En nombre de Daniel Joo Enviado el: viernes, 24 febrero, 2006 2:45 Para: pgsql-sql@postgresql.org Asunto: [SQL] how to add primary key to existing table with multiple primary keys Hi there, 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: alter table extprobe2tissue ADD primary key (expid); ERROR: multiple primary keys for table "extprobe2tissue" are not allowed The details of the extprobe2tissue table is: \d extprobe2tissue; Table "public.extprobe2tissue" Column | Type | Modifiers -------------+------------------------+----------- probeid | integer | not null tissueid | integer | not null value | double precision | not null entrydate | date | not null description | character varying(200) | qval | double precision | expid | integer | not null Indexes: "extprobe2tissue_pkey" PRIMARY KEY, btree (probeid, tissueid) "extprobe2tissue_probeid" btree (probeid) "extprobe2tissue_tissueid" btree (tissueid) Foreign-key constraints: "extprobe2tissue_tissueid_fkey" FOREIGN KEY (tissueid) REFERENCES tissue(tissueid) "extprobe2tissue_probeid_fkey" FOREIGN KEY (probeid) REFERENCES extprobe(probeid) Any suggestion would be much appreciated. Thanks! ------=_NextPart_000_0026_01C63D21.B1C9DF10 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
Your=20 table has a primary key yet, so you can´t add a second primary = key.  You=20 only can define one pk, however it can be composed as your table has (it = now has=20 2 columns), so I think you need to drop the current pk and add again the = new pk=20 with your 3 columns (probeid, tissueid, expid).
 
Review this http://www.postgresql.org/docs/8.1/interactive/ddl-constr= aints.html#AEN2038
 

Mauricio = Fernández=20 A.
Ingeniero de Sistemas
Universidad Autónoma de Manizales = (Colombia)

-----Mensaje original-----
De: = pgsql-sql-owner@postgresql.org=20 [mailto:pgsql-sql-owner@postgresql.org]En nombre de Daniel=20 Joo
Enviado el: viernes, 24 febrero, 2006 = 2:45
Para:=20 pgsql-sql@postgresql.org
Asunto: [SQL] how to add primary = key to=20 existing table with multiple primary keys

Hi=20 there,

 

I am trying to add = another primary=20 key to an existing table with two other primary keys.  I got the=20 following error when I tried this = command:

 

alter table = extprobe2tissue ADD=20 primary key (expid);

ERROR:  multiple = primary keys=20 for table "extprobe2tissue" are not = allowed

 

The details of the = extprobe2tissue=20 table is:

 

\d=20 extprobe2tissue;

         =20 Table "public.extprobe2tissue"

  =20 Column   =20 |         =20 Type          |=20 Modifiers

-------------+------------------------+-----------

 probeid    =20 |=20 = integer           =     =20 | not null

 tissueid    |=20 = integer           =     =20 | not null

 value      =20 | double precision       | not=20 null

 entrydate   |=20 = date           &nb= sp;      =20 | not null

 description | = character=20 varying(200) |

 qval       =20 | double precision      =20 |

 expid      =20 |=20 = integer           =     =20 | not null

Indexes:

   =20 "extprobe2tissue_pkey" PRIMARY KEY, btree (probeid,=20 tissueid)

   =20 "extprobe2tissue_probeid" btree (probeid)

   =20 "extprobe2tissue_tissueid" btree = (tissueid)

Foreign-key=20 constraints:

   =20 "extprobe2tissue_tissueid_fkey" FOREIGN KEY (tissueid) REFERENCES=20 tissue(tissueid)

   =20 "extprobe2tissue_probeid_fkey" FOREIGN KEY (probeid) REFERENCES=20 extprobe(probeid)

 

Any suggestion would be = much=20 appreciated.

 

Thanks! =20

------=_NextPart_000_0026_01C63D21.B1C9DF10--