// MySQL error reference

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
Error message
Cannot truncate a table referenced in a foreign key constraint (%s)

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 customers in a test reset or data reload.
  • Truncating tables in an order where parents come before children.

How to fix it

  1. Use DELETE instead, which checks (and cascades) foreign keys row by row.

    DELETE FROM customers;
  2. 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

More keys and constraints errors

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.

// fewer errors, faster fixes

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.