PostgreSQL 11.1
I have struggled with this problem for a long time. (I have tried to improve the question from before).
Problem: A person has two different names in table tempA. Each name has its associated records in table tempB. How can I move all the records associated with one name to the other name, then delete the name?
Example: I have two names –“Tom” and “Bob”. I want to change all the records associated with “Bob” to “Tom”, then remove “Bob” from the database.
How is this done while keeping the associated records in table tempb?
CREATE TABLE tempA ( id serial PRIMARY KEY, name text UNIQUE NOT NULL ); CREATE TABLE tempb ( id serial PRIMARY KEY, tempa_id integer NOT NULL, description text NOT NULL, CONSTRAINT foo_bar_fk FOREIGN KEY (tempa_id) REFERENCES tempa (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ) INSERT INTO tempA (name) VALUES('tom'); INSERT INTO tempA (name) VALUES('bob'); INSERT INTO tempB (tempA_id, description) SELECT id, 'test1' FROM tempA WHERE tempA.name = 'tom'; INSERT INTO tempB (tempA_id, description) SELECT id, 'test2' FROM tempA WHERE tempA.name = 'tom'; INSERT INTO tempB (tempA_id, description) SELECT id, 'test3' FROM tempA WHERE tempA.name = 'bob'; INSERT INTO tempB (tempA_id, description) SELECT id, 'test4' FROM tempA WHERE tempA.name = 'bob'; Initial set: -- tempA id name 1 "tom" 2 "bob" id tempA_id description 1 1 "test1" 2 1 "test2" 3 2 "test3" 4 2 "test4"
The goal I am trying to reach is:
--Desired Results -- tempA id name 1 "tom" -- tempB id tempA_id description 1 1 "test1" 2 1 "test2" 3 1 "test3" 4 1 "test4"
This is what I’ve tried, but it continues to fail:
BEGIN; SET CONSTRAINTS ALL DEFERRED; -- from 'tom' to 'bob' -- when all is done 'tom' must be the name to keep. WITH _in (name1, name2) AS( VALUES('tom','bob') ), _bob AS( -- DELETING 'bob' record FROM tempA. DELETE FROM tempA USING _in WHERE (tempA.name = _in.name2) RETURNING tempA.* ) UPDATE tempA -- REPLACING 'bob' with 'tom'. REPLACING 'bobs' id with 'toms' id. SET name = _in.name1, id = _tom.id FROM _in JOIN _bob ON (_bob.name = _in.name2) JOIN tempA _tom ON (_tom.name = _in.name1) WHERE (tempA.id = _bob.id); COMMIT;
ERROR: update or delete on table “tempa” violates foreign key constraint “foo_bar_fk” on table “tempb” DETAIL: Key (id)=(2) is still referenced from table “tempb”.
It seems I cannot get the UPDATE to occur before the Delete is enforced.
Any help is most appreciated. TIA
Advertisement
Answer
You would need to update
the child table first, then delete
from the parent – the foreign key constraints prevents you from proceeding the other way around.
Consider:
with names (to_keep, to_del) as(values('tom','bob')), upd as ( update tempB set tempA_id = a_keep.id from names n inner join tempA a_keep on a_keep.name = n.to_keep inner join tempA a_del on a_del.name = n.to_del where tempB.tempA_id = a_del.id returning a_del.id ) delete from tempA using upd where tempA.id = upd.id