Possible to specify unique index with NULLs allowed in Rails/ActiveRecord?

Question

I want to specify a unique index on a column, but I also need to allow NULL values (multiple records can have NULL values). When testing with PostgreSQL, I see that I can have 1 record with a NULL value, but the next will cause an issue:

irb(main):001:0> u=User.find(5)   User Load (111.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 5]] => #<User id: 5, email: "[email protected]", created_at: "2013-08-28 09:55:28", updated_at: "2013-08-28 09:55:28"> irb(main):002:0> u.email=nil => nil irb(main):003:0> u.save    (1.1ms)  BEGIN   User Exists (4.8ms)  SELECT 1 AS one FROM "users" WHERE ("users"."email" IS NULL AND "users"."id" != 5) LIMIT 1    (1.5ms)  ROLLBACK => false 

So even if the database allows it, Rails first checks to see if a User exists with a different id and with the email column set to NULL. Is there a way that not only the database can allow it, but Rails will not check first like above as well?

The idea is users don't have to enter an email, but if they do I need to be able to find a user by their email. I know I can create another model to associate users to emails, but I'd much rather do it the above way.

UPDATE: Here's the migration code I had created to add the email column:

class AddEmailToUsers < ActiveRecord::Migration   def change     add_column :users, :email, :string     add_index :users, :email, :unique => true   end end 

And here's the code I had added to the User model:

validates :email, uniqueness: true 

I forgot that I had added the validates call to the User model. So that makes sense that Rails is checking first. I guess the only other question is if it's safe for databases to have a unique index and NULL fields? Is there a way to specify in Rails that I want to validate the email is unique unless it's nil?

Answer 1

Your migration will work and will allow multiple null values (for the most database engines).

But your validation for the user class should look like below.

validates :email, uniqueness: true, allow_nil: true