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

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.

The accepted answer is not exactly safe for huge tables. What might happen is:

1. Index is removed successfully
2. Adding a new index takes forever and/or fails

In this situation you end up having no index at all. And if you have a fair amount of traffic and loads of queries that used to use the index you might run into big trouble.

For large tables I suggest:

1. De-dupe records having the same value on the indexed column.
2. Add a unique index with a different name than the non-unique one (you'll temporarily have two indexes on the same column, with different names):

class AddUniqueUuidIndexToItems < ActiveRecord::Migration[7.1]
def up
add_index :items, :uuid, name: index_name, unique: true
end


def down
remove_index :items, column: :uuid, name: index_name
end


private


def index_name
'unique_index_items_on_uuid'
end
end

3. Remove the old, non-unique index (cleanup step):

class RemoveNonUniqueIndexFromItemsUuid < ActiveRecord::Migration[7.1]
def up
remove_index :items, column: :uuid, name: index_name
end


def down
add_index :items, :uuid, name: index_name
end


private


# old index name, check db/schema.rb to find the actual name
def index_name
'index_items_on_uuid'
end
end