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

If you have ever had this error, is because you want to truncate a table that has foreign key in another table. Let me put it this way.

Having this  structure, where "articles" can have multiple users , and you want to delete the table users using the function TRUNCATE, Mysql will throw this error: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint

There is a way to avoid this error, but you have to understand,  if you want to delete the  users tables and reset the auto increment field , you have to do the same with the articles table.

This is a basic example, but what would happen if you want to clean an entire database and reset the auto increment? That would be very complicated without the following code.


I know this code could be useful for you:



SYNTAX:

   

     SET foreign_key_checks = 0;

       TRUNCATE  `users`;


       TRUNCATE  `articles`;


     SET foreign_key_checks = 1;

 

Doing that, you will temporarily disabling  referential constraint, and at the end you will enable the referential constraint.