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.

Solution 1

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]

Solution 2

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.

Solution 3

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

Solution 4

From RailsGuides. validates works too:

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

Solution 5

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:

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

View more solutions

Comments

[dfrankow]

What if I do <pre> validate_uniqueness_of :user_id, :scope => :question_id validate_uniqueness_of :question_id, :scope => :user_id </pre> Is that enough?

[shingara]

Yes, but the exception don't help when you want just an error message.

[Leopd]

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?

[ahnbizcad]

should the value be in square brackets, not curly braces?

[D-side]

@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 :)

[ahnbizcad]

Oh it's not a scope lambda.

[februaryInk]

If you prefer the validates syntax: validates :user_id, uniqueness: { scope: [:question_id] }

[Eugene]

This should be the best answer, validates alone is not enough! See this article for the details why: robots.thoughtbot.com/the-perils-of-uniqueness-validations

[aks]

Will this validation only fail when user_id and question_id both are same?

[xaunlopez]

Best response, thanks for including everything needed!