Database tables are typically related or linked with each other using foreign key constraints. When we try to delete one or more rows from table constrained by foreign key in MySQL, you may get an error ‘ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint’. This is because before deleting any rows in a foreign key constrained table, MySQL checks to see if the deletion will break any foreign key relations. If it looks possible, then MySQL will throw this error. In this article, we will learn how to truncate foreign key constrained table in MySQL.
How to Truncate Foreign Key Constrained Table
There are a couple of ways to truncate foreign key constrained table in MySQL. When we try to delete one or more rows from a foreign key constrained table, MySQL checks to see if the said deletion will lead to any foreign key integrity problems. If that is possible, then it throws the error message mentioned above. So to delete rows from foreign key constrained table, we can disable foreign key check with the following command.
SET FOREIGN_KEY_CHECKS = 0;
The above command will delete all kinds of foreign key checks across all your databases. Let us say you have two tables table1 and table2 linked using foreign key constraints. You can truncate them with the following commands.
TRUNCATE table1; TRUNCATE table2;
Next, you can optionally delete any rows that reference to nowhere.
Then re-enable foreign key checks with the following command.
SET FOREIGN_KEY_CHECKS = 1;
Please remember to re-enable foreign key checks otherwise it may lead to foreign key integrity issues later on since MySQL will not check foreign key integrity when new rows are added or existing rows are modified in your tables. By the way, if you close your current session and start a new one, even then these checks will be automatically re-enabled.
Alternatively, you can also use delete statement on the table followed by resetting of auto increment column. This way you will not lose the foreign key constraint but only the data will be deleted.
DELETE FROM table1; ALTER TABLE table1 AUTO_INCREMENT = 1;
In this article, we have learnt how to truncate foreign key constrained table in MySQL.
Also read:
How to Export MySQL Scheme Without Data
How to Insert Element After Another Element in JS
How to Sum Values of JS Object
How to Access POST Form Fields in ExpressJS
How to Detect Scroll Direction in JavaScript