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

Question

I know the question of how to update multiple tables in SQL has been asked before and the common answer seems to be do them separately in a transaction.

However, the 2 columns I need to update have a foreign key so cannot be updated separately.

e.g.

Table1.a is a foreign key to Table2.a

One of the entries in the tables is wrong, e.g. both columns are 'xxx' and should be 'yyy'

How do I update Table1.a and Table2.a to be 'yyy'?

I know I could temp remove the key and replace but surely there's another way.

Thanks

Samp

For those who find this question but are looking for the PostgresQL answer, see #34383912. – Humbert

Answer 1

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

Menhaden

Thanks, that clears up the fact that I can't. Cascade works just fine. – Samp

Answer 2

Not being a fan of on update cascade, I would suggest a different route.

First you do not update the Parent table, you add a new record with the value you want (and the same data as the other record for all other fields). Then you have no difficulty updating the child tables to use this value instead of that value. Further you now have the ability to to do the work in batches to avoid locking the system up while the change promulgates through it. Once all the child tables have been updated, you can delete the original bad record.

Claudine

Thanks for your answer which sounds fine except I need to keep the same identity fields and there are unique indexes on 4 other columns as well which would restrict their duplication required for your method. – Samp

@Claudine What could go wrong with update cascade? – Damali

Answer 3

my answer is based on the following link: http://msdn.microsoft.com/en-us/library/ms174123%28v=SQL.90%29.aspx

You need to make sure that your table_constraint will be defined as ON UPDATE CASCADE

          CREATE TABLE works_on1
         (emp_no INTEGER NOT NULL,
          project_no CHAR(4) NOT NULL,
          job CHAR (15) NULL,
          enter_date DATETIME NULL,
          CONSTRAINT prim_works1 PRIMARY KEY(emp_no, project_no),
          CONSTRAINT foreign1_works1 FOREIGN KEY(emp_no) REFERENCES employee(emp_no) ON DELETE CASCADE,
          CONSTRAINT foreign2_works1 FOREIGN KEY(project_no) REFERENCES project(project_no) ON UPDATE CASCADE)

and then when you will change the value of your primary key

see the following quote:

For ON DELETE or ON UPDATE, if the CASCADE option is specified, the row is updated in the referencing table if the corresponding referenced row is updated in the parent table. If NO ACTION is specified, SQL Server Compact Edition returns an error, and the update action on the referenced row in the parent table is rolled back.

For example, you might have two tables, A and B, in a database. Table A has a referential relationship with table B: the A.ItemID foreign key references the B.ItemID primary key.

If an UPDATE statement is executed on a row in table B and an ON UPDATE CASCADE action is specified for A.ItemID, SQL Server Compact Edition checks for one or more dependent rows in table A. If any exist, the dependent rows in table A are updated, as is the row referenced in table B.

Alternatively, if NO ACTION is specified, SQL Server Compact Edition returns an error and rolls back the update action on the referenced row in table B when there is at least one row in table A that references it.

Septempartite

Answer 4

you can also drop constrant, do changes and put constraint back again.

Viscus

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Warms

Moreover, OP already knew this as (s)he wrote "I know I could temp remove the key and replace but surely there's another way." – Rempe

Answer 5

If you get error like below, you can use dependentTable.

ERROR: update or delete on table "table1" violates foreign key constraint "table_fk1" on table "table2" Detail: Key (sth, sth)=(sth,sth) is still referenced from table "table2".;

WITH dependentTable AS (
    UPDATE table1
        SET column2 = 'sth1'
        WHERE column1 = 'sth2'
        RETURNING *
)
UPDATE table2
SET column2 = 'sth1'
WHERE column1 = 'sth2';

Memling