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:

ALTER TABLE <MyTable> NOCHECK CONSTRAINT ALL

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:

Constraint."

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?

MH
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
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.

Tom
<-> 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?

http://groups.google.com/group/microsoft.public.sqlserver.server/browse_thread/thread/acf1876ea03d2f0e/6432666afd587f44?lnk=st&q=how+to+truncate+table+referenced+by+foreign+key+constraint+sql+server&rnum=2#6432666afd587f44

*********************** 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


ALTER TABLE <referencing table name> NOCHECK CONSTRAINT ALL


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.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
<-> 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.

Thanks.