How to truncate a foreign key constrained table?

Posted by Prem Tiwari | Updated on January 25, 2020

If you want to truncate a foreign key constrained table in MySQL? 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.

Remove constraints
Perform TRUNCATE
Delete manually the rows that now have references to nowhere
Create constraints

OR second opinion you can use the below MySQL query:

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE table $table_name;

SET FOREIGN_KEY_CHECKS = 1;

If you like this question & answer and want to contribute, then write your question & answer and email to freewebmentor[@]gmail.com. Your question and answer will appear on FreeWebMentor.com and help other developers.

How to truncate a foreign key constrained table?

Related Questions & Answers

Solved Programs