0

I was truncating tables like this

TRUNCATE TABLE `enterprise_url_rewrite`; -- error
TRUNCATE TABLE `enterprise_catalog_category_rewrite`; -- works fine

in that order or reversed when I got this error. 

Cannot truncate a table referenced in a foreign key constraint 
(`magento`.`enterprise_catalog_category_rewrite`, CONSTRAINT 
`FK_415B32DA3DF924D5C803CF24EB3AC1D9` FOREIGN KEY (`url_rewrite_id`) REFERENCES 
`magento`.`enterprise_url_rewrite` (`url_rewrite_id`)

I thought foreign key constraints were enforced to prevent deletion when there are actual values being referenced. I recollect (I could be wrong) being able to TRUNCATE this table previously without disabling key checks. Note that DELETE FROM enterprise_url_rewrite works.

More info on the constraint definition on enterprise_catalog_category_rewrite.

KEY `FK_744D72D1D79D148B7C2542E53B0370B5` (`url_rewrite_id`),
CONSTRAINT `FK_744D72D1D79D148B7C2542E53B0370B5` FOREIGN KEY (`url_rewrite_id`)     
REFERENCES `enterprise_url_rewrite` (`url_rewrite_id`) ON DELETE CASCADE ON UPDATE NO ACTION
laketuna
  • 3,832
  • 14
  • 59
  • 104
  • 1
    What is the definition of the constraint? Have you disabled the check constraint? – Kermit Mar 21 '14 at 14:36
  • It has a `ON DELETE CASCADE` option (post updated), which removes the referenced `enterprise_catalog_category_rewrite` record on deletion of the record on `enterprise_url_rewrite`, correct? – laketuna Mar 21 '14 at 14:46
  • Well, a `TRUNCATE` isn't a `DELETE.` – Kermit Mar 21 '14 at 14:48
  • Yeah `DELETE` works on `enterprise_url_rewrite`. Does that constraint definition prevent `TRAUNCATE` on `enterprise_url_rewrite` regardless of existence of records in related tables? – laketuna Mar 21 '14 at 14:56
  • 1
    See [How do I truncate tables properly?](http://stackoverflow.com/a/5452798/679449) – Kermit Mar 21 '14 at 14:58
  • Hmm, I must have had set `FOREIGN_KEY_CHECKS = 0` previously then.. Thanks Kermit! – laketuna Mar 21 '14 at 15:00

0 Answers0