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

问题描述

在 SQL Server 中是否支持禁用和启用外键约束?或者是我删除然后重新创建约束的唯一选择?

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屋！