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.

ndp · Accepted Answer · 2020-06-08T21:00:28.150

787

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.

edited Jun 08 '20 at 21:00

answered Sep 19 '09 at 20:39

ndp

21,546
5
36
52

38

+1 for suggesting continuing to use the validates_uniqueness_of. The error handling is much cleaner using this method for the cost of a single indexed query I would suggest he does both – Steve Weet Sep 19 '09 at 22:08
2

I tried that it doesn't seem to work! I could insert two record with the column_name that I defined as unique! I'm using Rails 2.3.4 and MySql any ideas? – Tam Sep 20 '09 at 04:57
I used you second suggestion by using execute: execute "ALTER TABLE users ADD UNIQUE(email)" and it works! not sure why the first one didn't would be interested in knowing – Tam Sep 20 '09 at 05:19
I found that the composite index alone didn't present any nice errors, therefore validated uniqueness as well. Cheers! – Michael De Silva Jul 15 '11 at 12:21
@Tam - I found this comment about the same problem on apidock. Looks like you just need to add one more parameter to the method `:name => index_name` http://apidock.com/rails/ActiveRecord/ConnectionAdapters/SchemaStatements/add_index#note_info-953 – plainjimbo Mar 29 '12 at 21:32
If you want uniqueness, I believe you still have to add `:unique => true` as the last argument to `add_index` even if the second argument is an array. Details can be found at the same doc linked above: http://apidock.com/rails/ActiveRecord/ConnectionAdapters/SchemaStatements/add_index – Jason Swett May 03 '13 at 20:26
1

If you get an `indexed columns are not unique` error when trying to create a unique index, it might be because the data in the table already contains duplicates. Try removing the duplicate data and running the migration again. – Hartley Brody Oct 04 '13 at 23:18
What does `add_index :table_name, [:column_name_a, :column_name_b], :unique => true` do? Is the uniqueness enforced using two columns now? – Jwan622 Dec 19 '16 at 00:17
As a side note, don't forget to add validators to your models. eg: `class TableName < ApplicationRecord validates :column_name_a, presence: true, uniqueness: { scope: :column_name_b, message: 'column_name_a and column_name_b must be unique' } end` – Blaskovicz Jan 20 '17 at 21:35
Alert: Using an array of column names like `[:column_name_a, :column_name_b]` will create a composite index rather than indexing each column. – Kunal Kapadia Mar 06 '18 at 10:39
Following suggested, adding to`validates :column_name, uniqueness: true` to the model definition will result in a 422 response code rather than 500. Also returns a specific message. – NicoE Nov 03 '20 at 15:44
If you need to change a column anyway (like, decrease `limit`), you can go with: `change_column :table, :column, :type, limit: 191, unique: true`. – x-yuri Dec 16 '21 at 16:01

score 158 · Answer 2 · edited Jun 10 '15 at 16:04

158

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

edited Jun 10 '15 at 16:04

go2null

2,080
1
21
17

answered Mar 13 '14 at 16:17

d.danailov

9,594
4
51
36

7

I upvoted this because I wanted the command line form. But it's silly that it adds the column even when I specify `add_index...` and not `add_column...`. – Tyler Collier Oct 02 '14 at 17:47
1

Yeap, maybe in next version. – d.danailov Oct 02 '14 at 20:24

Pioz · Answer 3 · 2018-09-07T15:12:54.407

101

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

edited Sep 07 '18 at 15:12

answered Apr 13 '18 at 09:22

Pioz

6,051
4
48
67

2

Note that you can skip null definition, i.e.: `t.string :title, index: { unique: true }` – Parziphal Jun 29 '21 at 04:57
So this will create an index but it's not going to be unique, right? – Burak Kaymakci Sep 13 '21 at 10:01
@BurakKaymakci it will be unique. If you want non unique index use simply `index: true`. – Pioz Sep 13 '21 at 10:02
Oh sorry that's my mistake. I just got lost and asked a stupid question. You answered exactly what I wanted to ask haha. Thank you! I had wanted to ask whether `index: true` would create a unique index – Burak Kaymakci Sep 13 '21 at 10:09
@Parziphal so the `index: { unique: true }` is covering from `null` values out of the box? – alexventuraio Apr 18 '23 at 22:56

score 57 · Answer 4 · answered Mar 10 '17 at 17:30

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)

score 20 · Answer 5 · answered Nov 10 '16 at 16:52

20

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

answered Nov 10 '16 at 16:52

Nicholas Nelson

211
2
3

Note this does not work if the index already exists. – David Gay Jun 22 '20 at 19:36

score 1 · Answer 6 · answered Jan 05 '17 at 11:39

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

score 1 · Answer 7 · edited Jul 29 '17 at 08:55

1

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.

edited Jul 29 '17 at 08:55

Peter Haddad

78,874
25
140
134

answered Jul 28 '17 at 11:15

murali

11
2

Ravistm · Answer 8 · 2019-02-05T17:46:21.630

-1

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

edited Feb 05 '19 at 17:46

answered Dec 28 '18 at 14:48

Ravistm

2,163
25
25

3

I would not recommend just adding the validation without a corresponding index. The better option is to clean up any existing duplicates and then add the index. Otherwise you risk invalidating existing data (which will cause any updates to those rows to fail), and you could still end up with duplicates if you have any code that skips Rails validations. (e.g., when running an update_all, or direct SQL inserts) – Nate Feb 04 '19 at 20:11
1

This is good enough to show a nice error message but not to enforce integrity of your data. See https://thoughtbot.com/blog/the-perils-of-uniqueness-validations for an explanation. – Pascal Nov 12 '21 at 16:23

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

8 Answers8

Linked

Related