Validate uniqueness of multiple columns

Ruby on-RailsRuby on-Rails-3ValidationActiverecordRails Activerecord

Ruby on-Rails Problem Overview


Is there a rails-way way to validate that an actual record is unique and not just a column? For example, a friendship model / table should not be able to have multiple identical records like:

user_id: 10 | friend_id: 20
user_id: 10 | friend_id: 20

Ruby on-Rails Solutions


Solution 1 - Ruby on-Rails

You can scope a validates_uniqueness_of call as follows.

validates_uniqueness_of :user_id, :scope => :friend_id

Solution 2 - Ruby on-Rails

You can use validates to validate uniqueness on one column:

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

The syntax for the validation on multiple columns is similar, but you should provide an array of fields instead:

validates :attr, uniqueness: {scope: [:attr1, ... , :attrn]}

However, validation approaches shown above have a race condition and can’t ensure consistency. Consider the following example:

  1. database table records are supposed to be unique by n fields;

  2. multiple (two or more) concurrent requests, handled by separate processes each (application servers, background worker servers or whatever you are using), access database to insert the same record in table;

  3. each process in parallel validates if there is a record with the same n fields;

  4. validation for each request is passed successfully, and each process creates a record in the table with the same data.

To avoid this kind of behaviour, one should add a unique constraint to db table. You can set it with add_index helper for one (or multiple) field(s) by running the following migration:

class AddUniqueConstraints < ActiveRecord::Migration
  def change
   add_index :table_name, [:field1, ... , :fieldn], unique: true
  end
end

Caveat : even after you've set a unique constraint, two or more concurrent requests will try to write the same data to db, but instead of creating duplicate records, this will raise an ActiveRecord::RecordNotUnique exception, which you should handle separately:

begin
# writing to database
rescue ActiveRecord::RecordNotUnique => e
# handling the case when record already exists
end 

Solution 3 - Ruby on-Rails

This can be done with a database constraint on the two columns:

add_index :friendships, [:user_id, :friend_id], unique: true

You could use a rails validator, but in general I recommend using a database constraint.

More reading: https://robots.thoughtbot.com/validation-database-constraint-or-both

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionre5etView Question on Stackoverflow
Solution 1 - Ruby on-RailsDylan MarkowView Answer on Stackoverflow
Solution 2 - Ruby on-RailspotashinView Answer on Stackoverflow
Solution 3 - Ruby on-RailsTate ThurstonView Answer on Stackoverflow