Search code examples
sqlsql-servert-sqlforeign-keys

Disable Constraints on Bulk Update in SQL Server

I need to change the values of a PK/FK (add 10000) on 2 tables. How do I tell the two tables involved that they should not care about referential integrity during the update, but to care after. I don't want to have to drop and recreate the relationships if I don’t have to.

Solution

  • Your FK should have a "ON UPDATE CASCADE" option.

    ALTER TABLE child CHANGE myfkconst FOREIGN KEY id REFERENCES parent (id) ON UPDATE CASCADE;

    (or something like that. not 100% sure about the syntax)

    And then you can just do

    UPDATE parent SET id = id + 10000 WHERE id = something

    and the child table will be updated automatically.