Truncate tables with foreign key between them


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

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

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s