SQL Tutorials

How to Delete Huge Number of Rows in mysql

How to Delete Huge Number of Rows in mysql

While delete the high volume rows in mysql it will pop on reference other tables dependency even. Delete will fail on foreign key issue.

Error could be the following,

Cannot delete or update a parent row: a foreign key constraint fails…FOREIGN KEY (`xxxxxxx_id`) REFERENCES `xxxxx` (`id`))

Solution:

1. Disable foreign checks

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

2. Run deletion

mysql> delete from dummytable where password=”deleterow”;
Query OK, 1500 rows affected (0.03 sec)

3. Enable foreign key checks

mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

Tags: mysql,technews,mytecharticle,knowledge articles,database