Tag Archives: mysql

Truncating MySQL tables with foreign key constrains

One cannot TRUNCATE, MySQL table that has FK constraints applied on it. TRUNCATE is not the same as DELETE. There are 2 possible workarounds for the problem. The good (which does not risk damage to data integrity) and the bad.

Option 1:

  1. Remove constraints.
  2. Perform TRUNCATE.
  3. Delete manually the rows that now have references to nowhere.
  4. Create constraints.

Option 2: Aka the BAD variant. Very handy during development.

SET FOREIGN_KEY_CHECKS = 0;
 
TRUNCATE table1;
TRUNCATE table2;
 
SET FOREIGN_KEY_CHECKS = 1;

Disabling foreign key checks may lead to a damage to the data integrity, for ex. leaving your tables with rows that do not adhere to the FOREIGN KEY constraints.