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

sql-server sql-server-2005 tsql truncate foreign-keys
ctrlShiftBryan picture ctrlShiftBryan · Oct 31, 2008 · Viewed 705.5k times · Source

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.

Answer

John Rudy picture John Rudy · Oct 31, 2008

Correct; you cannot truncate a table which has an FK constraint on it.

Typically my process for this is:

  1. Drop the constraints
  2. Trunc the table
  3. 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.

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.…

Read More