如何使用T-SQL临时禁用外键约束？ [英] How can foreign key constraints be temporarily disabled using T-SQL?

问题描述

是否禁用和启用SQL Server中支持的外键约束？或者是 drop 和 re - 创建 约束？

解决方案

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

   - 禁用所有约束
 EXEC sp_MSforeachtableALTER TABLE？NOCHECK CONSTRAINT all
  / pre> 
 
 
要打开它们，请运行：（打印是可选的，只是列出表格）
   - 启用所有约束
 exec sp_MSforeachtable @ command1 =print'？'，@ command2 =ALTER TABLE？WITH CHECK CHECK CONSTRAINT all
  
我发现在将数据从一个数据库填充到另一个数据库时很有用。这是比放弃约束好得多的方法。正如你所提到的，当删除数据库中的所有数据并重新填充它（例如在测试环境中）时，它很方便。 
 
 
 
如果您要删除所有数据，可以找到这个解决方案有用。
 
 
 
触发器，您可以查看完整的解决方案这里。
 
Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints?
解决方案
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.

                        
这篇关于如何使用T-SQL临时禁用外键约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！