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

Question

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.

Answer 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.

Answer 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

Answer 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

Answer 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)

Answer 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

Answer 6

You might want to add name for the unique key as many times the default unique_key name by rails can be too long for which the DB can throw the error.

To add name for your index just use the name: option. The migration query might look something like this -

add_index :table_name, [:column_name_a, :column_name_b, ... :column_name_n], unique: true, name: 'my_custom_index_name'

More info - http://apidock.com/rails/ActiveRecord/ConnectionAdapters/SchemaStatements/add_index

Answer 7

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.

Answer 8

If you have missed to add unique to DB column, just add this validation in model to check if the field is unique:

class Person < ActiveRecord::Base
  validates_uniqueness_of :user_name
end

refer here Above is for testing purpose only, please add index by changing DB column as suggested by @Nate

please refer this with index for more information