Truncate tables with dependent foreign key constraints

Question

Hi I'm trying to truncate some tables from my db, but they are related with foreign key constraints so every time I try it SQL Server throws an error like this:

Cannot truncate table 'Table' because it is being referenced by a FOREIGN KEY constraint

I don't want to drop the tables or delete them.

A friend told about a Truncate Cascade for this case, but I haven't found any info related, also other user told me to try this; I did try it, but I'm still not getting my tables truncated.

Also I read about SCRIPT for Foreign Keys DROP and RE-CREATE for use with TRUNCATE.

And the script is supposed to affect my db or at least I thought so. But after running it, I was unable to truncate my tables and it threw the same error. I'm using SQL Server 2008 R2, and running my queries with that version of SSMS.

Answer 1

You cannot truncate a table that has foreign key constraints. I would script your truncate statement to drop the constraints, then truncate the table, and then re-create the constraints.

Because TRUNCATE TABLE is a DDL command, it cannot check to see whether the records in the table are being referenced by a record in the child table.

This is why DELETE works and TRUNCATE TABLE doesn't: because the database is able to make sure that it isn't being referenced by another record.

Reference by SO user Michael

One more reference by SO John Rudy

As per your reference to truncate cascade, there is a delete cascade or update that is based on constraints. This is not the same as a truncate. Here is a link with some information regarding that.

In regards to your link, all it is doing is running a script that creates statements you can copy and paste, then run on your database. If you wanted it to become more automated with your truncate; you would need to find a way to store the create statements, run the drop constraint statements, truncate your table, then pull back and run the create statements.

Dynamic SQL is likely your best bet if this had to be automated. I'd look into another solution though if this were the case. Perhaps partition swapping or something else, but we'd need a lot more information to dig into other suggestions for an automated best practice process.

Answer 2

The two links in your post point to scripts that generate the commands necessary to drop and recreate all your current foreign keys (I haven't tested them, just glanced through for intent).

However, in both cases the scripts place those commands in a variable, and print them. Neither script (as shown) automatically executes those commands.

This seems wise to me - I would want to look at the scripts generated, try to understand them, and maybe even compare them to the constraints in a CREATE TABLE script generated by SSMS, to make sure they seem to be complete.

The first link (to a DBA.SE question) would let you DELETE the rows in your table, not actually TRUNCATE, so we'll ignore that one.

In the MSDN link, to actually drop your foreign keys, you'd need to execute this command after populating @strsql with the necessary commands to do the drop:

EXEC sp_executesql @strsql;

If this is your actual problem, then you need to be very careful to test this all very thoroughly, on a test database, that you can easily restore if you need to.

You must capture the necessary commands to recreate your foreign keys before you drop them. If you ran the necessary commands to drop the foreign keys, then tried to put the commands to recreate them in @strsql (which is what the second chunk of code in the MSDN link does) - you won't get anything, because your foreign keys will already have been dropped.

Make sure to capture the commands you need to recreate the foreign keys before you drop them. Also, make sure you put the "drop" commands in a different variable than the commands to recreate the foreign keys, since you need to have both sets of commands available before you drop the foreign keys.

The proper order of operation would be:

• Generate the code to recreate the foreign keys (maybe in to a variable @FK_Create
• Generate the code to drop the foreign keys (into a variable @FK_Drop)
• EXECUTE sp_executesql @FKDrop
• TRUNCATE your tables.
• EXECUTE sp_executesql @FKCreate

Answer 3

This might be a bit old, also note that this might leave orphaned Data, so just make sure it is what you want to do.

@"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