MySQL: Truncate DB Tables with Constraints

To truncate db Tables with some “linked” data might not be that obvious Task. Foreign Keys always try to interrupt your process.

You can TRUNCATE tables one by one in the correct order. i.e. from “children” to “parents”.
But sometimes this is not possible due to child-parent relations inside the table itself.

so here is a quick tip:

Disable the foreign key constraint check

mysql> SET FOREIGN_KEY_CHECKS = 0;

Truncate your tables

mysql> TRUNCATE the_table;
mysql> TRUNCATE the_table_children;
...

Enable the foreign key constraint check

mysql> SET FOREIGN_KEY_CHECKS = 1;

If you’re using phpMyAdmin, or similar: constants override is not preserved between calls.
So, decorate TRUNCATE with those two lines and run 1 query.

or, on “tableView” page

  • check all needed Tables
  • select “Empty”
  • copy contents of the WARNING block to Clipboard
  • click NO
  • goto SQL tab
  • paste back
  • wrap text with those 2 lines
  • press OK