Menu
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.
736
You can't truncate a table that has a foreign key constraint, that is the whole reason for having a constraint. You will need to delete and re-create the constraints so make sure you script them out before deleting them.
To achieve high performance, TRUNCATE TABLE bypasses the DML method of deleting data. Thus, it does not cause ON DELETE triggers to fire, it cannot be performed for InnoDB tables with parent-child foreign key relationships, and it cannot be rolled back like a DML operation.
You can disable a foreign key constraint during INSERT and UPDATE transactions in SQL Server by using SQL Server Management Studio or Transact-SQL. Use this option if you know that new data will not violate the existing constraint or if the constraint applies only to the data already in the database.
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.
102
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
