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 th开发者_如何学Ce REFERENCE constraint..' which refers to the foreign key constraint.

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

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.

You need to drop the constraints, update the values, and then re-add the constraints.

You could change your constraint to ON UPDATE CASCADE.

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

You are going to have to disable the constraints first, do the update and then enable the constraints again.

Check out this article by OdeToCode discussing this.

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.

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

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