2 ways to truncate foreign key constrained table

In MySQL we create tables and build relationship among them. In the process if we need to truncate foreign key constrained table then we can’t do it simply and MySQL will throw an error even we have already removed table having foreign key constraint:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint…

I’m presenting here 2 ways to truncate foreign key constrained table in MySQL with a simple category, product tables example.

In the table above we have tables with foreign key constraints and if we wish to truncate category table then we can’t do it by running simple query TRUNCATE category as we cannot truncate a table that has foreign key constraints applied on it.

2 ways to truncate foreign key constrained table 

First one longer but does not risk damage to data integrity:

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

Second one is short but can damage your data integrity. It’s a bad practice and use it if you can risk damage to data integrity.

So here we are temporarily disabling foreign key checks to forcibly truncate foreign key constrained table (category in our case).

Like this Post? Share