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

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

Answer 2

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.

Answer 3

You could change your constraint to ON UPDATE CASCADE.

Answer 4

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.

Answer 5

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.