0

I have a databse called store having 100 tables having too much foreign key references with tables, having thousands of records.

Problem: Is it possible something like truncate to delete all the rows of all the tables in a single query.

I tried but I am not able to find the exact way to achieve this when I try to TRUNCATE TABLE it gives me error: Cannot delete or update a parent row: a foreign key constraint fails which is expected too.

Can anyone please help me to achieve this, or suggest me the better way to do this.

Kara
  • 6,115
  • 16
  • 50
  • 57
subodh
  • 6,136
  • 12
  • 51
  • 73
  • If you want to delete all data you can simply recreate the DB. Delete the database and run the create script again. – juergen d Apr 17 '13 at 13:11
  • This may help you? http://stackoverflow.com/q/1912813/1983854 – fedorqui Apr 17 '13 at 13:13
  • Take a look at http://punkave.com/window/2009/05/27/how-to-add-on-delete-cascade-to-an-existing-mysql-table-without-dropping-any-existing-data – anazimok Apr 17 '13 at 13:14

1 Answers1

1

Do this before deleting:

SET FOREIGN_KEY_CHECKS=0;

When finished, reset it to 1:

SET FOREIGN_KEY_CHECKS=1;
Walid
  • 1,262
  • 11
  • 10