pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy table has drop, but the foreign key still there?
4+ messages / 4 participants
[nested] [flat]
* Why table has drop, but the foreign key still there?
@ 2003-08-13 18:14 Raymond Chui <raymond.chui@noaa.gov>
0 siblings, 3 replies; 4+ messages in thread
From: Raymond Chui @ 2003-08-13 18:14 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-admin@postgresql.org; pgsql-sql
Here are the simple things I did
create table state (
state_code char(2) not null,
state varchar(15) not null,
primary key (state_code)
);
create table whitepage (
user_id char(8) not null,
email varchar(50),
telephone char(16) not null,
contact_name varchar(30) not null,
city varchar(20),
state_code char(2),
primary key (user_id),
foreign key (state_code) references state (state_code)
);
insert into state (state_code,state) values ('GU','Guam');
drop table whitepage;
delete from state where state_code = 'GU';
ERROR: Relation "whitepage" does not exist
Why I got this error message??!!
The whitepage table already drop. When the table drop, will the
foreign key constraint also drop??
Now, order for me delete a row from state table, I HAVE TO
re-create whitepage table. That's silly!!!
What shall I do? How do I delete a row in state without
re-create the whitepage table???
Thank you very much in advance!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why table has drop, but the foreign key still there?
@ 2003-08-13 19:38 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Raymond Chui <raymond.chui@noaa.gov>
2 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2003-08-13 19:38 UTC (permalink / raw)
To: Raymond Chui <raymond.chui@noaa.gov>; +Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org; pgsql-sql
On Wed, 13 Aug 2003, Raymond Chui wrote:
>
> Here are the simple things I did
>
> create table state (
> state_code char(2) not null,
> state varchar(15) not null,
> primary key (state_code)
> );
>
> create table whitepage (
> user_id char(8) not null,
> email varchar(50),
> telephone char(16) not null,
> contact_name varchar(30) not null,
> city varchar(20),
> state_code char(2),
> primary key (user_id),
> foreign key (state_code) references state (state_code)
> );
>
>
> insert into state (state_code,state) values ('GU','Guam');
> drop table whitepage;
> delete from state where state_code = 'GU';
> ERROR: Relation "whitepage" does not exist
What version are you using? I can't seem to replicate this given the
above on 7.2, 7.3 or 7.4.
If these tables were preexisting and had gone through a dump cycle
from 7.0 or 7.1, there was a bug in pg_dump for those versions that
would lose the connection between the triggers and the other table
of the constraint.
In any case, you'll need to find and drop the two orphaned triggers on
state (see techdocs for information on how to find them).
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why table has drop, but the foreign key still there?
@ 2003-08-13 19:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Raymond Chui <raymond.chui@noaa.gov>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-08-13 19:53 UTC (permalink / raw)
To: Raymond Chui <raymond.chui@noaa.gov>; +Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org; pgsql-sql
Raymond Chui <raymond.chui@noaa.gov> writes:
> drop table whitepage;
> delete from state where state_code = 'GU';
> ERROR: Relation "whitepage" does not exist
> Why I got this error message??!!
I could not duplicate this error. What PG version are you running?
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Why table has drop, but the foreign key still there?
@ 2003-08-13 19:54 Rod Taylor <rbt@rbt.ca>
parent: Raymond Chui <raymond.chui@noaa.gov>
2 siblings, 0 replies; 4+ messages in thread
From: Rod Taylor @ 2003-08-13 19:54 UTC (permalink / raw)
To: Raymond Chui <raymond.chui@noaa.gov>; +Cc: pgsql-general@postgresql.org; pgsql-admin@postgresql.org; pgsql-sql
> insert into state (state_code,state) values ('GU','Guam');
> drop table whitepage;
> delete from state where state_code = 'GU';
> ERROR: Relation "whitepage" does not exist
Old version of PostgreSQL? Effort went into cleaning up inter-object
dependencies in 7.3. I don't recall having that particular issue in
versions prior either.
That said, you can remove the triggers that are on table "state"
beginning with the characters "RI". psql -d state will tell you what
they are.
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-08-13 19:54 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-13 18:14 Why table has drop, but the foreign key still there? Raymond Chui <raymond.chui@noaa.gov>
2003-08-13 19:38 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-13 19:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-08-13 19:54 ` Rod Taylor <rbt@rbt.ca>
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