Sometime ago I wanted to truncate some tables in a MySQL database on my development environment. But I did got the following error:
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
Due to that a table is referenced in a foreign key constraint it isn’t possible to truncate the table.
It’s possible to disable the foreign key constraint check by adding SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0; TRUNCATE table1; TRUNCATE table2; SET FOREIGN_KEY_CHECKS=1;
The example works as follow:
- Disable the foreign key constraint check
- Truncate table1
- Truncate table2
- Enable foreign key constraint check again
It’s not neccesarry to use SET FOREIGN_KEY_CHECKS=1; to enable the foreign key constraint check again as the setting is only valid during the connection. As soon as you disconnect, the next session will have it set back to 1.