title: Possible to specify unique index with NULLs allowed in Rails/ActiveRecord?
body: <p>I want to specify a unique index on a column, but I also need to allow <code>NULL</code> values (multiple records can have <code>NULL</code> values). When testing with PostgreSQL, I see that I can have 1 record with a <code>NULL</code> value, but the next will cause an issue:</p>
  
  <pre><code>irb(main):001:0&gt; u=User.find(5)
    User Load (111.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT 1  [["id", 5]]
  =&gt; #&lt;User id: 5, email: "a@b.com", created_at: "2013-08-28 09:55:28", updated_at: "2013-08-28 09:55:28"&gt;
  irb(main):002:0&gt; u.email=nil
  =&gt; nil
  irb(main):003:0&gt; 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
  =&gt; false
  </code></pre>
  
  <p>So even if the database allows it, Rails first checks to see if a <code>User</code> exists with a different id and with the <code>email</code> column set to <code>NULL</code>. Is there a way that not only the database can allow it, but Rails will not check first like above as well?</p>
  
  <p>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.</p>
  
  <p><em><strong>UPDATE</em></strong>: Here's the migration code I had created to add the <code>email</code> column:</p>
  
  <pre><code>class AddEmailToUsers &lt; ActiveRecord::Migration
    def change
      add_column :users, :email, :string
      add_index :users, :email, :unique =&gt; true
    end
  end
  </code></pre>
  
  <p>And here's the code I had added to the <code>User</code> model:</p>
  
  <pre><code>validates :email, uniqueness: true
  </code></pre>
  
  <p>I forgot that I had added the <code>validates</code> call to the <code>User</code> 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 <code>NULL</code> fields? Is there a way to specify in Rails that I want to validate the email is unique unless it's <code>nil</code>?</p>