121

I have a table and I'm trying to add a unique index on two columns. Those columns are also indexed. So my question is if I just can remove the indexes who were just for one column or if I have to use all three indexes:

add_index "subscriptions", ["user_id"]
add_index "subscriptions", ["content_id"]
add_index "subscriptions", ["user_id"], ["content_id"], :unique => true
Maksim Kalmykov
  • 1,293
  • 3
  • 20
  • 26
Markus
  • 3,948
  • 8
  • 48
  • 64
  • 6
    As a side note: If you are using MySQL it makes no sense to have your `used_id` and `content_id` in separate indexes if you're also having a unique index of both columns. This probably also applies to other DBs... Contrary to what you expect, it will have a negative impact on performance (especially insertions/updates). – hurikhan77 Nov 08 '10 at 13:12
  • How does this work with nil values? Is there any way to do this with nullable column and if so how would null be counted? I would like to have it allow where one of the values could be nil, and allow for multiple tuples of that, but as soon as the nullable column gets value I want the combination to be unique. Is this possible? – Brainmaniac Jan 04 '22 at 12:26

1 Answers1

238
add_index :subscriptions, [:user_id, :content_id], unique: true
Dorian
  • 22,759
  • 8
  • 120
  • 116
shingara
  • 46,608
  • 11
  • 99
  • 105
  • 7
    There are alternative syntaxes as well: add_index :subscriptions, [:user_id, :content_id], :unique => true add_index :subscriptions, %w(user_id content_id), :unique => true They're the same thing, just different syntax for specifying the columns. – François Beausoleil Nov 08 '10 at 13:30
  • 16
    @FrançoisBeausoleil `%w(user_id content_id)` in ruby just creates an array of strings, it's not special to rails. You can do the same with `"user_id content_id".split` which is still creating an array of strings. I am sure you know this, this comment is just so other readers don't relate this to rails incorrectly :) – Khaja Minhajuddin Feb 18 '13 at 10:19
  • What would be the syntax for this on table creation? <- found answer (https://stackoverflow.com/questions/4870961/validate-uniqueness-of-multiple-columns) – tnaught Mar 15 '18 at 16:15
  • I am having weird problem after using this. Not able to run any new migration using rake db:migrate. Only able to run migration using version number rake db:migrate:up VERSION=20180411062714 one file at a time. I already had data on the table on which I did unique true on two column. – thedudecodes Apr 19 '18 at 06:49
  • 1
    @FrançoisBeausoleil Well, actually they are not the same thing, `%w(user_id content_id)` is an Array of Strings. Which is the same as `['user_id', 'content_id']`. The different notation for Array of Symbols is `%i(user_id content_id)`, or as the OP wrote it `[:user_id, :content_id]` – Sasa Blagojevic Jun 09 '18 at 19:07
  • I'm not so familiar with ActiveRecord concepts, so I'm wondering if anyone could answer a question about this for me. – zigzag Feb 23 '21 at 18:29
  • @zigzag Open a new question if you want to ask a new question. Comment if you want to ask for clarification about an existing answer. – Luc Apr 09 '21 at 15:41