截断具有依赖外键约束的表

您不能截断具有外键约束的表。我会编写您的 truncate 语句以删除约束，然后截断表，然后重新创建约束。

因为 TRUNCATE TABLE 是一个 DDL 命令，它无法检查表中的记录是否被子表中的记录引用。

这就是 DELETE 有效而 TRUNCATE TABLE 无效的原因：因为数据库能够确保它没有被另一条记录引用。

SO用户迈克尔的参考

SO John Rudy 的另一篇参考资料

根据您对截断级联的引用，有一个基于约束的删除级联或更新。这与截断不同。这是一个链接，其中包含一些相关信息。

关于您的链接，它所做的只是运行一个脚本，该脚本创建您可以复制和粘贴的语句，然后在您的数据库上运行。如果您希望它通过截断变得更加自动化；您需要找到一种方法来存储创建语句，运行删除约束语句，截断您的表，然后拉回并运行创建语句。

如果必须自动化，动态 SQL 可能是您最好的选择。如果是这种情况，我会考虑另一种解决方案。也许是分区交换或其他东西，但我们需要更多信息来挖掘自动化最佳实践过程的其他建议。

您帖子中的两个链接指向生成删除和重新创建所有当前外键所需命令的脚本（我没有测试它们，只是浏览了一下意图）。

但是，在这两种情况下，脚本都将这些命令放在一个变量中，然后打印出来。两个脚本（如图所示）都不会自动执行这些命令。

这对我来说似乎很明智 - 我想查看生成的脚本，尝试理解它们，甚至可能将它们与CREATE TABLESSMS 生成的脚本中的约束进行比较，以确保它们看起来是完整的。

第一个链接（指向 DBA.SE 问题）将让您DELETE查看表中的行，而不是实际TRUNCATE的，因此我们将忽略那个。

在MSDN 链接中，要实际删除外键，您需要在填充@strsql必要的命令以执行删除后执行此命令：

EXEC sp_executesql @strsql;

如果这是您的实际问题，那么您需要非常小心地在测试数据库上非常彻底地测试这一切，如果需要，您可以轻松恢复。

在删除外键之前，您必须捕获重新创建外键所需的命令。如果您运行了必要的命令来删除外键，然后尝试输入命令以重新创建它们（这是 MSDN 链接中的第二段代码所做的） - 您将一无所获，因为您的外键将已经被丢弃了。@strsql

确保在删除外键之前捕获重新创建外键所需的命令。此外，请确保将“删除”命令放在与重新创建外键的命令不同的变量中，因为在删除外键之前需要使两组命令都可用。

正确的操作顺序是：

• 生成代码以重新创建外键（可能在变量中@FK_Create
• 生成代码以删除外键（放入变量中@FK_Drop）
• EXECUTE sp_executesql @FKDrop
• TRUNCATE你的桌子。
• EXECUTE sp_executesql @FKCreate

这可能有点老了，还要注意这可能会留下孤立的数据，所以只要确保它是你想要做的。

@"SET NOCOUNT ON

                            --DECLARE TOP LEVEL VARIABLE

                            DECLARE @FKTable TABLE(
                                      fk_id int identity(1,1) primary key,
                                      fk_row_id int,
                                      fk_name nvarchar(100),
                                      fk_parent_table_name nvarchar(100),
                                      fk_parent_table_coulumn_name nvarchar(100),
                                      fk_reference_table nvarchar(100),
                                      fk_reference_table_column_name nvarchar(100)
                            )

                            DECLARE @FKTableRef TABLE(
                                      fk_id int identity(1,1) primary key,
                                      fk_row_id int,
                                      fk_name nvarchar(100),
                                      fk_parent_table_name nvarchar(100),
                                      fk_parent_table_coulumn_name nvarchar(100),
                                      fk_reference_table nvarchar(100),
                                      fk_reference_table_column_name nvarchar(100)
                            )

                                    --DROP TEMP TABLE IF EXISTS--------------

                                    IF Object_Id('TempDB..#FKRefConstructAdd') IS NOT NULL
                                      BEGIN
                                       DROP TABLE #FKRefConstructAdd
                                    END


                            CREATE TABLE #FKRefConstructAdd
                            (
                               fk_Add_id int primary key identity(1,1),
                               AddScript nvarchar(2000) -- number can be increased
                            )

                            DECLARE @MinLoopCount int
                            DECLARE @MaxLoopCount int
                            -------------------------------
                            DECLARE @Error nvarchar(600)
                            --declare @TableName varchar(1000) = '[mrc].[M_LibWKB]' --Test Data
                            ----------------------------
                            DECLARE @InnerMinLoopCount int
                            DECLARE @InnerMaxLoopCount int
                            ------------------------------
                            DECLARE @AddMinLoopCount int
                            DECLARE @AddMaxLoopCount int
                            ---------------------------
                            DECLARE @TransTry varchar(100) ='Try_Transaction' --better name 

                            --INSERT TABLE FOREIGN KEYS INTO TABLE VARIABLE

                            INSERT INTO @FKTable  
                                select
                                 ROW_NUMBER() OVER (PARTITION BY fk.name ORDER BY fk.name) as fk_row_id,
                                fk.name as fk_name,
                                '['+OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].['+ object_name(fk.parent_object_id)+']' as fk_parent_table_name,
                                '['+c1.name+']' as fk_parent_table_coulumn_name,
                                '[' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].[' + object_name(fk.referenced_object_id) + ']' as fk_reference_table,
                                '['+c2.name+']' as fk_reference_table_column_name
                            from 
                                sys.foreign_keys fk
                            inner join
                                sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
                            inner join
                                sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
                            inner join
                                sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id
                                where '['+OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].['+ object_name(fk.parent_object_id)+']'  = @TableName

                            -------------------INSERT DEPENDECIES-------------------------------------------------

                            INSERT INTO @FKTableRef  
                                select
                                 ROW_NUMBER() OVER (PARTITION BY fk.name ORDER BY fk.name) as fk_row_id,
                                fk.name as fk_name,
                                '['+OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].['+ object_name(fk.parent_object_id)+']' as fk_parent_table_name,
                                '['+c1.name+']' as fk_parent_table_coulumn_name,
                                '[' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].[' + object_name(fk.referenced_object_id) + ']' as fk_reference_table,
                                '['+c2.name+']' as fk_reference_table_column_name
                            from 
                                sys.foreign_keys fk
                            inner join
                                sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
                            inner join
                                sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
                            inner join
                                sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id
                                where '['+OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '].['+ object_name(fk.referenced_object_id)+']'  = @TableName

                                SET XACT_ABORT ON -- If transaction fails, rollback operation

                            -------------START TRANSACTION--------------------------

                            BEGIN TRAN @TransTry
                             BEGIN
                            ---LOOP THROUGH THE RESULT SET
                            SET @MinLoopCount = 1
                            SELECT @MaxLoopCount = (SELECT COUNT(*) FROM @FKTable)


                                    IF @MaxLoopCount > 0

                                      BEGIN
                                        WHILE @MaxLoopCount >= @MinLoopCount
                                          BEGIN
                                      ---------------------------DECLARE SUPPORT VARABLES---------------------------
                                           DECLARE @fkName nvarchar(100)
                                           DECLARE @fkParentTableName nvarchar(100)
                                           DECLARE @fkParentTableCoulumnName nvarchar(100)
                                           DECLARE @fkReferenceTable nvarchar(100)
                                           DECLARE @fkReferenceTableColumnName nvarchar(100)
                                           DECLARE @sql nvarchar(200)

                                           -----------------DECLARE SUPPORT INNER VARIABLES---------------------------
                                                       DECLARE @fkName_Inner nvarchar(100)
                                                       DECLARE @fkParentTableName_Inner nvarchar(100)
                                                       DECLARE @fkParentTableCoulumnName_Inner nvarchar(100)
                                                       DECLARE @fkReferenceTable_Inner nvarchar(100)
                                                       DECLARE @fkReferenceTableColumnName_Inner nvarchar(100)
                                                       DECLARE @sql_Inner nvarchar(200)

                                                       ------Set support variables------------

                                            SET @fkName = (SELECT TOP(1) fk_name FROM  @FKTable WHERE fk_id = @MinLoopCount)
                                            SET @fkParentTableName = (SELECT TOP(1) fk_parent_table_name FROM  @FKTable WHERE fk_id = @MinLoopCount)
                                            SET @fkParentTableCoulumnName = (SELECT TOP(1) fk_parent_table_coulumn_name FROM  @FKTable WHERE fk_id = @MinLoopCount)
                                            SET @fkReferenceTable = (SELECT TOP(1) fk_reference_table FROM  @FKTable WHERE fk_row_id = @MinLoopCount)
                                            SET @fkReferenceTableColumnName = (SELECT TOP(1) fk_reference_table_column_name FROM  @FKTable WHERE fk_id = @MinLoopCount)

                                            ----------------------------------------Drop Constraint-------------------------------------

                                                 SET @sql = (select 'ALTER TABLE ' + @fkParentTableName + ' DROP CONSTRAINT ' + @fkName)
                                                 PRINT @sql
                                                 exec sp_executesql @sql

                                                 ------------------------------Drop Reference Constraints--------------------------------
                                                 SET @InnerMinLoopCount = 1 --SET THE INNER LOOP
                                                 SET @InnerMaxLoopCount = (SELECT COUNT(*) FROM @FKTableRef WHERE fk_reference_table = @fkParentTableName)
                                                 IF @InnerMaxLoopCount > 0
                                                   BEGIN 
                                                       WHILE @InnerMaxLoopCount >= @InnerMinLoopCount
                                                         BEGIN
                                                       ----SET SUPPORTING inner VARIABLES -------------------------

                                                             SET @fkName_Inner = (SELECT TOP(1) fk_name FROM  @FKTableRef WHERE fk_id = @InnerMinLoopCount)
                                                             SET @fkParentTableName_Inner = (SELECT TOP(1) fk_parent_table_name FROM  @FKTableRef WHERE fk_id = @InnerMinLoopCount)
                                                             SET @fkParentTableCoulumnName_Inner = (SELECT TOP(1) fk_parent_table_coulumn_name FROM  @FKTableRef WHERE fk_id = @InnerMinLoopCount)
                                                             SET @fkReferenceTable_Inner = (SELECT TOP(1) fk_reference_table FROM  @FKTableRef WHERE fk_row_id = @MinLoopCount)
                                                             SET @fkReferenceTableColumnName_Inner = (SELECT TOP(1) fk_reference_table_column_name FROM  @FKTableRef WHERE fk_id = @InnerMinLoopCount)


                                                       SET @sql_Inner = (select 'ALTER TABLE ' + @fkParentTableName_Inner + ' DROP CONSTRAINT ' + @fkName_Inner)
                                                       print @sql_Inner
                                                       exec sp_executesql @sql_Inner

                                                       --------------------Add refernced table re-add scripts to array table ----------------------------
                                                       INSERT INTO #FKRefConstructAdd(AddScript)
                                                             VALUES('ALTER TABLE ' + @fkParentTableName_Inner + ' WITH NOCHECK ADD  CONSTRAINT ' + @fkName_Inner + ' FOREIGN KEY(' + @fkParentTableCoulumnName_Inner+') REFERENCES ' + @fkReferenceTable_Inner + '(' +@fkReferenceTableColumnName_Inner +')')

                                                        INSERT INTO #FKRefConstructAdd(AddScript)
                                                             VALUES('ALTER TABLE ' + @fkParentTableName_Inner + ' CHECK CONSTRAINT ' + @fkName_Inner)

                                       ------------------------------------------------------------------

                                                       SET @InnerMinLoopCount = @InnerMinLoopCount + 1
                                                       PRINT @sql
                                                   END
                                                  END

                                            -----------------------------------------Truncate Table-----------------------------------

                                                 SET @sql = (select 'TRUNCATE TABLE ' + @fkParentTableName)
                                                 PRINT @sql
                                                 exec sp_executesql @sql

                                            ------------------------------------ADD Constraint back------------------------------

                                                SET @sql = (select 'ALTER TABLE ' + @fkParentTableName + ' WITH NOCHECK ADD  CONSTRAINT ' + @fkName + ' FOREIGN KEY(' + @fkParentTableCoulumnName+') REFERENCES ' + @fkReferenceTable + '(' +@fkReferenceTableColumnName +')')
                                                PRINT @sql
                                                exec sp_executesql @sql

                                            ------------------------CHECK CONSTRAINT---------------------

                                               SET @sql = (select 'ALTER TABLE ' + @fkParentTableName + ' CHECK CONSTRAINT ' + @fkName)
                                               print @sql
                                               exec sp_executesql @sql

                                               ---------------------------EXECUTE INNER LOOP ADD FK Scripts ---------------------

                                               SET @AddMinLoopCount = 1
                                               SET @AddMaxLoopCount = (select COUNT(*) FROM #FKRefConstructAdd)

                                               IF @AddMaxLoopCount > 0
                                                 BEGIN
                                                 WHILE @AddMaxLoopCount >= @AddMinLoopCount
                                                   BEGIN
                                                      SET @sql_Inner = (select top(1) AddScript from #FKRefConstructAdd WHERE fk_Add_id = @AddMinLoopCount)
                                                      print @sql_Inner
                                                      exec sp_executesql @sql_Inner

                                                      SET @AddMinLoopCount = @AddMinLoopCount + 1

                                                    END
                                                 END

                                                 TRUNCATE TABLE #FKRefConstructAdd ---clear the table after use

                                           SET @MinLoopCount = @MinLoopCount + 1
                                         END
                                    END

                                    COMMIT TRAN @TransTry
                              END

                                -- SET @Error =  ERROR_MESSAGE() --set the error variable
                                --PRINT @@error
                                --PRINT ERROR_MESSAGE()
                                --PRINT ERROR_SEVERITY()
                                --PRINT ERROR_STATE()
                                --PRINT ERROR_LINE()

                              SET @Error = ERROR_MESSAGE();
                              SELECT @Error as ERROR--Return the error value