Saturday, July 2, 2011

How can I truncate the table which have foreign key

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
GO
truncate table dbo.CategoryTable1
truncate table dbo.CategoryTable1Sub GO
--Add Foreign key back
ALTER TABLE dbo.CategoryTable1Sub ADD CONSTRAINT
FK_CategoryID FOREIGN KEY
( CatID )
REFERENCES dbo.CategoryTable1 ( Category_ID )
GO

No comments:

Post a Comment