Pages

Thursday, June 19, 2008

Truncate a table that contain a foreign key

The way I "reset" a table is using the truncate statement as shown underneath, this does not only remove all data in the table but also resets the identity:
TRUNCATE TABLE my_table 
But if you do this on a table that contain one or more foreign keys, you get this error when trying execute the query:
Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'my_table' because it is being referenced by a FOREIGN KEY constraint.


To solve this problem with the foreign key error, you can use the delete statement first to delete the content and afterwards reset the identity, and that is done this way:
DELETE from my_table

DBCC CHECKIDENT ('my_table', RESEED, 0)
The my_table is where you have to put your own table name in, the 0 value is the identity start point

No comments:

Post a Comment