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 

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.

Answer 2

Instead of using Disable foreign key checking.

You can use the below code.

DELETE FROM forums; ALTER TABLE forums AUTO_INCREMENT = 1;  DELETE FROM dates; ALTER TABLE dates AUTO_INCREMENT = 1;  DELETE FROM remarks; ALTER TABLE remarks AUTO_INCREMENT = 1; 

It will just delete all the rows and make id increment from 1 onwards.