批量插入时忽略外键约束

Question

我想批量插入大量生成的数据，其中存在循环依赖（每个表中的一列都是外键约束到另一个表）。为了解决这个问题，我希望关闭外键约束，插入数据，然后再打开约束。

我在网上搜索了许多解决方案，但没有一个可行的。目前我有以下代码：

ALTER TABLE TableName NOCHECK CONSTRAINT ALL

这个命令运行时没有出现任何错误，但是当我试图清空表以准备插入数据时，出现了以下错误：

System.Data:0:in `OnError': The DELETE statement conflicted with the REFERENCE constraint "FK_1_2_ConstraintName". The conflict occurred in database "DatabaseName", table "dbo.SomeOtherTable", column 'PrimaryKey'.\r\nThe statement has been terminated.\r\nChecking identity information: current identity value '0', current column value '0'.\r\nDBCC execution completed. If DBCC printed error messages, contact your system administrator. (System::Data::SqlClient::SqlException)

我的当前理论是，这是由于另一张表上的外键约束依赖于正在更改的表所导致的。

我能想到两个解决方案：

1. 遍历所有依赖于我要插入的表的表，并禁用它们的外键约束。这似乎过于复杂。 2. 禁用数据库中所有表的外键约束。

任何一个解决方案都可以解决问题，但我不确定从哪里开始处理。有什么建议吗？

- kerkeslager

---

如果我在两个表之间有循环依赖关系，我会非常担心。这通常意味着你有一个严重的设计问题。 - HLGEM

@HLGEM 这不是我的设计，我也没有更改的选项。 :| - kerkeslager

Answer 1

这是我在这种工作中使用的方法。

--Disable all Constraints 
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

-- INSERT DATA HERE

--Enable all Constraints 
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 

- EricZ

---

完成后，您的约束条件可能无法信任，这并不好。您可能想阅读Hugo Kornelis的《您能相信自己的约束条件吗？》。 - A-K

@AlexKuznetsov：感谢您的文章。是的，在进行这种工作时，您必须了解自己的数据。对于我来说，我只是为开发服务器执行此操作，以从其他服务器创建测试数据。 - EricZ

Answer 2

禁用约束和触发器

请参阅“禁用所有外键”部分

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
            IF @disable = 1 
                SET @sql = ‘ALTER TABLE ‘ + @tableSchema 
                    + ‘.[‘ + @tableName + ‘] DISABLE TRIGGER ALL’ 
            ELSE 
                SET @sql = ‘ALTER TABLE ‘ + @tableSchema 
                    + ‘.[‘ + @tableName + ‘] ENABLE TRIGGER ALL’ 

            PRINT ‘Executing Statement - ‘ + @sql

            EXECUTE ( @sql )
            FETCH NEXT FROM triggerCursor
            INTO @tableName, @tableSchema
        END

    CLOSE triggerCursor
    DEALLOCATE triggerCursor

- Rodrigo

Answer 3

您还可以在FK约束上启用ON DELETE CASCADE，这将导致每当主表上的PK被删除时，它们的记录也会被删除。这将是一次性更改，不需要每次加载时重新运行。

编辑：

更多信息，请参见Pinal Dave博客（SQLAuthority）中列出所有FK约束的脚本。底部的WHERE子句允许您根据需要将其限制为某个PK和FK表集。

- JNK

---

当然，每次你想要清理/加载数据到一个表中时，你都会清除其他相关的表。这似乎不是一个好的解决方案，伙计。 - Rodrigo

@Rodrigo - 我假设如果他正在重建主表记录，那么他将需要重新处理支持/事实表，因为那些数据可能会改变。 - JNK