Обсуждение: FOREIGN KEY errors.

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

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@bigpanda.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@selectacast.net
> Increase signal to noise ratio.  http://www.targabot.com
> 

Script to reproduce the problem.  It drops the tables at the end of the
script.

Stephan Szabo wrote:
> 
> 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?
> 

Script to reproduce the problem.  It drops the tables at the end of the
script.  If data is not inserted into mtable there is no problem.
========================================================================================

create table utable (        uk int not null,pk int not null,thetext text,primary key (uk, pk) 
);    

create table mtable(id serial not null primary key,mtext text,o int,  /* corresponds to uk */p int  /* corresponds to
pk*/
 
);

insert into utable (uk,pk,thetext) values (2,4,'blah 2 4');
insert into utable (uk,pk,thetext) values (2,5,'blah 2 5');

insert into mtable (mtext,o,p) values ('m 2 4',2,4);


alter table mtable add FOREIGN KEY (p,o) REFERENCES utable (pk,uk);

drop sequence mtable_id_seq;
drop table mtable;
drop table utable;



-- 
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com