15

I think I get foreign key constraint error when I try to truncate innodb tables. I was not having problems with this when using MyISAM.

Is there an easy way to force truncate all tables? Or should I just make a script to drop the database, create new one and then create the tables from scratch?

developarvin
  • 4,940
  • 12
  • 54
  • 100

2 Answers2

58

About the FK constraints, you could disable them with next statements -

SET FOREIGN_KEY_CHECKS = 0;
...DML statements
SET FOREIGN_KEY_CHECKS = 1; -- enable checking
Devart
  • 119,203
  • 23
  • 166
  • 186
  • 4
    Don't do this because you will get foreign key contraint errors. Use instead `DELETE FROM table`. It will NOT cascade delete every record found in other tables as it's foreign key. – machineaddict Apr 12 '13 at 10:23
8

If you have foreign key problems during your operation you can:

ALTER TABLE tablename DISABLE KEYS

then do your business, and afterwards re-enable keys with:

ALTER TABLE tablename ENABLE KEYS

This techinique is used in MySQL dumps.

vbence
  • 20,084
  • 9
  • 69
  • 118