Alex Rivera | Logout

Case-insensitive unique index in Rails/ActiveRecord?

Asked 2011-10-30T22:55:06.683
31

I need to create a case-insensitive index on a column in rails. I did this via SQL:

execute(
   "CREATE UNIQUE INDEX index_users_on_lower_email_index 
    ON users (lower(email))"
 )

This works great, but in my schema.rb file I have:

add_index "users", [nil], 
  :name => "index_users_on_lower_email_index", 
  :unique => true

Notice the "nil". So when I try to clone the database to run a test, I get an obvious error. Am I doing something wrong here? Is there some other convention that I should be using inside rails?

Thanks for the help.

ruby-on-rails activerecord
Edit
Report

1 Answer

3

The documentation is unclear on how to do this but the source looks like this:

def add_index(table_name, column_name, options = {})
  index_name, index_type, index_columns = add_index_options(table_name, column_name, options)
  execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{index_columns})"
end

So, if your database's quote_column_name is the default implementation (which does nothing at all), then this might work:

add_index "users", ['lower(email)'], :name => "index_users_on_lower_email_index", :unique => true

You note that you tried that one but it didn't work (adding that to your question might be a good idea). Looks like ActiveRecord simply doesn't understand indexes on a computed value. I can think of an ugly hack that will get it done but it is ugly:

  1. Add an email_lc column.
  2. Add a before_validation or before_save hook to put a lower case version of email into email_lc.
  3. Put your unique index on email_lc.

That's pretty ugly and you might feel dirty for doing it but that's the best I can think of right now.

answered 2011-10-30T23:16:50.397

Your Answer