Validate Uniqueness in Rails

Apr 20, 2017

Uniquess constraints are an everyday fact of life. For example, in our application we might want to validate that all usernames are unique. We can easily achieve this with a unique validation.

Uniqueness Validations and Constraints

class User < ActiveRecord::Base
  validates :username, uniqueness: true
end

Note that this does not guarantee uniqueness, since application-layer checks are inherently prone to race conditions. Rails will perform the uniqueness check before creating the user, so you might have the following order of events.

# User 1 Checks to see if their username is available
SELECT * FROM users WHERE username = 'bob';

# User 2 Checks to see if their username is available
SELECT * FROM users WHERE username = 'bob';

# Since no duplicates were found User 1 is created
INSERT INTO users (...,username,...) VALUES (...,'bob',...);

# Since no duplicates were found User 2 is created
INSERT INTO users (...,username,...) VALUES (...,'bob',...);

This could happen even if you use transactions. The preferred solution to this problem is to add a unique index at the database level.

class AddUniqueIndexOnUsername < ActiveRecord::Migration
  def change
    add_index :users, :username, unique: true
  end
end

This means the database will ensure the uniquess of this column, avoiding the race condition.

Scoped Uniqueness

We don’t always need the uniqueness constraint to apply to all records though. For example, when a user is deleted we might choose to simply flag them as deleted in order to maintain referential integrity across other records. A common way to implement this behavior is to have a deleted_at column on the table.

class AddDeletedAtToUsers < ActiveRecord::Migration
  def change
    add_column :users, :deleted_at, :datetime
  end
end

This also changes the uniquess constraint - instead of requiring that usernames be unique we now only need to check that usernames are unique for non-deleted users.

We can achieve this in the application layer by adding a condition to our validation.

class User < ActiveRecord::Base
  scope :not_deleted, -> { where(deleted_at: nil) }
  validates :username, uniqueness: { constraint: -> { not_deleted } }
end

We also need to modify our database index to only apply the uniqueness constraint to records where the deleted_at column is null. The way to do this depends on your database server, but if you’re using PostgreSQL you would use a Partial Unique Index.

class AddUniqueIndexOnUsername < ActiveRecord::Migration
  def up
    execute <<-SQL
      CREATE UNIQUE INDEX users_username_constraint ON users (username) WHERE deleted_at IS NULL;
    SQL
  end

  def down
    execute <<-SQL
      DROP INDEX users_username_constraint;
    SQL
  end
end

Unfortunately the migration API doesn’t support this type of index, which means you will need to ditch schema.rb in favor of structure.sql. (Although it sounds like Rails 5 will be supporting this in the migration API)

Happy coding.