Checking for duplicate values across multiple fields with case-insensitive match

Question

I have two fields, name and email, whose combination should be unique without regard for case. Here is code for class and specs. The first 3 tests pass, but not the 4th one.

class Person < ActiveRecord::Base
  validates :name, presence: true
  validates :email, presence: true

  validates_uniqueness_of :email, :scope => :name, :case_sensitive => false
  validates_uniqueness_of :name, :scope => :email, :case_sensitive => false
end

describe Person do
  context "with duplicate name and email" do
    before do
      @person1 = create(:person)
    end
    it "for case-sensitive match of both" do
      expect(build(:person, {name: @person1.name, email: @person1.email})).to_not be_valid
    end
    it "for case-insensitive match of name" do
      expect(build(:person, {name: @person1.name.swapcase, email: @person1.email})).to_not be_valid
    end
    it "for case-insensitive match of email" do
      expect(build(:person, {name: @person1.name, email: @person1.email.swapcase})).to_not be_valid
    end
    it "for case-insensitive match of both" do
      expect(build(:person, {name: @person1.name.swapcase, email: @person1.email.swapcase})).to_not be_valid
    end
  end
end

see this question http://stackoverflow.com/questions/2215237/rails-validates-uniqueness-of-across-multiple-columns-with-case-insensitivity?rq=1 — Thaha kp, Sep 06 '13 at 13:32

score 1 · Answer 1 · edited May 23 '17 at 12:28

I tried to figure out the problem you encounter using the following:

class Person < ActiveRecord::Base
  validates :name,  presence: true 
  validates :email, presence: true

  validates_uniqueness_of :name,  :case_sensitive => false, :scope => [ :email ]
  validates_uniqueness_of :email, :case_sensitive => false, :scope => [ :name ]
end

and

require 'spec_helper'

describe Person do
  context 'with duplicate name and email' do
    before do
      @person1 = Person.new(name: 'test@example.com', email: 'TEST@EXAMPLE.COM')
    end
    subject { @person1 }
    it { should be_valid }
    describe '(I) for case-sensitive match of both' do
      before do
        person = @person1.dup
        person.save
      end
      it { should_not be_valid }
    end
    describe  '(II) for case-insensitive match of name' do
      before do
        person = @person1.dup
        person.name.swapcase!
        person.save
      end
      it { should_not be_valid }
    end
    describe  '(III) for case-insensitive match of email' do
      before do
        person = @person1.dup
        person.email.swapcase!
        person.save
      end
      it { should_not be_valid }
    end
    describe '(IV) for case-insensitive match of both' do
      before do
        person = @person1.dup
        person.name.swapcase!
        person.email.swapcase!
        person.save
      end
      it { should_not be_valid }
    end
  end
end

The behavior of case_sensitive is strange somehow as seen in the log of my example case:

(0.4ms)  SAVEPOINT active_record_1
Person Exists (1.2ms)  SELECT 1 AS one FROM "people" WHERE (LOWER("people"."name") = LOWER('TEST@EXAMPLE.COM') AND "people"."email" = 'test@example.com') LIMIT 1
Person Exists (0.5ms)  SELECT 1 AS one FROM "people" WHERE (LOWER("people"."email") = LOWER('test@example.com') AND "people"."name" = 'TEST@EXAMPLE.COM') LIMIT 1
SQL (3.7ms)  INSERT INTO "people" ("created_at", "email", "name", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["created_at", Sat, 07 Sep 2013 14:21:05 UTC +00:00], ["email", "test@example.com"], ["name", "TEST@EXAMPLE.COM"], ["updated_at", Sat, 07 Sep 2013 14:21:05 UTC +00:00]]
(0.2ms)  RELEASE SAVEPOINT active_record_1
Person Exists (0.3ms)  SELECT 1 AS one FROM "people" WHERE (LOWER("people"."name") = LOWER('test@example.com') AND "people"."email" = 'TEST@EXAMPLE.COM') LIMIT 1
Person Exists (0.3ms)  SELECT 1 AS one FROM "people" WHERE (LOWER("people"."email") = LOWER('TEST@EXAMPLE.COM') AND "people"."name" = 'test@example.com') LIMIT 1
(0.2ms)  ROLLBACK

The problem seems to be that the "LOWER" statements are only being used on either email or name but not on both at the same time. Basically, I would have expected your code to work just fine.

However, as seen in the db-log and also pointed out in another question (Rails 3. Validating email uniqueness and case sensitive fails) it might not be such a good idea to ensure case insensitive behavior in the constraints when performance is an issue ;-) Rather use a before filter to save the email/name in lower case. As this might also not be the best idea (because you very well might want not to lose the case info on the name) you might use yet another lower-case-name-col to ensure the constraint or use an after_valition filter accordingly.

Using the following model should green your test suite:

class Person < ActiveRecord::Base
  validates :name,  presence: true
  validates :email, presence: true

  before_validation :downcase_name_email

  validates_uniqueness_of :name,  :case_sensitive => false, :scope => [ :email ]
  validates_uniqueness_of :email, :case_sensitive => false, :scope => [ :name ]

  private

    def downcase_name_email
      self.email = self.email.downcase if self.email.present?
      self.name = self.name.downcase if self.name.present?
    end

end

Best, Ben.

P.S.: I you are going to take the lower-case approach, be sure to migrate your db-data:

Person.update_all('email = LOWER(email)')
Person.update_all('name = LOWER(name)')

Thanks for taking time to solve and suggest the alternates. While I haven't tried yet, I'm going to write a custom validator to solve the problem. — Srini K, Sep 07 '13 at 15:55
One more thing, just wondering if your "subject { @person1 }" is right. — Srini K, Sep 07 '13 at 16:41

Checking for duplicate values across multiple fields with case-insensitive match

1 Answers1