How to truncate a foreign key constrained table?

Yes you can:

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table1;
TRUNCATE table2;

SET FOREIGN_KEY_CHECKS = 1;

With these statements, you risk letting in rows into your tables that do not adhere to the FOREIGN KEY constraints.


You cannot TRUNCATE a table that has FK constraints applied on it (TRUNCATE is not the same as DELETE).

To work around this, use either of these solutions. Both present risks of damaging the data integrity.

Option 1:

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

Option 2: suggested by user447951 in their answer

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

I would simply do it with :

DELETE FROM mytest.instance;
ALTER TABLE mytest.instance AUTO_INCREMENT = 1;

Easy if you are using phpMyAdmin.

Just uncheck Enable foreign key checks option under SQL tab and run TRUNCATE <TABLE_NAME>

enter image description here