How to quickly wipe and recreate a db table that doesn’t have a migration

January 17, 2008

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.

About these ads

2 Responses to “How to quickly wipe and recreate a db table that doesn’t have a migration”

  1. Sergio Salvatore Says:

    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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: