How to Update Rows of Two Tables That Have Foreign Key Restrictions

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

How to update 2 columns in 2 tables that have foreign key

You can't do the update simultaneously, however you can force SQL to do the update. You need to make sure your foreign keys have the referential triggered action ON UPDATE CASCADE

e.g.

ALTER TABLE YourTable
ADD CONSTRAINT FK_YourForeignKey
FOREIGN KEY (YourForeignKeyColumn) 
REFERENCES YourPrimaryTable (YourPrimaryKeyColumn) ON UPDATE CASCADE

update values of table based on foreign key

Update with JOIN

-- MySQL
UPDATE Table2
INNER JOIN Table1 
        ON Table2.col1 = Table1.id
SET Table2.col2 = Table1.col1;

Please check from url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a28fec2da45aa634f2509ec9299c2bed

INSERT or UPDATE Table with Foreign Key Constraints

The order of your create table and insert statements needs to be reversed. When olympic.tb_register comes to life it needs to already see olympic.tb_athlete for its reference constraints against that table to make sense - you can't force a table to make sure its rows match a not-yet-existing table.

It's the same situation for inserts: when a new row enters olympic.tb_register, it has to already see its corresponding row in olympic.tb_athlete. Otherwise it'll think someone is trying to register a non-existing athlete.

You can also use on delete cascade and on update cascade in your reference constraint so that you only need to issue a delete/update on tb_athlete, which will automatically delete/update the corresponding record in tb_register:

  CONSTRAINT fk_register_athlete 
     FOREIGN KEY (athlete_id) 
     REFERENCES olympic.tb_athlete (athlete_id)
     ON DELETE CASCADE
     ON UPDATE CASCADE,

SQL Server update primary key that's also a foreign key in two tables

You may:

1. disable enforcing FK constraints temporarily (see here or here)
2. update your PK
3. update your FKs
4. enable back enforcing FK constraints

do it all within a transaction and make sure that if transaction fails, you roll it back properly and still enforce the FK constraints back.

But... why do you need to change a PK? I hope this is an action that is executed rarely (legacy data import or something like that).

SQL update a table with a foreign key

If you are doing a 1 time fix, drop the constraint, UPDATE and then add the constraint back.

If you need to do this as part of the application, insert one category row, update the sub-categories row, and then delete original category row.

Updating a PostgreSQL table based on a foreign key match with another table

update second_table t2
set 
    column_c = true 
from 
    first_table t1 
where 
    t1.column_b = t2.column_b 
    and t1.column_a = 123
returning 
    t1.column_b, t2.column_c, t2.column_e; 

Result: 
column_b    column_c    column_e
a           true        horse

---

Related Topics