Uniquess constraints are an everyday fact of life. For example, in our application we might want to validate that all usernames are unique. We can easily achieve this with a unique validation.
Uniqueness Validations and Constraints
Note that this does not guarantee uniqueness, since application-layer checks are inherently prone to race conditions. Rails will perform the uniqueness check before creating the user, so you might have the following order of events.
This could happen even if you use transactions. The preferred solution to this problem is to add a unique index at the database level.
This means the database will ensure the uniquess of this column, avoiding the race condition.
We don’t always need the uniqueness constraint to apply to all records though. For example, when a user is deleted we might choose to simply flag them as deleted in order to maintain referential integrity across other records. A common way to implement this behavior is to have a
deleted_at column on the table.
This also changes the uniquess constraint - instead of requiring that usernames be unique we now only need to check that usernames are unique for non-deleted users.
We can achieve this in the application layer by adding a condition to our validation.
We also need to modify our database index to only apply the uniqueness constraint to records where the deleted_at column is
null. The way to do this depends on your database server, but if you’re using PostgreSQL you would use a Partial Unique Index.
Unfortunately the migration API doesn’t support this type of index, which means you will need to ditch
schema.rb in favor of
structure.sql. (Although it sounds like Rails 5 will be supporting this in the migration API)