By default, you cannot TRUNCATE 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.

Solution 1

The fastest way to clean the table is to disable FOREIGN_KEY_CHECKS:

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

Keep in mind, that disabling FOREIGN_KEY_CHECKS might result in inconsistent data with foreign key values that do not exists.

Solution 2

To remove all rows, you can also go the long way:

• Remove all foreign key constraints
• Run TRUNCATE on the table(s)
• Manually delete all rows without reference
• Recreate the foreign key constraints

Photo by Jan Antonin Kolar on Unsplash