外键删除数据

Question

我无法理解关于FKs的几个概念，我有一些非常大的表(数千万行)和很多FKs，我想删除旧的数据，我可以禁用FKs --删除数据--使FKs能够做到这一点，我担心它们会变得不受信任，并且父表将发生冲突。

我读过“关于级联删除”的文章，但我认为这只适用于儿童桌。

示例表：

有一个只有一个FK，我使用了以下查询：

DELETE  

FROM    guide_category

WHERE   

id_guide NOT IN

        (
        SELECT  id_guide
        FROM    guide
        )

这是可行的，但我不适用于许多FKs。

数据的重要性实际上是零(测试DB)，我只想要小DB和所有表被信任以获得更好的性能。

我是不是错过了解决这个问题的简单方法？或者甚至可能看到一个不存在的问题。

Answer 1

你的问题有很多，所以我要把我的回答分成几部分。

我无法理解关于FKs的几个概念，我有一些非常大的表(数千万行)和很多FKs，我想删除旧的数据，我可以禁用FKs --删除数据--使FKs能够做到这一点，我担心它们会变得不受信任，并且父表将发生冲突。

为了从定义了引用完整性的表中删除数据，不需要禁用外键；在从父表中删除引用数据之前，只需要从子表(S)中删除数据。当您在发出DELETEs之前禁用外键，然后只从父表中删除数据时，您肯定会面临产生孤立记录的风险。在这种情况下，尝试重新启用FKey约束也会引发错误。这个MSSQL提示文章完成了一项相当不错的工作，完成了为什么这很重要的过程，所以我不会在这里重述它。

我读过“关于级联删除”的文章，但我认为这只适用于儿童桌。

级联删除将标识当前试图从父表中删除数据的表是否为父表，并在从父表中删除记录之前从基础子表中发出适当的删除。所有这些活动都嵌套在隐式事务中，因此根据父记录中提到的子级数据引用的数量，您可能会发现自己正在运行一个长时间运行的事务，该事务会产生大量阻塞。级联删除(和更新)有很多问题，但采用这种方法是个人偏好。我认为大卫·斯皮莱的S 回答 to 这个dba.se问题做得很好，用层叠的陈述来触及一些风险/回报。

我是不是错过了解决这个问题的简单方法？

是的，如果这是一个测试环境，而且你不关心数据，那么最快的方法就是我会考虑核选择，如下所示：

1. 删除所有引用完整性约束
2. 截断所有表 (这就是我们禁用检查约束的原因)
3. 重新创建所有引用完整性约束
4. 将任何默认数据插入您选择的表中。

下面的脚本将完成这一任务，而无需付出很大的努力(或任何检查)。注意这一点；它是不分青红皂白的，它将从数据库中删除所有用户数据，而无需考虑：

DECLARE @objectName NVARCHAR(1024), @fkeyName NVARCHAR(256), @refName NVARCHAR(1024), @colSelf NVARCHAR(MAX), @colReference NVARCHAR(MAX), @dSQL NVARCHAR(MAX)

-- Walk through the tables dropping RI
DECLARE fkeyDefinitions CURSOR STATIC
FOR
    -- Based on query lifted from RJB's SO Post: https://stackoverflow.com/a/36818102/944748
    SELECT  '[' + OBJECT_SCHEMA_NAME(t.object_id) + '].[' + OBJECT_NAME(t.object_id) + ']' AS objectName
        , fk.name
        , '[' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].[' + OBJECT_NAME(fk.referenced_object_id) + ']' AS refName
        , STUFF((SELECT ', ' + QUOTENAME(c.name)
                 FROM sys.columns AS c 
                    INNER JOIN sys.foreign_key_columns AS fkc 
                        ON fkc.parent_column_id = c.column_id
                       AND fkc.parent_object_id = c.[object_id]
                 WHERE fkc.constraint_object_id = fk.[object_id]
                 ORDER BY fkc.constraint_column_id 
                 FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS col_self
        , STUFF((SELECT ', ' + QUOTENAME(c.name)
                 FROM sys.columns AS c 
                    INNER JOIN sys.foreign_key_columns AS fkc 
                        ON fkc.referenced_column_id = c.column_id
                       AND fkc.referenced_object_id = c.[object_id]
                 WHERE fkc.constraint_object_id = fk.[object_id]
                 ORDER BY fkc.constraint_column_id 
                 FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'') AS col_reference
    FROM    sys.tables t
        LEFT JOIN sys.foreign_keys fk
            ON t.object_id = fk.parent_object_id
    WHERE   t.type = 'U'
    --AND t.name LIKE 'blah%'  -- for only a subset of tables, uncomment/add more conditions to the predicate

-- go through the fkey cursor and drop all RI
OPEN fkeyDefinitions
FETCH NEXT FROM fkeyDefinitions
INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'ALTER TABLE ' + @objectName + ' DROP CONSTRAINT [' + @fkeyName + ']'
    PRINT @dSQL
    EXEC(@dSQL)

    FETCH NEXT FROM fkeyDefinitions
    INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference
END

-- go through new cursor truncating all the tables
DECLARE truncUserTables CURSOR 
FOR
    SELECT  '[' + OBJECT_SCHEMA_NAME(object_id) + '].[' + OBJECT_NAME(object_id) + ']' AS objectName
    FROM    sys.tables
    WHERE   type = 'U'
    --AND t.name LIKE 'blah%'  -- for only a subset of tables, uncomment/add more conditions to the predicate

OPEN truncUserTables
FETCH NEXT FROM truncUserTables
INTO    @objectName

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'TRUNCATE TABLE ' + @objectName
    PRINT @dSQL
    EXEC(@dSQL)

    FETCH NEXT FROM truncUserTables
    INTO    @objectName
END

CLOSE truncUserTables
DEALLOCATE truncUserTables

-- go through the fkey cursor a second time re-creating RI
FETCH FIRST FROM fkeyDefinitions
INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @dSQL = 'ALTER TABLE ' + @objectName + ' ADD CONSTRAINT [' + @fkeyName + '] FOREIGN KEY (' + @colSelf + ') REFERENCES ' + @refName + '(' + @colReference + ')'
    PRINT @dSQL
    EXEC(@dSQL)

    FETCH NEXT FROM fkeyDefinitions
    INTO    @objectName, @fkeyName, @refName, @colSelf, @colReference
END

CLOSE fkeyDefinitions
DEALLOCATE fkeyDefinitions

我甚至设置了一个dbfiddle.uk演示，如果你想看到它的行动。