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.

• mathewb: Truncate cascade is not an option in SQL Server.
• mathewb: Possible duplicate of Is there a way to truncate table that has foreign a key?
• RDFozz: Not a duplicate - the OP has specified they do not want to delete the rows in the table, but want to perform a truncate explicitly.
• mathewb: @RDFozz The first line of the answer states, "delete and re-create the keys" in response to the question, "Is there a way to truncate table that has foreign keys?" The answers provided here state to delete and re-create the keys. I don't see what this question is adding. Furthermore, if you drop the foreign key constraints, then you are no longer truncating a table with dependent foreign key constraints, you are truncating a table without dependent foreign key constraints, which the answer to the duplicate makes clear - you can't do what the OP is asking for.
• RDFozz: @mathewb - If you use the code from the OP's second link, you can capture the commands to drop and recreate the foreign keys; drop those keys; run the TRUNCATE TABLE commands; and recreate the keys. OP explicitly says they don't want to DROP or DELETE from their tables; OP does not say they object to removing and recreating the constraints. The answer in that so-called duplicate does not delete and recreate the foreign key constraints. Note that question also talks about reseeding - the "keys" being deleted are the rows in the table, not the constraints.

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.

• mathewb: @E.Rawrdríguez.Ophanim This answer should work without any problems for your scenario. If it is not working, please update your question with the script that you are attempting to use to perform the operations.

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

• E.Rawrdríguez.Ophanim: ty for your time and contribution