patternsqlMinor
Truncate tables with dependent foreign key constraints
Viewed 0 times
tablesconstraintswithforeigntruncatedependentkey
Problem
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.
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#190073, answer score: 8
Revisions (0)
No revisions yet.