Rails multicolumn unique index allowing null or empty values
A Rails application can make use of uniqueness validations to detect duplicated records. However, this is not enough to ensure data integrity. Constraining the values allowed by your application at the database-level, rather than at the application-level, is a more robust way of ensuring your data stays sane.
Database indexes can be used to enforce uniqueness of a column’s value, or the uniqueness of the combined values of more than one column. It might be the case that the requirements at hand specify that one of the columns can be null
or an empty string.
In Rails, this can be done as follows:
class IndexUsersOnEmailAndUserDirectoryId
add_index :users, [:email, :user_directory_id],
unique: true,
where: "(email IS NOT NULL) OR (email != '')"
end
Note that null
values are not considered equal.