Received: from thor.tht.net (thor.tht.net [209.47.145.4]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eB84NFZ34618 for ; Thu, 7 Dec 2000 23:23:15 -0500 (EST) (envelope-from jks@selectacast.net) Received: from p1.selectacast.net (IDENT:root@p1.selectacast.net [206.41.1.251]) by thor.tht.net (8.9.3/8.9.3) with ESMTP id AAA01942 for ; Fri, 8 Dec 2000 00:23:29 GMT (envelope-from jks@selectacast.net) Received: from selectacast.net (IDENT:jks@d1 [209.219.91.66]) by p1.selectacast.net (8.9.3/8.9.3) with ESMTP id XAA14185 for ; Thu, 7 Dec 2000 23:23:09 -0500 Message-ID: <3A30622B.468EB383@selectacast.net> Date: Thu, 07 Dec 2000 23:23:07 -0500 From: Joseph Shraibman Organization: Xtenit, Inc. http://www.xtenit.com X-Mailer: Mozilla 4.75 [en] (X11; U; Linux 2.2.15 i686) X-Accept-Language: en MIME-Version: 1.0 To: "pgsql-sql@postgresql.org" Subject: FOREIGN KEY errors. Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200012/58 X-Sequence-Number: 389 When trying to alter a table and add a foreign key, I am getting this error if the table has any data in it: playpen=# alter table message add FOREIGN KEY (pod,originator) REFERENCES usertable (podkey,userkey); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: constraint : table usertable does not have an attribute originator If I do the alter before I put any data in the table: playpen=# alter table message add FOREIGN KEY (pod,originator) REFERENCES usertable (podkey,userkey); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) CREATE playpen=# select version(); version --------------------------------------------------------------------- PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 (1 row) Reversing the order creates a different message: playpen=# alter table message add FOREIGN KEY (originator,pod) REFERENCES usertable (userkey,podkey); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: constraint : table usertable does not have an attribute pod Am I just misunderstanding how to use FOREIGN KEY? Then why would it work one time and not the other? http://www.postgresql.org/docs/aw_pgsql_book/node159.html doesn't have any column names after 'refrences '. -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com