How do I make a column unique and index it in a Ruby on Rails migration?

I would like to make a column unique in Ruby on Rails migration script. What is the best way to do it? Also is there a way to index a column in a table?

I would like to enforce unique columns in a database as opposed to just using :validate_uniqueness_of.

Solution 1:

The short answer for old versions of Rails (see other answers for Rails 4+):

add_index :table_name, :column_name, unique: true

To index multiple columns together, you pass an array of column names instead of a single column name,

add_index :table_name, [:column_name_a, :column_name_b], unique: true

If you get "index name... is too long", you can add name: "whatever" to the add_index method to make the name shorter.

For fine-grained control, there's a "execute" method that executes straight SQL.

That's it!

If you are doing this as a replacement for regular old model validations, check to see how it works. The error reporting to the user will likely not be as nice without model-level validations. You can always do both.

Solution 2:

rails generate migration add_index_to_table_name column_name:uniq

or

rails generate migration add_column_name_to_table_name column_name:string:uniq:index

generates

class AddIndexToModerators < ActiveRecord::Migration
  def change
    add_column :moderators, :username, :string
    add_index :moderators, :username, unique: true
  end
end

If you're adding an index to an existing column, remove or comment the add_column line, or put in a check

add_column :moderators, :username, :string unless column_exists? :moderators, :username

Solution 3:

If you are creating a new table, you can use the inline shortcut:

  def change
    create_table :posts do |t|
      t.string :title, null: false, index: { unique: true }
      t.timestamps
    end
  end

Solution 4:

Since this hasn't been mentioned yet but answers the question I had when I found this page, you can also specify that an index should be unique when adding it via t.references or t.belongs_to:

create_table :accounts do |t|
  t.references :user, index: { unique: true } # or t.belongs_to

  # other columns...
end

(as of at least Rails 4.2.7)

Solution 5:

I'm using Rails 5 and the above answers work great; here's another way that also worked for me (the table name is :people and the column name is :email_address)

class AddIndexToEmailAddress < ActiveRecord::Migration[5.0]
  def change
    change_table :people do |t|
      t.index :email_address, unique: true
    end
  end
end