Reputation: 4886

SQL update a table with a foreign key

I have a categories table, which one of the fields serves as the foreign key for a sub-categories table. One field that serves as part of the primary key for each table is the language id. I need to update these in both tables. Basically, wherever the language id = x in both tables, I need to set it to y.

When I try to do an update on either table, I get a 'The UPDATE statement conflicted with the REFERENCE constraint..' which refers to the foreign key constraint.

How can I update the language field on both of these tables?

Upvotes: 16

Answers (8)

Ankit

Reputation: 13

You can create a new row, with the updated data and delete the old rows.

Upvotes: 0

Yousra ADDALI

Reputation: 412

Drop the constraint, UPDATE and then ADD the constraint back

ALTER TABLE X DROP CONSTRAINT constraint_fkey;

UPDATE TABLE Y SET col1='', id=2 WHERE id=3;

ALTER TABLE X ADD CONSTRAINT constraint_fkey FOREIGN KEY (X_id) REFERENCES Y(id);

postgres (PostgreSQL) 14.5

Upvotes: 0

Mehsaan1719

Reputation: 91

Use this, no need to remove and add as well.

ALTER TABLE Table_Name
    NOCHECK CONSTRAINT FoerignKey_Name

--update query here

ALTER TABLE Table_Name
    CHECK CONSTRAINT FoerignKey_Name

For more info MSDN link : https://learn.microsoft.com/en-us/sql/relational-databases/tables/disable-foreign-key-constraints-with-insert-and-update-statements?view=sql-server-ver15

Upvotes: 7

Philip Kelley

Reputation: 40359

I'm always leery about disabling constraints, and you really don't want to do that if this is a common operation.

An admittedly ugly alternative is to: - Create a row in the parent table, based on the row to be updated but containing the new foreign key value - Update all child rows where the foreign key contains the old value with the new value. - Delete the now-unused parent key row

This is awkward for any number of obvious reasons, and may not be suitable to your implementation, but it maintains referential integrity within the database.

Upvotes: 2