如何禁用所有表的约束并启用它？ [英] How to disable Constraints for all the tables and enable it?

问题描述

我有60张表。我想删除10个表，其中这10个表是约束（PK，FK）到其他20个表。在删除这10个表时，我需要截断或删除其他20个表中的数据。最后，我想禁用所有60表约束（FK，PK），然后启用所有60表约束后，我完成我的工作添加/删除表。这是可能的吗？

当我删除一个表，它要求FK。当我截断那些FK依赖，它也仍然显示相同。我不想混淆所有的FK，PK。

我想知道更聪明的方法。

解决方案

  EXEC sp_MSforeachtable @ command1 =ALTER TABLE？NOCHECK CONSTRAINT ALL
 GO 
  / pre> 
 
 
您也可以这么做：
  EXEC sp_MSforeachtable @ command1 =ALTER TABLE？DISABLE TRIGGER ALL
 GO 
  
 
  EXEC sp_MSforeachtable @ command1 =ALTER TABLE？ENABLE TRIGGER ALL
 GO 
 
 -  SQL启用所有约束 - 启用所有约束sql server 
  -  sp_MSforeachtable是一个未记录的系统存储过程
 EXEC sp_MSforeachtable @ command1 =ALTER TABLE？CHECK CONSTRAINT ALL
 GO 
  
 编辑：
 
如果禁用约束不够，你将不得不删除约束。
 
 
 
如果你删除并重新创建表，你将不得不重新创建外键约束。
 
 
 
如果你只是需要删除约束，你可能会发现这很有用：
 
  SQL DROP TABLE外键约束 
 
 
 
如果您需要编写一个脚本来删除并创建约束，你可能会发现我的帖子更有用：
 
  SQL Server：如何从information_schema获取外键参考？ 
 
I have 60 tables. I want to drop 10 tables where these 10 tables are Constraints(PK,FK) to other 20 tables. While dropping these 10 tables, I need to truncate or delete data from the other 20 tables. Finally I want to disable all 60 table Constraints(FK,PK) and then enable all 60 table constraints after I am done with my work of adding/dropping tables. Is this possible?

When I drop a table it is asking for FK. When I truncate those FK dependencies it also is still showing the same. I don't want to mess with all those FK,PK.

I want to know smarter method.
解决方案
EXEC sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO
You may also want to do this:
EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"
GO
To enable them afterwards
EXEC sp_MSforeachtable @command1="ALTER TABLE ? ENABLE TRIGGER ALL"
GO

-- SQL enable all constraints - enable all constraints sql server
-- sp_MSforeachtable is an undocumented system stored procedure
EXEC sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"
GO
Edit:

If disabling the constraints is not enough, you will have to drop the constraints.

If you're dropping and recreating the tables, you will have to recreate the foreign key constrains afterwards.

If you just need to drop the constrains, you might find this useful:

SQL DROP TABLE foreign key constraint

If you need to write a script to drop and create the constraints, you might find my post here more useful:

SQL Server: Howto get foreign key reference from information_schema?

                        
这篇关于如何禁用所有表的约束并启用它？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！