CODEWITHSUNDEEP
rubyruby-on-rails-3indexing
Vignesh
Vignesh

Reputation: 558

How can I change an index defined on a table?

I have an index defined like 

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

Is there any way to change this index to drop the UNIQUE constraint, something like 'change_index'?
Or is it the only way to drop the index and add it again without the UNIQUE constraint?

Upvotes: 4

Views: 2645

Answers (3)

Epigene
Epigene

Reputation: 3908

This should be done in two steps - first creating the index you need in one migration file and dropping the existing one in another.

First file:

class AddNewIndex < ActiveRecord::Migration
  disable_ddl_transaction!

  def change
    add_index(
      :users,
      :email,
      unique: false,
      name: "index_non_unique_emails_on_users",
      algorithm: :concurrently
    )
  end
end

Second file:

class DropOldListCIndexFromPushLogs < ActiveRecord::Migration
  def change
    remove_index(
      :users,
      name: <existing unique index name> # have a look in db/schema.rb
    ) if index_exists?(:users, name: "index_non_unique_emails_on_users")
  end
end

Please have a look at Zero Downtime Migrations' readme if you're new to safe migrations.

Upvotes: 0

Hugo
Hugo

Reputation: 12914

There is no "change_index" in migrations so you can do:

remove_index :users, :email

add_index :users, :email, :unique => false

Upvotes: 4

Holger Just
Holger Just

Reputation: 55768

Different index types are typically implemented in very different ways in the database you are using. A primary index is very different from any secondary index. And unique indexes are typically different from search indexes to facilitate their primary use case: to quickly determine if a value is already present in a column vs. allowing efficient searches.

As such, (depending on your DBMS) you can't change an existing index. You safest bet in any case is to drop the index and create a new one. This can be done during live operations. There is no need to shutdown neither the database nor your rails app.

Upvotes: 3

Related Questions