pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFOREIGN KEY errors.
3+ messages / 2 participants
[nested] [flat]
* FOREIGN KEY errors.
@ 2000-12-08 04:23 Joseph Shraibman <jks@selectacast.net>
2000-12-08 17:23 ` Re: FOREIGN KEY errors. Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 3+ messages in thread
From: Joseph Shraibman @ 2000-12-08 04:23 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: FOREIGN KEY errors.
2000-12-08 04:23 FOREIGN KEY errors. Joseph Shraibman <jks@selectacast.net>
@ 2000-12-08 17:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-12-12 01:45 ` Re: FOREIGN KEY errors. Joseph Shraibman <jks@selectacast.net>
0 siblings, 1 reply; 3+ messages in thread
From: Stephan Szabo @ 2000-12-08 17:23 UTC (permalink / raw)
To: Joseph Shraibman <jks@selectacast.net>; +Cc: pgsql-sql
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: FOREIGN KEY errors.
2000-12-08 04:23 FOREIGN KEY errors. Joseph Shraibman <jks@selectacast.net>
2000-12-08 17:23 ` Re: FOREIGN KEY errors. Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2000-12-12 01:45 ` Joseph Shraibman <jks@selectacast.net>
0 siblings, 0 replies; 3+ messages in thread
From: Joseph Shraibman @ 2000-12-12 01:45 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-12-12 01:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-08 04:23 FOREIGN KEY errors. Joseph Shraibman <jks@selectacast.net>
2000-12-08 17:23 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-12-12 01:45 ` Joseph Shraibman <jks@selectacast.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox