How do you validate uniqueness of a pair of ids in Ruby on Rails?

Question

Suppose the following DB migration in Ruby:

    create_table :question_votes do |t|
      t.integer :user_id
      t.integer :question_id
      t.integer :vote

      t.timestamps
    end

Suppose further that I wish the rows in the DB contain unique (user_id, question_id) pairs. What is the right dust to put in the model to accomplish that?

validates_uniqueness_of :user_id, :question_id

seems to simply make rows unique by user id, and unique by question id, instead of unique by the pair.

Note: I haven't gone back to this project, so I haven't had time to test the answers below. If someone posts an answer that has the shortest possible test that demonstrates an answer, and the output of that test, I will accept that answer. Thanks. — dfrankow, Dec 12 '09 at 19:18

score 95 · Answer 1 · answered May 29 '09 at 00:12

95

validates_uniqueness_of :user_id, :scope => [:question_id]

if you needed to include another column (or more), you can add that to the scope as well. Example:

validates_uniqueness_of :user_id, :scope => [:question_id, :some_third_column]

answered May 29 '09 at 00:12

Demi

6,147
7
36
38

should the value be in square brackets, not curly braces? – ahnbizcad Aug 28 '14 at 11:24
3

@gwho **Square brackets** denote an **array, a sequence of values**. **Curly braces** denote a **hash, a set of key-value pairs**. We're not specifying keys here, only values. I understand it's kinda late and you probably know it already, but it might help out beginners coming here :) – D-side Nov 07 '14 at 14:40
Oh it's not a scope lambda. – ahnbizcad Nov 08 '14 at 01:18
3

If you prefer the `validates` syntax: `validates :user_id, uniqueness: { scope: [:question_id] }` – februaryInk Jun 09 '15 at 20:17
Will this validation only fail when `user_id and question_id` both are same? – aks Nov 28 '16 at 09:48

score 27 · Answer 2 · edited Jul 01 '14 at 16:12

27

If using mysql, you can do it in the database using a unique index. It's something like:

add_index :question_votes, [:question_id, :user_id], :unique => true

This is going to raise an exception when you try to save a doubled-up combination of question_id/user_id, so you'll have to experiment and figure out which exception to catch and handle.

edited Jul 01 '14 at 16:12

Ciro Santilli OurBigBook.com

347,512
102
1,199
985

answered Jun 24 '10 at 08:27

pakeha

2,480
3
22
24

Yes, but the exception don't help when you want just an error message. – shingara Jun 24 '10 at 08:37

score 20 · Answer 3 · edited Jun 01 '17 at 18:31

20

The best way is to use both, since rails isn't 100% reliable when uniqueness validation come thru.

You can use:

  validates :user_id, uniqueness: { scope: :question_id }

and to be 100% on the safe side, add this validation on your db (MySQL ex)

  add_index :question_votes, [:user_id, :question_id], unique: true

and then you can handle in your controller using:

  rescue ActiveRecord::RecordNotUnique

So now you are 100% secure that you won't have a duplicated value :)

edited Jun 01 '17 at 18:31

Dorian

22,759
8
120
116

answered Feb 06 '15 at 12:01

dpedoneze

949
7
17

1

This should be the best answer, validates alone is not enough! See this article for the details why: https://robots.thoughtbot.com/the-perils-of-uniqueness-validations – Eugene Jan 21 '16 at 05:16
1

Best response, thanks for including everything needed! – xaunlopez Jan 11 '19 at 02:38

Jonathan Lin · Answer 4 · 2015-06-11T03:31:41.913

15

From RailsGuides. validates works too:

class QuestionVote < ActiveRecord::Base
  validates :user_id, :uniqueness => { :scope => :question_id }
end

edited Jun 11 '15 at 03:31

answered Aug 16 '12 at 07:13

Jonathan Lin

19,922
7
69
65

eggdrop · Answer 5 · 2009-05-29T00:19:31.653

10

Except for writing your own validate method, the best you could do with validates_uniqueness_of is this:

validates_uniqueness_of :user_id, :scope => "question_id"

This will check that the user_id is unique within all rows with the same question_id as the record you are attempting to insert.

But that's not what you want.

I believe you're looking for the combination of :user_id and :question_id to be unique across the database.

In that case you need to do two things:

Write your own validate method.
Create a constraint in the database because there's still a chance that your app will process two records at the same time.

edited May 29 '09 at 00:19

answered May 29 '09 at 00:14

eggdrop

3,356
5
29
32

What if I do

validate_uniqueness_of :user_id, :scope => :question_id
validate_uniqueness_of :question_id, :scope => :user_id

Is that enough? – dfrankow May 29 '09 at 13:33

1

I'm not seeing the difference between `validates_uniqueness_of` using `scope` and the combination being unique. Can you provide an example of how they would differ? – Leopd Mar 20 '13 at 19:38

score 2 · Answer 6 · answered Jul 15 '16 at 17:41

When you are creating a new record, that doesn't work because the id of your parent model doesn't exist still at moment of validations.

This should to work for you.

class B < ActiveRecord::Base
  has_many :ab
  has_many :a, :through => :ab
end

class AB < ActiveRecord::Base
  belongs_to :b
  belongs_to :a
end

class A < ActiveRecord::Base
  has_many :ab
  has_many :b, :through => :ab

  after_validation :validate_uniqueness_b

  private
  def validate_uniqueness_b
    b_ids = ab.map(&:b_id)
    unless b_ids.uniq.length.eql? b_ids.length
      errors.add(:db, message: "no repeat b's")
    end
  end
end

In the above code I get all b_id of collection of parameters, then compare if the length between the unique values and obtained b_id are equals.
If are equals means that there are not repeat b_id.

Note: don't forget to add unique in your database's columns.

How do you validate uniqueness of a pair of ids in Ruby on Rails?

6 Answers6

Linked