What's the correct syntax for remove_index in a Rails 3.1.0 migration?

Question

I'm in the process of adding Devise to an existing Rails app, with a Users table already defined. The devise generator pushed out the following migration:

class AddDeviseToUsers < ActiveRecord::Migration
  def self.up
    change_table(:users) do |t|

     ## Database authenticatable
     t.string :email,              :null => false, :default => ""
     t.string :encrypted_password, :null => false, :default => ""

     ## Recoverable
     t.string   :reset_password_token
     t.datetime :reset_password_sent_at

     ## Rememberable
     t.datetime :remember_created_at

     ## Trackable
     t.integer  :sign_in_count, :default => 0

     blah blah blah....

   end

   add_index :users, :email,                :unique => true
   add_index :users, :reset_password_token, :unique => true
 end

The downward migration isn't generated, and I'm having a heck of a time removing those indexes. I'm seeing different suggested notation in the documentation, and different suggestions online, but none of them seem to be working for me. For example...

def self.down
  change_table(:users) do |t|
    t.remove  :email
    t.remove  :encrypted_password

    t.remove  :reset_password_token

    blah blah blah...
  end

  remove_index :users, :email
  remove_index :users, :reset_password_token
end

results in...

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

Index name 'index_users_on_email' on table 'users' does not exist

which is odd, because if I check the database, sure enough, 'index_users_on_email' is right there...

I've tried other variations, including

remove_index :users, :column => :email

remove_index :users, 'email'

or:

change_table(:users) do |t|
  t.remove_index :email
end

...but no dice. I'm running Rails 3.1.0, Ruby 1.9.2, rake 0.9.2.2, with Postgres.

The command that's letting me down is:

bundle exec rake db:rollback STEP=1

after successfully apply the migration up. Any advice?

Extramarital

Also remember to first remove the index and then remove the column, your codes does the other way around and will fail even if you use a correct syntax. – Coloration

Answer 1

Depending on the database type, you don't need to worry about removing the indexes in the self.down method since the index will automatically be removed from the database when you drop the column.

You can also use this syntax in your self.down method:

def self.down
   remove_column :users, :email
   remove_column :users, :encrypted_password
   remove_column :users, :reset_password_token
end

Coat

Based on the answer to this question, indexes will not be dropped when you drop the column: #7204976 – Loose

^ Depends on the database type – Coat

To further @Coat 's response, be careful with this approach. If you have a composite index and only remove one of the columns on MySQL, it'll still leave the index. It'll just be referencing the remaining column(s). The response below is a much safer approach to explicitly remove the index. – Chrystel

Answer 2

For the record, the way to remove an index by name is

remove_index(:table_name, :name => 'index_name')

so in your case

remove_index(:users, :name => 'index_users_on_email')

Courcy

For copy-paster with newer syntax: remove_index :users, name: 'index_users_on_email' – Taratarabar

Answer 3

You can also remove the index specifying the columns, which from my point of view is less error prone than writing the name

remove_index :actions, :column => [:user_id, :action_name]

Undesirable

For the question this would be remove_index :users, column: :email. – Elah

Answer 4

I'd like to expand on @iWasRobbed's answer. If you have index on just single column then worrying about remove_index doesn't make sense since (just an assumtion!) the DB should be smart enough to cleanup the resources used by that index. But in case you have multiple columns index removing the column will reduce index to still existing columns, which is totally sensible thing to do, but kind of shows where you might want to use remove_index explicitely.

Just for illustration - migration below has that flaw that after being applied up and down it will leave the unique index on email (meaning the down part is not doing its job properly)

class AddIndexes < ActiveRecord::Migration
  def up
    add_column :users, :action_name, :string
    add_index  :users, [:email, :action_name], unique: true
  end

  def down
    remove_column :users, :action_name
  end
end

Changing the down block to

  def down
    remove_index :users, [:email, :action_name]
    remove_column :users, :action_name
  end

will fix that flaw and allow the migration to correctly return DB to the previous state with rake db:rollback

Bibliotaph

Answer 5

To alter a table and/or its indeces use #change_table inside #change action of a migration. Then you be able to create reversable index removal as follows:

def change
   change_table :users do |t|
      t.index :email, :unique => true
      t.index :reset_password_token, :unique => true
   end
end

When you have to drop a table with its index of course with reversable action you can use #drop_table method for SchemaStatements with the #index method of Table class for ConnectionAdapter:

def change
   drop_table :users do |t|
      t.index :email, :unique => true
      t.index :reset_password_token, :unique => true
   end
end

In case you have need exactly the #up/down pair in a migration. Use just a #change_table method along with #remove_index method of Table class for ConnectionAdapter:

def up
   change_table :users do |t|
      t.index :email, :unique => true
      t.index :reset_password_token, :unique => true
   end
end

def down
   change_table :users do |t|
      t.remove_index :email, :unique => true
      t.remove_index :reset_password_token, :unique => true
   end
end

All of the methods are available in Rails version of 2.1.0 or of earlier ones.

Ferromagnetism

Answer 6

Here is my full run of this(in Rails 5):

I have team_id as an index in table vendors. I no longer need this relation. To get rid of it. Did the following:

1) create the migration.

  $ rails generate migration RemoveTeam_idFromVendor team_id:integer

2) Running the migration, give me this error. And that is because vendor table has rows whose foreign key references the primary key value of the team table

== 20170727202815 RemoveTeamIdFromVendor: migrating ===========================
-- remove_column(:vendors, :team_id, :integer)
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

SQLite3::ConstraintException: FOREIGN KEY constraint failed: DROP TABLE "vendors"

3) To solve this and get the migration running, I did the following(Note: i am in dev):

$ rake db:drop


Dropped database 'db/development.sqlite3'
Dropped database 'db/test.sqlite3'


$ rake db:create
Created database 'db/development.sqlite3'
Created database 'db/test.sqlite3'

$ rake db:migrate
~
~
~

== 20170727202815 RemoveTeamIdFromVendor: migrating ===========================
-- remove_column(:vendors, :team_id, :integer)
   -> 0.0185s
== 20170727202815 RemoveTeamIdFromVendor: migrated (0.0185s) ==================

Monochromatism