Cannot Truncate A Table Referenced In A Foreign Key Constraint

When developing with Magento, it is common that you may require to empty all products and categories from the catalog when working with import/export features in order to start-a-fresh.

Magento’s database can seem complex for many and removing products or categories from the database is never easy due to how many tables in the database relate to products.

When deleting all products and categories in Magento, you may run into an error message such as:-

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

To get around this error, you’ll need to disable the foreign keys check prior to running your SQL query and then enable them again afterwards. For example:-

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
SET FOREIGN_KEY_CHECKS=1;

This will then allow you to truncate your tables as necessary in MYSQL.