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

Posthaste

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.

Ljubljana

any idea how to do this in ruby? I tried running each command separately within the app and it didnt delete anything. I then tried running them one by one in phpmyadmin in order and it still says #1701 - Cannot truncate a table referenced in a foreign key constraint – Posthaste

scratch that! it worked... it was only there for a millisecond before it re-added my data and i didnt catch it actually truncating. thanks! – Posthaste

I truncated the child tables, but even then it fails while deleting parent table. Anyways, I used the hack of disabling of Foreign key checks for now. – Coh

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.

Ouzo