# Truncating MySQL tables with foreign key constraints

*2016-07-04*

> Truncate MySQL tables with foreign key constraints by dropping and recreating constraints or temporarily disabling FOREIGN_KEY_CHECKS.

With foreign key checks enabled, MySQL does not allow [`TRUNCATE TABLE`](https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/truncate-table.html) on an InnoDB table referenced by a foreign key in another table. `TRUNCATE` behaves differently from [`DELETE`](https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/delete.html). Here are two ways to work around this restriction.

Option 1: Drop and recreate the foreign key constraints.

1. Drop the foreign key constraints that reference the table.
2. Truncate the table.
3. Delete rows in related tables that now reference missing rows.
4. Recreate the foreign key constraints.

Option 2: Temporarily disable foreign key checks.

This can be useful when clearing related tables during development. Run all of the following statements in the same database session:

```sql
SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;
```

Disabling foreign key checks can compromise data integrity by leaving rows that reference missing records. Re-enabling [`FOREIGN_KEY_CHECKS`](https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/create-table-foreign-keys.html) does not validate existing rows or repair broken references.