MySQL error 1701: Can't truncate a table with foreign keys
TRUNCATE TABLE is refused because another table has a foreign key pointing at this one, even if no child rows exist.
- Error number
1701- Name
ER_TRUNCATE_ILLEGAL_FK- SQLSTATE
42000- Category
- Keys and constraints
What it means
TRUNCATE TABLE drops and recreates the table rather than deleting rows one by one, so InnoDB can't check foreign keys row by row. It refuses to truncate any table that other tables reference, regardless of whether child rows exist.
Common causes
- Truncating a parent table such as
customersin a test reset or data reload. - Truncating tables in an order where parents come before children.
How to fix it
-
Use
DELETEinstead, which checks (and cascades) foreign keys row by row.DELETE FROM customers;
-
For a full reset of related tables, disable foreign key checks for the session while truncating them all, then turn checks back on.
SET foreign_key_checks = 0; TRUNCATE TABLE order_lines; TRUNCATE TABLE orders; TRUNCATE TABLE customers; SET foreign_key_checks = 1;
Related errors
1451Foreign key fails: cannot delete parent row1217Cannot delete or update a parent row0A000PostgreSQL: feature not supported
More keys and constraints errors
1062Duplicate entry for key1061Duplicate key name1452Foreign key fails: cannot add child row1216Cannot add or update a child row1215Cannot add foreign key constraint3780Foreign key columns are incompatible1822Missing index for foreign key constraint1824Failed to open the referenced table
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.