Rails “validates_uniqueness_of” Case Sensitivity

Question

Here is the model (I am using SQLLite3):

class School < ActiveRecord::Base

  validates_uniqueness_of :name

end

For example, after I add "Yale", I cannot add "Yale" but can add "yale." How can I make the validation case insensitive?

EDIT: Found it - Active Record Validations

Answer 1

There is a similar question but the answer is more interesting: https://stackoverflow.com/a/6422771

Basically, using :case_sensitive => false performs a very inefficient database query.

Answer 2

validates_uniqueness_of :name, :case_sensitive => false does the trick, but you should keep in mind that validates_uniqueness_of does not guarantee uniqueness if you have multiple servers/server processes (e.g. running Phusion Passenger, multiple Mongrels, etc) or a multi-threaded server. That's because you might get this sequence of events (the order is important):

1. Process A gets a request to create a new user with the name 'foo'
2. Process B does the same thing
3. Process A validates the uniqueness of 'foo' by asking the DB if that name exists yet and the DB says the name doesn't exist yet.
4. Process B does the same thing and gets the same response
5. Process A submits the insert statement for the new record and succeeds
6. If you have a database constraint requiring uniqueness for that field, Process B will submit the insert statement for the new record and fail with a ugly server exception that comes back from the SQL adapter. If you do not have a database constraint, the insert will succeed and you now have two rows with 'foo' as the name.

See also "Concurrency and integrity" in the validates_uniqueness_of Rails documentation.

From Ruby on Rails 3rd Edition:

...despite its name, validates_uniqueness_of doesn’t really guarantee that column values will be unique. All it can do is verify that no column has the same value as that in the record being validated at the time the validation is performed. It’s possible for two records to be created at the same time, each with the same value for a column that should be unique, and for both records to pass validation. The most reliable way to enforce uniqueness is with a database-level constraint."

See also this programmer's experience with validates_uniqueness_of.

One way this commonly happens is accidental double-submissions from a web page when creating a new account. This is a hard one to solve because what the user will get back is the second (ugly) error and it will make them think their registration failed, when in reality it succeeded. The best way I've found to prevent this is just to use javascript to try to prevent double-submission.

Answer 3

In rails 3 you can do this in your model:

validates :name, :uniqueness => true

or without case_sensitivity

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

Answer 4

There is an option where you can specify case insensitivity

  validates_uniqueness_of :name, :case_sensitive => false