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:

"Can't Truncate <MyTable> because it is being referenced by a FOREIGN KEY
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?

Does 'DELETE FROM MyTable' work?

MH

<-> wrote in message news:4628
[..]

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

<-> wrote in message news:4628
[..]

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

"Tom Cooper" <tomcooper> wrote in message
news:3496
[..]

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/micro...32666afd587f44

*********************** 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"
<kalen_dela>
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.


********************************





"Tom Cooper" <tomcooper> wrote in message
news:3996
[..]

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:
"Does anyone know any T-SQL command to disable/enable the constraint(s)?"

I apologize for the confusion. Tom is right, you have to drop the
referencing constraint in order to truncate the table.

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.

"Kalen Delaney" <replies> wrote in message
news:a492
[..]