Disable Constraints on Bulk Update in SQL Server

jchrr9hc 于 2023-11-16 发布在 SQL Server

关注(0)|答案(4)|浏览(135)

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.

sql-server

来源：https://stackoverflow.com/questions/2072991/disable-constraints-on-bulk-update-in-sql-server

4条答案

按热度按时间

3yhwsihp1#

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?

赞(0）回复(0）举报 2023-11-16

3okqufwl2#

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.

赞(0）回复(0）举报 2023-11-16

n6lpvg4x3#

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

赞(0）回复(0）举报 2023-11-16

4szc88ey4#

Sorry, you have to. No option.

赞(0）回复(0）举报 2023-11-16