How to add `unique` constraint to already existing index by migration

How can I add unique: true constraint to already existing index in Rails database?

I tried to migrate by

  def change
    add_index :editabilities, [:user_id, :list_id], unique: true
  end

but migration fails with a error like this.

> Index name 'index_editabilities_on_user_id_and_list_id' on table > 'editabilities' already exists

I'm using rails4 and postgresql.

Remove the old index and add it again with the new constraint:

def change
  remove_index :editabilities, [:user_id, :list_id]
  add_index :editabilities, [:user_id, :list_id], unique: true
end

If it's the existing index then you may need to do more than that:

1. Delete duplicated data.
2. Add uniqueness index.

This is the safest way to add uniqueness constraints into existing indexes with large data in production.

class AddStoreIdUniquenessIndexToOrders < ActiveRecord::Migration[5.2]
  disable_ddl_transaction!

  def up
    delete_duplicated_records

    rename_index :orders, :index_orders_on_store_id, :non_uniqueness_index_orders_on_store_id
    add_index :orders, :store_id, unique: true, algorithm: :concurrently
    remove_index :orders, name: :non_uniqueness_index_orders_on_store_id, column: :store_id
  end

  def down
    remove_index :orders, name: :index_orders_on_store_id, column: :store_id
    add_index :orders, :store_id, name: :index_orders_on_store_id
  end

  private

  def delete_duplicated_records
    dup_store_ids = Order.group(:store_id).having('COUNT(*) > 1').pluck(:store_id)
    dup_store_ids.each_slice(400) do |store_ids|
      not_remove_order_ids = Order.where(store_id: store_ids).group(:store_id).having('COUNT(*) > 1').pluck('MIN(id)')
      Order.where(store_id: store_ids).where.not(id: not_remove_order_ids).destroy_all
    end
  end
end

Note:

> As you see, I rename the index index_orders_on_store_id before > deleting it. It's for performance purposes. This means if the > migration fails while adding the new index, and we have to re-run the > migration, we’re now executing the query without an index on the > retail_orders column. > > If you’re doing a big ol’ query against a few hundred thousand rows of > data, with the index, it’ll take a few seconds. Without the index, it > could take… many minutes.