From djoo@vistagen-inc.com Fri Feb 24 01:51:52 2006 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 126E39DCA22 for ; Thu, 23 Feb 2006 21:51:52 -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 13966-05 for ; Thu, 23 Feb 2006 21:51:54 -0400 (AST) X-Greylist: delayed 00:06:38.815395 by SQLgrey- Received: from smtp109.sbc.mail.mud.yahoo.com (smtp109.sbc.mail.mud.yahoo.com [68.142.198.208]) by postgresql.org (Postfix) with SMTP id A9BB99DC9E3 for ; Thu, 23 Feb 2006 21:51:48 -0400 (AST) Received: (qmail 6160 invoked from network); 24 Feb 2006 01:45:13 -0000 Received: from unknown (HELO DMJVGEN) (franklin.rice@sbcglobal.net@64.165.39.190 with login) by smtp109.sbc.mail.mud.yahoo.com with SMTP; 24 Feb 2006 01:45:12 -0000 From: "Daniel Joo" To: Subject: how to add primary key to existing table with multiple primary keys Date: Thu, 23 Feb 2006 17:45:03 -0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0000_01C638A0.E0E1E020" X-Mailer: Microsoft Office Outlook, Build 11.0.6353 Thread-Index: AcY448Z/2//8OVVaRkekT7CMEVCfpw== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Message-Id: <20060224015148.A9BB99DC9E3@postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.928 required=5 tests=[HTML_MESSAGE=0.001, MSGID_FROM_MTA_ID=0.927] X-Spam-Score: 0.928 X-Spam-Level: X-Archive-Number: 200602/331 X-Sequence-Number: 24341 This is a multi-part message in MIME format. ------=_NextPart_000_0000_01C638A0.E0E1E020 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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_0000_01C638A0.E0E1E020 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 &nb= sp; | 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_0000_01C638A0.E0E1E020-- From sszabo@megazone.bigpanda.com Wed Mar 1 02:54:06 2006 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) From mfacontacto@ono.com Wed Mar 1 10:22:45 2006 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--