Truncate tables with foreign key between themPosted: July 2, 2008
How to truncate tables with foreign key linked with each other?
You can drop the Foreign Key before you do theTRUNCATE and add it back to the table after you are done. By the way, the TRUNCATE action is logged but it is just not the same way as the DELETE action. You can roll back a truncate in a transaction with the page logging.
Here is a simple sample for the question:
–Drop the foreign key ALTER TABLE dbo.CategoryTable1Sub
DROP CONSTRAINT FK_CategoryID
truncate table dbo.CategoryTable1Sub
–Add Foreign key back
TABLE dbo.CategoryTable1Sub ADD CONSTRAINT
CatID )REFERENCES dbo.CategoryTable1
( Category_ID )