我正在寻找一种方法来暂时关闭所有数据库约束（例如表之间的关系）。
我需要将一个数据库中的表使用 INSERT 命令复制到另一个数据库中。 我知道可以通过按正确顺序执行命令来实现此操作（以避免破坏关系）。
但如果能够在操作完成后暂时关闭并重新启用检查约束条件，那将更加容易。
这种做法可行吗？
5个回答
318
-- Disable the constraints on a table called tableName:
ALTER TABLE tableName NOCHECK CONSTRAINT ALL
-- Re-enable the constraints on a table called tableName:
ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL
---------------------------------------------------------
-- Disable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
-- Re-enable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
---------------------------------------------------------
- Donal
5
1@kevinc 不需要。只要你保持一致就可以了。 - Po-ta-toe
2使用带引号的标识符是我认为符合ANSI标准的设置，这意味着您不应该将其用于字符串。这与保持一致无关。请参见https://dev59.com/-nI-5IYBdhLWcg3wKE6x。 - kevinc
2谢谢您提供这个过程！顺便说一下，它的正确大小写是 "sp_MSforeachtable"（MS 大写）。谢谢！ - Sielu
3这个回答应该被标记为正确答案，因为它完全回答了问题。不仅如此，@Donal还包括一个通配符版本，对我非常有帮助。 - Matt Jackson
2感谢重新启用位。特别是双重“检查检查”，许多人都会忘记！ - Alex from Jitbit
239
您可以禁用FK和CHECK约束，但只能在SQL 2005及以上版本中实现。请查看ALTER TABLE。
ALTER TABLE foo NOCHECK CONSTRAINT ALL
ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column
主键和唯一约束不能被禁用，但如果我理解正确的话，这应该是可以接受的。
- gbn
5
10但这并非暂时性的。 - Nicolas Barbulesco
@NicolasBarbulesco：这取决于情况。在某种程度上，你可以使用DROP/CREATE重新启用它们。 - gbn
1这个答案只是解决方案的前半部分。我在寻找一种简单的方法来暂时关闭约束，并得出结论，在Oracle上它不存在。 - Nicolas Barbulesco
PK和唯一约束不可禁用的说法是错误的。至少在较新版本的SQL Server中，它可以正常工作。例如，请参考：http://www.techonthenet.com/sql_server/primary_keys.php - Dejan
1@NicolasBarbulesco 关于 Oracle？
禁用某个表的某个约束：
ALTER TABLE some_table DISABLE CONSTRAINT some_table_fk1;//执行一些违反约束的操作启用某个表的某个约束：
ALTER TABLE some_table ENABLE CONSTRAINT some_table_fk1; - Steve Swinsburg
57
如果你想要验证你没有破坏你的关系并且引入了孤立节点，那么当你重新启用检查时，可以进行验证。
ALTER TABLE foo CHECK CONSTRAINT ALL
ALTER TABLE foo CHECK CONSTRAINT FK_something
那么您可以返回并针对任何选中的列执行更新，如下所示：
UPDATE myUpdatedTable SET someCol = someCol, fkCol = fkCol, etc = etc
在那个时候的任何错误都是由于未能满足限制条件造成的。
- Michael K. Campbell
2
13更好的方法是使用 ALTER TABLE FOO WITH CHECK CHECK CONSTRAINT FK_something。 - Cody Konior
2执行"ALTER TABLE foo CHECK CONSTRAINT ALL" 或 "ALTER TABLE foo CHECK CONSTRAINT FK_something" 会启用约束，但不会检查数据，这意味着约束将是不可信的（is_no_trusted = 1，is_disabled = 0）。 - Bogdan Sahlean
18
您实际上可以通过一个SQL命令禁用所有数据库约束，并通过调用另一个单个命令重新启用它们。请参阅：
我目前正在使用SQL Server 2005工作，但我几乎确定这种方法也适用于SQL 2000。
- kristof
3
禁用和启用所有外键
CREATE PROCEDURE pr_Disable_Triggers_v2
@disable BIT = 1
AS
DECLARE @sql VARCHAR(500)
, @tableName VARCHAR(128)
, @tableSchema VARCHAR(128)
-- List of all tables
DECLARE triggerCursor CURSOR FOR
SELECT t.TABLE_NAME AS TableName
, t.TABLE_SCHEMA AS TableSchema
FROM INFORMATION_SCHEMA.TABLES t
ORDER BY t.TABLE_NAME, t.TABLE_SCHEMA
OPEN triggerCursor
FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @sql = 'ALTER TABLE ' + @tableSchema + '.[' + @tableName + '] '
IF @disable = 1
SET @sql = @sql + ' DISABLE TRIGGER ALL'
ELSE
SET @sql = @sql + ' ENABLE TRIGGER ALL'
PRINT 'Executing Statement - ' + @sql
EXECUTE ( @sql )
FETCH NEXT FROM triggerCursor INTO @tableName, @tableSchema
END
CLOSE triggerCursor
DEALLOCATE triggerCursor
首先，声明foreignKeyCursor游标作为SELECT语句，用于收集外键及其表名的列表。接下来，打开游标并执行初始FETCH语句。此FETCH语句将第一行数据读入本地变量@foreignKeyName和@tableName中。在循环游标时，可以检查@@FETCH_STATUS的值是否为0，这表示获取成功。这意味着循环将继续向前移动，以便从行集中获取每个连续的外键。@@FETCH_STATUS对连接上的所有游标都可用。因此，如果您正在循环遍历多个游标，则重要的是在FETCH语句后立即检查@@FETCH_STATUS的值。@@FETCH_STATUS将反映连接上最近的FETCH操作的状态。@@FETCH_STATUS的有效值为：
0 = 获取成功
-1 = 获取失败
-2 = 获取的行丢失
在循环内部，代码根据意图构建ALTER TABLE命令，具体取决于是禁用还是启用外键约束（使用CHECK或NOCHECK关键字）。然后，该语句作为消息打印出来，以便观察其进度，然后执行该语句。最后，在迭代完所有行之后，存储过程关闭并释放游标。
- 0x49D1
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
sql-server和sql-server-2005标签来判断的。我给出的链接是针对 SQL Server 的，但你也可以在 Oracle 中做同样的事情 - 参见这里和这里。你也可以在 PostgreSQL 中实现。 - brichins