SQL Server - Can't Truncate table

Asked By <-> on 12-May-07 01:09 PM
I don't get this.

I have foreign keys set up on a table, which I disabled via:


When I view the results of EXEC sp_helpconstraint <MyTable>

I can see that the status_enable for all FK constraints is "Disabled"

However, when I run TRUNCATE Table <MyTable> I get an error message:


As an extra measure, I disabled all constraints on every table (NOCHECK)

Then I performed a generate sql script on all tables and all foreign key
constraints are NOCHECK.
Yet, I still can't truncate MyTable.

Is there something I'm missing?

MH replied on 12-May-07 01:55 PM
Does 'DELETE FROM MyTable' work?

Tom Cooper replied on 12-May-07 02:00 PM
You must drop all the constraints that reference a table before you are able
to TRUNCATE it.  Disabling them and/or marking them NOCHECK is not enough.

Tom Cooper replied on 12-May-07 02:19 PM
Sorry, I should have said:  You must drop all the FOREIGN KEY constraints
that reference a table before you are able to TRUNCATE it.

<-> replied on 12-May-07 03:12 PM
Ok, then I will do that and recreate them.

But, is this response wrong then? Or am I misunderstanding it?


*********************** Question ********************************
When using truncate table statement on a table with foreign key relationship
will cause an error like,

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'purchase' because it is being referenced by a FOREIGN
KEY constraint.

Does anyone know any T-SQL command to disable/enable the constraint(s)?

******************************** Response ********************************

Newsgroups: microsoft.public.sqlserver.server
From: "Kalen Delaney"
Date: 2000/08/08
Subject: Re: How to truncate table with foreign key referenced
Reply to author | Forward | Print | Individual message | Show original |
Report this message | Find messages by this author
exec sp_helpconstraint purchase

This will tell which table is referencing purchase


This will disable ALL constraints on that table. Alternatively, you could
look up the actual name of the constraint in the other table.

Kalen Delaney replied on 12-May-07 04:33 PM
Hi -

Wow, that was a long time ago!

The answer was incomplete. It looks like I read the post too quickly and was
just answering the specific question that was asked:

I apologize for the confusion. Tom is right, you have to drop the
referencing constraint in order to truncate the table.
Kalen Delaney, SQL Server MVP
<-> replied on 12-May-07 06:27 PM
Aah, no problem.

When I first encountered the error message, I ran to google and tried out
the first link I got (yours). Hehe.