SQL update a table with a foreign key

Question

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?

Answer 1

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://docs.microsoft.com/en-us/sql/relational-databases/tables/disable-foreign-key-constraints-with-insert-and-update-statements?view=sql-server-ver15

Answer 2

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.