Re: [GENERAL] Foreign Keys Help Delete!

On Wed, 20 Sep 2000, Josh Berkus wrote:

> Timothy, Tom:
>
> > >1. a. Create new record with new key value in hosts table with the
> > >desired value
> > > b. Update the routes record to reference the new value
> > > c. Delete the old record in the hosts table
> > >
> >
> > Yes, that's what I tried.
> >
> > 1. foo.old.com exists in "hosts" table and "routes" table
> > 2. create foo.new.com in "hosts" table
> > 3. delete foo.old.com in "routes" table
> > 4. add foo.new.com into "routes" table
> > 5. try to delete foo.old.com and it complains!
>
> Tom - not to interrupt your coding :-) this sounds like a bug. Any
> thoughts?

Probably doesn't need to go all the way to Tom... :)

Hmm, on my 7.0.2 box,
sszabo=# create table hosts (fqdn varchar(30));
CREATE
sszabo=# create table routes (fqdn varchar(30),foreign key(fqdn)
references hosts(fqdn));
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
CREATE
sszabo=# insert into hosts values ('foo.old.com');
INSERT 181159 1
sszabo=# insert into routes values ('foo.old.com');
INSERT 181160 1
sszabo=# begin;
BEGIN
sszabo=# insert into hosts values ('foo.new.com');
INSERT 181161 1
sszabo=# delete from routes where fqdn='foo.old.com';
DELETE 1
sszabo=# insert into routes values ('foo.new.com');
INSERT 181162 1
sszabo=# delete from hosts where fqdn='foo.old.com';
DELETE 1
sszabo=# end;
COMMIT

--

To original complainant:
Since you won't be able to post the trigger information either probably,
can you check pg_trigger to make sure there are no dangling constraint
triggers?
You should have three rows that look like:

181144 | RI_ConstraintTrigger_181153 | 1644 | 21 | t | t
| <unnamed> | 181120 | f | f | 6 |
| <unnamed>\000routes\000hosts\000UNSPECIFIED\000fqdn\000fqdn\000
181120 | RI_ConstraintTrigger_181155 | 1654 | 9 | t | t
| <unnamed> | 181144 | f | f | 6 |
| <unnamed>\000routes\000hosts\000UNSPECIFIED\000fqdn\000fqdn\000
181120 | RI_ConstraintTrigger_181157 | 1655 | 17 | t | t
| <unnamed> | 181144 | f | f | 6 |
| <unnamed>\000routes\000hosts\000UNSPECIFIED\000fqdn\000fqdn\000

Except that the relation oids are likely to be different (important ones
are the tgrelid and tgconstrrelid). The function oids (1644, 1654, 1655)
should be the same I believe.

> > >2. a. Drop the Foriegn Key constraint
> > > b. Update both the routes and hosts tables
> > > c. Re-establish the foriegn key constraint
> >
> > This is the part that I'm fuzzy on. I've tried this before
> > with complete DB corruption resulting. I had to dump each table
> > one by one, edit my schema with vi, create new DB, import tables
> > one by one....very painful!
>
> This also sounds like a problem. One should be able to drop a
> constraint, the re-create the restraint and check existing records
> against it. You can do this in MSSQL and Oracle.

Well, we don't have ALTER TABLE ... DROP CONSTRAINT right now. Dropping
the constraint requires removing the triggers manually. We can do an
ADD CONSTRAINT which will check the data, but not the corresponding DROP.