Sometimes a table gets messed up and the best thing to do is to wipe it and recreate it from scratch. Also sometimes, we don’t have a rake migration for it, or it was built up through a series of messy migrations that add bits, remove them, add other stuff, etc.
If we delete all the rows, we DON’T always get the same as an empty table – if, like in most rails tables, we have a primary index (usually id) which is set to autoincrement, if we delete all the records and then add one, the first row won’t be numbered 1, it will be numbered after the last row to be created. So, we’re not really starting from scratch.
A nice quick way to just recreate a blank version of the table from scratch is as follows.
1) Make an sql dump of the entire database: at the command line, type
rake db:structure:dump
2) This will create an sql script file called development_structure.sql in the db folder, which contains instructions for adding all of the tables to the db. Open it in a text viewer and look for the section concerning the table you want. Copy it out.
3) Lose the existing table – at the sql command line type “drop table your_table_name”
4) In a sql browser, or at the command line, paste in the section of sql script from the dump and run it.
Voila – nice empty table, with indexes all starting from scratch.
5) If you have any migrations to populate the table with data, you can copy the code out of the ‘up’ method, run a rails console, paste in the code and run it.
April 6, 2009 at 7:33 pm
Hi,
If we’re talking about MySQL, running “TRUNCATE TABLE [tableName]” will reset the auto increment counter. It’s also significantly faster than “DELETE FROM TABLE [tableName]” (which you correctly stated will not reset the auto increment counter) when there are a lot of rows in the table because it basically deletes the files and creates new ones based on the schema.
April 6, 2009 at 8:41 pm
Sergio – that’s a very handy thing to know, thanks a lot!