How to truncate a foreign key constrained table in MySQL?

In MySQL, you can't truncate a table that has foreign key constraints, whether or not there are rows currently connected to it.

In this tutorial, I will show you how to work around this.

There are two options for emptying a foreign key-constrained table in MySQL.

1. Option 1

   In this first option, we disable the FOREIGN_KEY_CHECKS first, then we can truncate the table, and finally, we re-enable the FOREIGN_KEY_CHECKS.

   SET FOREIGN_KEY_CHECKS = 0;
   TRUNCATE table;
   SET FOREIGN_KEY_CHECKS = 1;
   

   With this option, you risk having rows that do not follow the foreign key constraints. So make sure to delete all rows that now reference nowhere.

2. Option 2

   In this option, we are not actually truncating the table;we are just deleting the table, and then we reset the AUTO_INCREMENT to 1.

   DELETE FROM table;
   ALTER TABLE table AUTO_INCREMENT = 1;
   

   With this option, you are not risking having rows that do not follow the foreign key constraints, but first you have to truncate or delete all rows that reference the table that you will empty.

   Take note that this option is much slower than the first.

Conclusion

In this tutorial, I have shown you two methods of emptying a table that has foreign key constraints.

The first method is much faster, although you risk having rows that don't follow the foreign key constraints. While the second method is much slower, it doesn't risk having rows that don't follow the key constraints.