It's a common scenario we need to place a
UNIQUE INDEX database constraint for data integrity. But sometime the requirements comes like we need to allow
null values for that column.
We can achieve this simply with rails migration.
add_index :users, :username, unique: true, where: 'username IS NOT NULL'
Also a model level validation for this constraint would be nice:
validates_uniqueness_of :username, allow_nil: true
If you also want to allow empty values aka empty string, migration would be like
add_index :users, :username, unique: true, where: "(username IS NOT NULL) OR (username != '')"
And the model validation:
validates_uniqueness_of :username, allow_blank: true
I've tested this with Rails and PostgreSQL. It should work for other databases as well.