Disabling foreign key constraint, still can't truncate table? (SQL Server 2005)

sql-server foreign-keys constraints
larryq picture larryq · Oct 2, 2010 · Viewed 72.8k times · Source

I have a table called PX_Child that has a foreign key on PX_Parent. I'd like to temporarily disable this FK constraint so that I can truncate PX_Parent. I'm not sure how this goes however.

I've tried these commands

ALTER TABLE PX_Child NOCHECK CONSTRAINT ALL

ALTER TABLE PX_Parent NOCHECK CONSTRAINT ALL

(truncate commands)

ALTER TABLE PX_Child CHECK CONSTRAINT ALL

ALTER TABLE PX_Parent CHECK CONSTRAINT ALL

But the truncate still tells me it can't truncate PX_Parent because of a foreign key constraint. I've looked all around the net and can't seem to find what I'm doing wrong, sorry for the basic nature of this question.

Answer

bobs picture bobs · Oct 2, 2010

You can't truncate the table if there is any foreign key referencing it, including disabled constraints. You either need to drop the foreign key constraints or use the DELETE command.

Related questions

How can foreign key constraints be temporarily disabled using T-SQL?

Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints?

Read More
SQL DROP TABLE foreign key constraint

If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first? GO IF OBJECT_ID('dbo.[Course]','U') …

Read More
How to disable Constraints for all the tables and enable it?

I have 60 tables. I want to drop 10 tables where these 10 tables are Constraints(PK,FK) to other 20 tables. While dropping these 10 tables, I need to truncate or delete data from the other 20 tables. Finally I want to disable all 60 table …

Read More