validates_uniqueness_of

Back it up with a unique index

As mentioned briefly above, as well as using this validation in your model you should ensure the underlying database table also has a unique index to avoid a race condition.

For example:

class User < ActiveRecord::Base
  validates_uniqueness_of :login_name
end

The index can be specified in the migration for the User model using add_index like this:

add_index :users, :login_name, :unique => true

You do a similar thing when using the :scope option:

class Person < ActiveRecord::Base
  validates_uniqueness_of :user_name, :scope => :account_id
end

Should have a migration like this:

add_index :people, [ :account_id, :user_name ], :unique => true

Note that both the attribute being validated (:user_name) and the attribute(s) used in the :scope (:account_id) must be part of the index.

For a clear and concise explanation of the potential for a race condition see Hongli Lai’s blog.

Does not work with polymorphic relations

If you have polymorphic relations, e.g.:

class Bookmark < ActiveRecord::Base
  belongs_to :thing, :polymorphic => true
  belongs_to :owner, :polymorphic => true
end

and you want to ensure that a thing can bookmarked by an owner at most once, you can’t do this:

validates_uniqueness_of :thing, :scope => :owner

Instead, you must use the real column names, e.g.:

validates_uniqueness_of :thing_id, :scope => [:thing_type, :owner_id, :owner_type]

multi scope to sql

validates_uniqueness_of :name, :scope => [:big_category_id, :small_category_id]

SELECT * FROM schedules WHERE (products.name = 'xxxx' AND products.big_category_id= 1 AND products.small_category_id = 1) LIMIT 1

Migration for uniqueness with existent data in DB

I’m using sub-transaction to update existent records on DB. I use this approach to update the uniqueness field when it value dependent on another existent field without uniqueness restriction.

Migration for uniqueness with existent dependent data in DB

class AddUniquenessBarToFoo < ActiveRecord::Migration
  class Foo < ActiveRecord::Base
  end

  def change

    add_column :foos, :bar, :string
    execute "ALTER TABLE foos ADD CONSTRAINT uk_foods_bar UNIQUE (bar)"    

    Foo.reset_column_information
    Foo.all.each do |f|
      begin
        #try get unique value in a new sub-transaction
        Foo.transaction(requires_new: true) do
          f.update_attributes!(:bar => "some ops. with another non-unique existent field to set this")
        end
      rescue ActiveRecord::StatementInvalid
         #We can't reuse a crashed transaction. New one.
         Foo.transaction(requires_new: true) do
          #Alternative unique value, if another error exist it's another
          #migration problem and then raise new error.
          f.update_attributes!(:bar => "some operation to set this-#{f.id}")
        end
      end
    end   
    change_column :foos, :bar, :string, :null => false

  end
end

Be aware about performance that is transaction per record for big DB.