MySQL error: Cannot truncate a table referenced in a foreign key constraint

By default, you cannot TRUNCATE (empty) a table that has foreign key constraints applied on it. This is to keep the data consistent over multiple tables that are linked by constraints. Nevertheless, it might be necessary to truncate all data from a table.

Here are a few options you can consider to resolve this issue:

Solution 1 – Disable Foreign Key Checks

The fastest way to clean the table is to disable FOREIGN_KEY_CHECKS. Temporarily disabling foreign key checks allows you to truncate the table without encountering the error. However, this should be done with caution as it can lead to data integrity issues if not handled properly.

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE TABLE `table1`;
TRUNCATE TABLE `table2`;
SET FOREIGN_KEY_CHECKS = 1;

Solution 2 – Delete Rows Instead of Truncating

Instead of truncating the table, you can delete rows from it. Deleting rows one by one will trigger the foreign key constraint checks, allowing MySQL to maintain referential integrity. You can use the DELETE statement with a WHERE clause to remove specific rows.

DELETE FROM your_table WHERE condition;

Solution 3 – Drop and Recreate Foreign Key Constraints

If it’s acceptable for your application, you can drop the foreign key constraints referencing the table you want to truncate, truncate the table, and then recreate the foreign key constraints.

ALTER TABLE referencing_table DROP FOREIGN KEY constraint_name;
TRUNCATE TABLE your_table;
ALTER TABLE referencing_table ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES your_table(column_name);

Photo by Jan Antonin Kolar on Unsplash