mysql - How do I truncate tables properly?

Question

I'm using datamapper with ruby to store data to certain tables.

Several of the tables have very large amounts of information and I want to clear them out when the user 'rebuilds database' (it basically deletes everything and re-calculates data).

I originally tried Forum.all.destroy and did it for all the different tables, but I noticed some of them just weren't deleted from within phpmyadmin. I can only imagine it's because of foreign keys. Although I really don't know because my other table which foreing keys was successfully deleted. Not to mention, I'd rather just 'zero' it out anyway so the keys don't get to extraordinarily large numbers (like key #500,000).

I then tried running it with the code below, but it doesn't clear the tables out because of 'foreign key constraints'. I want to force it to work because I know for a fact I'm clearing out all the tables that rely on each other (I'm only not clearing out 2 tables, a settings table and a random storage table, neither of which use foreign keys).

So far I have...

adapter = DataMapper.repository(:default).adapter
adapter.execute('TRUNCATE TABLE `forums`, `dates`, `remarks`');

That would be fine except the MySQL syntax is wrong apparently. So that's the first thing.

I did it 1 by 1 in phpmyadmin and when I did it that way it says

Cannot truncate a table referenced in a foreign key constraint

question from:https://stackoverflow.com/questions/8641703/how-do-i-truncate-tables-properly

Answer 1

Plan A:

SET FOREIGN_KEY_CHECKS = 0; -- Disable foreign key checking.
TRUNCATE TABLE forums;
TRUNCATE TABLE dates;
TRUNCATE TABLE remarks;
SET FOREIGN_KEY_CHECKS = 1; -- Enable foreign key checking.

Plan B:

You should truncate child tables firstly, then parent tables.

Disabling foreign key checks risks entering rows into your tables that don't adhere to the constraints which can cause undefined behavior.