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 <unnamed>: 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
gccegcs-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 <unnamed>: 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 <tablename>'.
--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com
