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

Question

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 1

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.

Answer 2

SQL server will not let you truncate the table while the constraint exists, even if it's disabled. Drop the constraint and re-create it after truncating the table. Or just drop and re-create the tables, whichever is easier to do in your application.