Disable constraints and truncate table

Question

Disable constraints and truncate table

Prabhakar-234228

Old Hand

Points: 399
More actions
June 1, 2005 at 10:20 am

#167357

Hi,

I am trying to disable constraints, truncate table and re-enable the constraints, but I am still getting the following error
'Cannot truncate table 'owner' because it is being referenced by a FOREIGN KEY constraint.' any idea?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

You'll have to delete the data in the child tables before deleting the parent table's data (even truncate can't go around this).

What's the problem you're trying to solve by this task?

Prabhakar-234228 Old Hand Points: 399 More actions · Answer 2

Delete is taking for ever, I am trying to use truncate instead to increase the performance and truncating the parent table after truncate the child table but still getting the error.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

If you just want to wipe out both tables, you can disable or delete the foreign key, then you'll be able to truncate both tables.

Here's out to disable / enable the fk :

--disable

ALTER TABLE TableName NOCHECK CONSTRAINT FkName

-- Reenable

ALTER TABLE TableName CHECK CONSTRAINT FkName

Prabhakar-234228 Old Hand Points: 399 More actions · Answer 4

That’s exactly what I am doing, but still getting error

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Script the foreign key, drop it, truncate, recreate foreign key... I was under the impression that it was possible to do it without dropping the relation.. but apparently it is not.

Can anyone else elaborate on this??

Prabhakar-234228 Old Hand Points: 399 More actions · Answer 6

Prabhakar-234228

Old Hand

Points: 399

June 1, 2005 at 11:39 am

#562296

Thanks Remi

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 7

Prabhakar,

Here is a script I am using in a DTS package:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblBuildingAlias_tblBuildingName]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)

ALTER TABLE [dbo].[tblBuildingAlias] DROP CONSTRAINT FK_tblBuildingAlias_tblBuildingName

just modify it with the appropriate names and you should be set.

Good luck,

Darrell

mike gercevich SSCrazy Points: 2961 More actions · Answer 8

While you are dropping the FK Constraints, you my want to check if you also have indexes on the tables that you are about to import into. After the import is successful, script the re-creation of you indexes as well. This saves a ton of time and helps keep your log file smaller if you are importing a large amount of data into tables withn complex indexes.

-Mike Gercevich

Prabhakar-234228 Old Hand Points: 399 More actions · Answer 9

Prabhakar-234228

Old Hand

Points: 399

June 2, 2005 at 8:39 am

#562550

Thanks guys!

Greg Charles SSC-Forever Points: 45403 More actions · Answer 10

Remi,

TRUNCATE won't work if a table is referenced by a foreign key constraint, even if the constraint is disabled. BOL is kind of vague about this, but I've experienced it. I always use the 'drop fk, truncate, load, create fk' method in DTS.

Greg

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Ya I reread up on this, and in the disable section of the constraint, they referrence only updates and inserts as affected operations... so I guess truncate/delete is out of the question, which makes sens when you think about it.