Re: [GENERAL] Foreign Keys Help Delete! - Mailing list pgsql-sql
From | Stephan Szabo |
---|---|
Subject | Re: [GENERAL] Foreign Keys Help Delete! |
Date | |
Msg-id | Pine.BSF.4.10.10009201107150.57382-100000@megazone23.bigpanda.com Whole thread Raw |
In response to | Re: [GENERAL] Foreign Keys Help Delete! (Josh Berkus <josh@agliodbs.com>) |
List | pgsql-sql |
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.