Truncating MySQL tables with foreign key constraints

With foreign key checks enabled, MySQL does not allow TRUNCATE TABLE on an InnoDB table referenced by a foreign key in another table. TRUNCATE behaves differently from DELETE. 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:

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 does not validate existing rows or repair broken references.