sql-server - 如何使用T-SQL临时禁用外键约束？(How can foreign key constraints be temporarily disabled using T-SQL?)

Question

Are disabling and enabling foreign key constraints supported in SQL Server?

(是否禁用和启用SQL Server支持的外键约束？)

Or is my only option to drop and then re- create the constraints?

(或者是我唯一的选择是drop然后重新create约束？)

  ask by Ray Vega translate from so

Answer 1

If you want to disable all constraints in the database just run this code:

(如果要禁用数据库中的所有约束，只需运行以下代码：)

-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

To switch them back on, run: (the print is optional of course and it is just listing the tables)

(要重新打开它们，请运行:(打印是可选的，当然只是列出表格）)

-- enable all constraints
exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

I find it useful when populating data from one database to another.

(我发现将数据从一个数据库填充到另一个数据库时很有用。)

It is much better approach than dropping constraints.

(这比删除约束要好得多。)

As you mentioned it comes handy when dropping all the data in the database and repopulating it (say in test environment).

(正如您所提到的，在将所有数据放入数据库并重新填充数据库时（例如在测试环境中），它非常方便。)

If you are deleting all the data you may find this solution to be helpful.

(如果要删除所有数据，您可能会发现此解决方案很有帮助。)

Also sometimes it is handy to disable all triggers as well, you can see the complete solution here .

(有时也可以方便地禁用所有触发器，您可以在此处查看完整的解决方案。)