Rails: Adding an index after adding column

Question

Suppose I created a table table in a Rails app. Some time later, I add a column running:

rails generate migration AddUser_idColumnToTable user_id:string.

Then I realize I need to add user_id as an index. I know about the add_index method, but where should this method be called? Am I supposed to run a migration (if yes, which one ?), then adding by hand this method?

score 281 · Accepted Answer · answered Apr 08 '13 at 14:31

281

You can run another migration, just for the index:

class AddIndexToTable < ActiveRecord::Migration
  def change
    add_index :table, :user_id
  end
end

answered Apr 08 '13 at 14:31

Jaap Haagmans

6,232
1
25
30

4

So I simply run in my console : rails generate migration AddIndexToTable ? – user1611830 Apr 08 '13 at 14:32
3

Yes you can do that, but you will have to edit that migration afterwards to reflect the above code. – Jaap Haagmans Apr 08 '13 at 14:33
1

Is :table supposed to be plural? – tomb Sep 02 '18 at 22:37
1

@tomb I used the example from the original question. `:table` is the actual table name, so in the case of a `users` table, you'd substitute `:users` for `:table`. – Jaap Haagmans Sep 13 '18 at 09:24
1

@tomb yes by rails convention, table names are always plural, so `add_index :bars, :foo` where the table name is `bars` – BenKoshy Jul 18 '22 at 03:22

Vadym Tyemirov · Answer 2 · 2019-02-17T16:18:37.717

75

If you need to create a user_id then it would be a reasonable assumption that you are referencing a user table. In which case the migration shall be:

rails generate migration AddUserRefToProducts user:references

This command will generate the following migration:

class AddUserRefToProducts < ActiveRecord::Migration
  def change
    add_reference :user, :product, index: true
  end
end

After running rake db:migrate both a user_id column and an index will be added to the products table.

In case you just need to add an index to an existing column, e.g. name of a user table, the following technique may be helpful:

rails generate migration AddIndexToUsers name:string:index will generate the following migration:

class AddIndexToUsers < ActiveRecord::Migration
  def change
    add_column :users, :name, :string
    add_index :users, :name
  end
end

Delete add_column line and run the migration.

In the case described you could have issued rails generate migration AddIndexIdToTable index_id:integer:index command and then delete add_column line from the generated migration. But I'd rather recommended to undo the initial migration and add reference instead:

rails generate migration RemoveUserIdFromProducts user_id:integer
rails generate migration AddUserRefToProducts user:references

edited Feb 17 '19 at 16:18

answered Jul 08 '14 at 23:01

Vadym Tyemirov

8,288
4
42
38

Thanks Vadym for the complete answer. One last question: why would you recommend undoing the initial migration? Is there any performance issue related to adding index later on? – Flavio Wuensche May 24 '15 at 16:44
2

To @fwuensche: there is no performance penalty for adding index later on. The domain logic will be less clear, though. E.g. in case you decide to break/abstract/etc the association later on, you'd need to be dealing with two separate migrations, which really should have been a single one ... – Vadym Tyemirov Jun 01 '15 at 19:01
9

WARNING: Note that index: true only works in a create_table migration. The migration will run, but no index will be created. See https://makandracards.com/makandra/32353-psa-index-true-in-rails-migrations-does-not-work-as-you-d-expect – rmcsharry Aug 20 '16 at 22:25
If "Then I realize I need to add user_id as an index" happened after the `user_id` column was already in production, then removing it will delete real data, so that's a bad idea. If it is still in development, why not just revert and then edit the original migration? Or if you feel the need to leave the original migration code untouched, you could add comments mentioning the relationship between the two migrations. – iconoclast Aug 17 '20 at 15:47
For the sake of completeness, could you add how the migration of `rails generate migration RemoveUserIdFromProducts user_id:integer`will look like? – Ndrslmpk Mar 29 '22 at 07:31

score 15 · Answer 3 · answered Apr 08 '13 at 14:32

15

Add in the generated migration after creating the column the following (example)

add_index :photographers, :email, :unique => true

answered Apr 08 '13 at 14:32

rdeandrade

151
5

you mean something like this : def self.up add_column ... end add_index ... ? – user1611830 Apr 08 '13 at 14:34

Mauro · Answer 4 · 2022-01-12T13:08:50.177

For references you can call

rails generate migration AddUserIdColumnToTable user:references

If in the future you need to add a general index you can launch this

rails g migration AddOrdinationNumberToTable ordination_number:integer:index

Generated code:

class AddOrdinationNumberToTable < ActiveRecord::Migration
  def change
   add_column :tables, :ordination_number, :integer
   add_index :tables, :ordination_number, unique: true
  end
end

score 3 · Answer 5 · answered Feb 06 '19 at 11:28

You can use this, just think Job is the name of the model to which you are adding index cader_id:

class AddCaderIdToJob < ActiveRecord::Migration[5.2]
  def change
    change_table :jobs do |t|
      t.integer :cader_id
      t.index :cader_id
    end
  end
end

score 3 · Answer 6 · answered Sep 30 '20 at 17:37

For those who are using postgresql db and facing error

StandardError: An error has occurred, this and all later migrations canceled:

=== Dangerous operation detected #strong_migrations ===

Adding an index non-concurrently blocks writes

please refer this article

example:

class AddAncestryToWasteCodes < ActiveRecord::Migration[6.0]
  disable_ddl_transaction!

  def change
    add_column :waste_codes, :ancestry, :string
    add_index :waste_codes, :ancestry, algorithm: :concurrently
  end
end

Rails: Adding an index after adding column

6 Answers6

Linked