Cannot truncate table

Question

When I try to truncate the table, i got the error Message as follows,

**Cannot truncate table 'Locations' because it is being referenced by a FOREIGN KEY constraint.**

I don't know the query for that..Anyone help me.

remove all the relations of the table `location` and then try... — Harjeet Jadeja, Apr 10 '13 at 09:32

Andy Nichols · Answer 1 · 2013-04-11T08:14:44.967

Why?

The reason why is that TRUNCATE removes all the records without individual checking.

So a DELETE FROM Locations will delete each record (and that's what gets logged in the transaction logs), checking that no constraints have been violated.

TRUNCATE TABLE Locations doesn't do this. A single truncate action is performed (and logged) and this is why is is quicker.

See this article for more information.

Workaround

The easiest way is to delete rather than truncate. But if this has a long execution time, and it is safe to remove constraints, you might want to write a script to drop constraints, truncate table, create constraints.

score 1 · Answer 2 · answered Apr 10 '13 at 19:45

One thing to be aware of - when you truncate the identity column is reset. When you delete it does not. So if you do a delete and the last value was 242, the next value will be 243.

The only way to do the truncate is to delete the relations. Personally - I would just delete instead of deleting all the relations and then putting them back.

Cannot truncate table

2 Answers2