CODEWITHSUNDEEP
mysqlinnodb
developarvin
developarvin

Reputation: 5069

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

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?

Upvotes: 16

Views: 16862

Answers (2)

Devart
Devart

Reputation: 122040

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

Upvotes: 60

vbence
vbence

Reputation: 20343

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.

Upvotes: 8

Related Questions