
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