Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
foreign-keys, sql-server, sql-server-2005, t-sql, truncate
Solution
Correct; you cannot truncate a table which has an FK constraint on it.
Typically my process for this is:
- Drop the constraints
- Trunc the table
- Recreate the constraints.
(All in a transaction, of course.)
Of course, this only applies if the child has already been truncated. Otherwise I go a different route, dependent entirely on what my data looks like. (Too many variables to get into here.)
The original poster determined WHY this is the case; see this answer for more details.
Problem
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.