暂时关闭约束 (MS SQL)

Turn off constraints temporarily (MS SQL)

提问by Maciej

I'm looking for a way to temporarily turn off all DB's constraints (eg table relationships).

我正在寻找一种方法来暂时关闭所有数据库的约束（例如表关系）。

I need to copy (using INSERTs) one DB's tables to another DB. I know I can achieve that by executing commands in proper order (to not break relationships).

我需要将一个数据库的表复制（使用插入）到另一个数据库。我知道我可以通过以正确的顺序执行命令（不破坏关系）来实现这一点。

But it would be easier if I could turn off checking constraints temporarily and turn it back on after the operation's finish.

但是如果我可以暂时关闭检查约束并在操作完成后重新打开它会更容易。

Is this possible?

这可能吗？

回答by Donal

-- 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:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Re-enable constraints for all tables:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'
---------------------------------------------------------

回答by gbn

You can disable FK and CHECK constraints only in SQL 2005+. See ALTER TABLE

您只能在 SQL 2005+ 中禁用 FK 和 CHECK 约束。请参阅更改表

ALTER TABLE foo NOCHECK CONSTRAINT ALL

or

或者

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

Primary keys and unique constraints can not be disabled, but this should be OK if I've understood you correctly.

主键和唯一约束不能被禁用，但如果我理解正确的话，这应该没问题。

回答by Michael K. Campbell

And, if you want to verify that you HAVEN'T broken your relationships and introduced orphans, once you have re-armed your checks, i.e.

而且，如果你想确认你没有破坏你的关系并引入孤儿，一旦你重新武装你的支票，即

ALTER TABLE foo CHECK CONSTRAINT ALL

or

或者

ALTER TABLE foo CHECK CONSTRAINT FK_something

then you can run back in and do an update against any checked columns like so:

然后您可以重新运行并对任何选中的列进行更新，如下所示：

UPDATE myUpdatedTable SET someCol = someCol, fkCol = fkCol, etc = etc

And any errors at that point will be due to failure to meet constraints.

并且此时的任何错误都将是由于未能满足约束。

回答by kristof

You can actually disable all database constraints in a single SQL command and the re-enable them calling another single command. See:

您实际上可以在单个 SQL 命令中禁用所有数据库约束，然后通过调用另一个单个命令重新启用它们。看：

• Can foreign key constraints be temporarily disabled using TSQL?

• 可以使用 TSQL 暂时禁用外键约束吗？

I am currently working with SQL Server 2005 but I am almost sure that this approach worked with SQL 2000 as well

我目前正在使用 SQL Server 2005，但我几乎可以肯定这种方法也适用于 SQL 2000

回答by 0x49D1

Disabling and Enabling All Foreign Keys

禁用和启用所有外键

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

First, the foreignKeyCursor cursor is declared as the SELECT statement that gathers the list of foreign keys and their table names. Next, the cursor is opened and the initial FETCH statement is executed. This FETCH statement will read the first row's data into the local variables @foreignKeyName and @tableName. When looping through a cursor, you can check the @@FETCH_STATUS for a value of 0, which indicates that the fetch was successful. This means the loop will continue to move forward so it can get each successive foreign key from the rowset. @@FETCH_STATUS is available to all cursors on the connection. So if you are looping through multiple cursors, it is important to check the value of @@FETCH_STATUS in the statement immediately following the FETCH statement. @@FETCH_STATUS will reflect the status for the most recent FETCH operation on the connection. Valid values for @@FETCH_STATUS are:

0 = FETCH was successful
-1 = FETCH was unsuccessful
-2 = the row that was fetched is missing

Inside the loop, the code builds the ALTER TABLE command differently depending on whether the intention is to disable or enable the foreign key constraint (using the CHECK or NOCHECK keyword). The statement is then printed as a message so its progress can be observed and then the statement is executed. Finally, when all rows have been iterated through, the stored procedure closes and deallocates the cursor.

首先，foreignKeyCursor 游标被声明为 SELECT 语句，该语句收集外键列表及其表名。接下来，打开游标并执行初始 FETCH 语句。这个 FETCH 语句会将第一行的数据读入局部变量 @foreignKeyName 和 @tableName。当循环游标时，您可以检查@@FETCH_STATUS 的值为 0，这表示获取成功。这意味着循环将继续向前移动，因此它可以从行集中获取每个连续的外键。@@FETCH_STATUS 可用于连接上的所有游标。因此，如果您要循环访问多个游标，那么检查紧跟在 FETCH 语句之后的语句中 @@FETCH_STATUS 的值很重要。@@FETCH_STATUS 将反映连接上最近 FETCH 操作的状态。@@FETCH_STATUS 的有效值为：

0 = FETCH 成功
-1 = FETCH 不成功
-2 = 获取的行丢失

在循环内部，代码根据意图是禁用还是启用外键约束（使用 CHECK 或 NOCHECK 关键字）以不同方式构建 ALTER TABLE 命令。然后将语句打印为消息，以便可以观察其进度，然后执行该语句。最后，当所有行都遍历完后，存储过程关闭并释放游标。

see Disabling Constraints and Triggers from MSDN Magazine

请参阅MSDN 杂志中的禁用约束和触发器