Rails 3. Validating email uniqueness and case sensitive fails

Question

I am developing an app in Rails 3 and upon signup I need the user to enter their email address and I need it to be unique and case sensitive. I.e. no one should be able to sign up with [email protected] when [email protected] already exists in the database.

This is my code and it crashes the app:

validates :email, :presence => true, :uniqueness => true, :case_sensitive => true,
                      :format => {:with => /^([^@\s]+)@((?:[-a-z0-9]+\.)+[a-z]{2,})$/i}

What is wrong with it?

Answer 1

I don't have the reputation to comment on the accepted answer, but @medBo asked about how this behaves in Rails 4. For reference, when using Rails 4.2 + MySQL, if I specify

validates :username, uniqueness: { case_sensitive: true }

ActiveRecord performs this query:

SELECT  1 AS one FROM `users` WHERE `users`.`username` = 'TEST_USER' LIMIT 1

In this case the search is not case sensitive. But when I set:

validates :username, uniqueness: { case_sensitive: false }

it performs:

SELECT  1 AS one FROM `users` WHERE `users`.`username` = BINARY 'TEST_USER'

The BINARY operator ensures the search is case sensitive without fetching all users, meaning for my setup at least, the case_sensitive flag doesn't suffer from the performance issue that @Michael Koper notes for earlier versions of Rails. I can't comment on how ActiveRecord performs for other database setups.

Answer 2

For the Rails 3 type of validation you need to nest the casse insensitive block like so

validates :email, :uniqueness => { :case_sensitive => false }

Answer 3

Please dont use case sensitive there!!!. It will fetch all the users! So if you have 100.000 users. first it will fetch them all with LOWER(email). This can be VERY slow and it wont use your index on email.

Here an article that i found just now about this topic: http://techblog.floorplanner.com/post/20528527222/case-insensitive-validates-uniqueness-of-slowness

My suggesting is: Run a query to make all the emails downcased and make a before validation filter to downcase the email attribute so you dont have any uppercased characters in that column.

User.update_all('email = LOWER(email)')

before filter:

before_validation :downcase_email

private

def downcase_email
  self.email = email.downcase if email.present?
end