How to force truncate all tables(which are all innodb) in a database in MySQL?

Question

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?

Solution 1

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

Solution 2

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.

Comments

[machineaddict]

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.