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.




What's on your mind?