How to update rows of two tables that have foreign key restrictions
In Postgres you can use a writeable CTE to update both tables in a single statement.
Assuming this table setup:
create table a (rid integer primary key, ride text, qunta integer);create table b (kid integer primary key, rid integer references a, date date);The CTE would be:
with new_a as ( update a set rid = 110 where rid = 1)update b set rid = 110 where rid = 1;As (non-deferrable) foreign keys are evaluated on statement level and both the primary and foreign key are changed in the same statement, this works.
SQLFiddle: http://sqlfiddle.com/#!15/db6d1/1
you can not update/delete primary key in table B, because the primary key is used in table A.
you can delete primary key in table B, IF >>
- you must delete the row in table A which is used primary key table B.
- you can delete the row in table B
you have to change both manual
SET foreign_key_checks = 0;UPDATE table a SET rid=110 WHERE rid=1 ;UPDATE table b SET rid=110 WHERE rid=1 ;SET foreign_key_checks = 1;