Re: FOREIGN KEY errors.

There was a bug (which should be fixed for 7.1) that got the
arguments wrong for the alter time check of the existing data.
I think I should be able to get a patch together to fix it once
I get a copy of the 7.0.3 source.

Can you send the table schema as well so I can test it out?

Stephan Szabo
sszabo(at)bigpanda(dot)com

On Thu, 7 Dec 2000, Joseph Shraibman wrote:

> 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 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 <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(at)selectacast(dot)net
> Increase signal to noise ratio. http://www.targabot.com
>