How do I truncate tables properly?

mysql ruby datamapper
Tallboy picture Tallboy · Dec 27, 2011 · Viewed 57.2k times · Source

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 werent deleted from within phpmyadmin. i can only imagine its because of foreign keys. Although I really dont know because my other table which foreing keys was successfully deleted. Not to mention, id rather just 'zero' it out anyway so the keys dont get to extraordinarly large numbers (like key #500,000).

I then tried running it with the code below, but it doesnt 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 thats 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

Devart picture Devart · Dec 27, 2011

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.

Related questions

Error installing mysql2: Failed to build gem native extension

I am having some problems when trying to install mysql2 gem for Rails. When I try to install it by running bundle install or gem install mysql2 it gives me the following error: Error installing mysql2: ERROR: Failed to build …

Read More
gem install: Failed to build gem native extension (can't find header files)

I am using Fedora 14 and I have MySQL and MySQL server 5.1.42 installed and running. Now I tried to do this as root user: gem install mysql But I get this error: Building native extensions. This could take a while... ERROR: …

Read More
Change a column type from Date to DateTime during ROR migration

I need to change my column type from date to datetime for an app I am making. I don't care about the data as its still being developed. How can I do this?

Read More