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.

8 Answers

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.

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 

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 

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)

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 

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 -

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.

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