CODEWITHSUNDEEP
sqlsql-servert-sqlforeign-keys
Ian Vink
Ian Vink

Reputation: 68810

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.

Upvotes: 1

Views: 1991

Answers (4)

rosscj2533
rosscj2533

Reputation: 9323

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

Upvotes: 2

Daniel Vassallo
Daniel Vassallo

Reputation: 344481

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?

Upvotes: 4

Tor Valamo
Tor Valamo

Reputation: 33769

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.

Upvotes: 2

David M
David M

Reputation: 72920

Sorry, you have to. No option.

Upvotes: 0

Related Questions