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.

If you liked this article and think it is useful use the buttons below.

Leave a Reply

Your email address will not be published. Required fields are marked *

*