Disable Constraints on Bulk Update in SQL Server

Question

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.

Answer 1

You may want to disable all the constraints in the database by executing the following command:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all";

Then switching them back on with:

EXEC sp_msforeachtable @command1="print '?'", 
                       @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all";

Source: Stack Overflow - Can foreign key constraints be temporarily disabled using TSQL?

Answer 2

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.

Answer 3

This link describes how to temporarily disable a contraint. I have not tested it.

-- disable constraint
ALTER TABLE table_name NOCHECK CONSTRAINT constraint_name
-- enable constraint
ALTER TABLE table_name CHECK CHECK CONSTRAINT constraint_name

Answer 4

Sorry, you have to. No option.