Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

by

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?

I know that I can either

  • Use a DELETE without a where clause and then RESEED the identity (or)
  • Remove the FK, truncate the table, and recreate the FK.

I thought that as long as I truncated the child table before the parent, I’d be okay without doing either of the options above, but I’m getting this error:

Cannot truncate table ‘TableName’ because it is being referenced by a FOREIGN KEY constraint.

29 s
29

DELETE FROM TABLENAME
DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME', RESEED, 0)

Note that this isn’t probably what you’d want if you have millions+ of records, as it’s very slow.

Leave a Comment