How to update rows of two tables that have foreign key restrictions

Question

I have two tables: one is foreign reference table lets say table a and other one is the data table lets say table b. Now, when I need to change the data in table b, but I get restricted by table a. How can I change "rid" in both tables without getting this message?

"ERROR: insert or update on table "table a" violates foreign key constraint "fk_boo_kid" SQL state: 23503

Detail: Key (kid)=(110) is not present in table "table b".

Example query to update both tables:

UPDATE table b table a SET rid = 110 WHERE rid =1

table b 
+-----+-------+-------+
| rid | ride  | qunta |
+-----+-------+-------+
|   1 |  car  |     1 |
|   2 |  bike |     1 |
+-----+-------+-------+  

table a
+-----+-----+------------+
| kid | rid |    date    |
+-----+-----+------------+
|   1 |   1 | 20-12-2015 |
|   2 |   2 | 20-12-2015 |
+-----+-----+------------+

Elliott

@Elliott . . . I have removed the extraneous database tags. Feel free to add the one(s) for the database you are actually using. – Roswell

As long as you have foreign keys enabled, you will have to comply with them. So either disable the foreign keys for the duration of the operation which is done often in database imports and sometimes bulk operations, or add a dummy key for the duration of your operation (dangerous in active systems, tricky in batch operations) – Glory

Answer 1

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

Hype

Thank you so much, I have read a lot about that foreign key must be deleted before performing such action, but your method worked perfectly. Btw, I'm using Postgresql and I had it in the tags but Gordon Linoff had removed my tags – Elliott

@H35am: Gordon removed the tag, because you had three different DBMS mentioned there which usually results in answers that aren't useful to you if you are not really using all of them – Hype

@a_horse_with_no_name This is great when it is already known that table b references table a. But what if this is not known ahead of time? – Tapster

Boy did this save me after a production DB issue. I was able to update 8 tables all at once. For others, that works in the form "with q as (... ), a as(... ), b as (...) update c..... " – Pound

Genius! Love it! – Thermodynamic

Are there situations where this doesn't work? I have 15 tables with FKs on the same table (all deferrable initially deferred), but regardless of where I place the PK update in the CTE, I still get an integrity error that one of the updated tables still references the old PK. The PK has an INCLUDE statement, but otherwise it is the same as the example (it's PG 13 BTW). – Finegrain

Answer 2

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 >>

1. you must delete the row in table A which is used primary key table B.
2. you can delete the row in table B

Chiliarch

With Postgres and Oracle you can do that using deferrable constraints. And in Postgres you can actually do it in a single statement. – Hype

Answer 3

you have to change both manual

SET session_replication_role = 'replica';
UPDATE table a SET rid=110 WHERE rid=1 ;
UPDATE table b SET rid=110 WHERE rid=1 ;
SET session_replication_role = 'origin';

Krigsman

@ctholho Thank you! This was the solution that worked for me, please post it as an answer so I can upvote the answer! (or should we just edit this answer? I'm not sure of the SO etiquette) – Palfrey

Answer 4

This is too long for a comment.

You should really explain why you want to change ids into something else. Primary keys really should be considered immutable, so they identify rows both within a table and over time.

If you do need to change them for some reason, then define proper foreign key constraints for the tables in question. Then define the foreign keys to be on update cascade. This will "cascade" changes to all affected changes when a primary key changes.

Roswell

wow Mr Linoff himsself got a negative score for an answer ) – Kislev