Soft Delete and Unique Constraint

Icons created by Pixel perfect — Flaticon

When using the soft delete mechanism on the database, you might run into a situation where a record with a unique constraint was deleted and you want to reuse the unique value.

If the unique value is a user email, you might assume that it is the same user that is trying to recreate its account and opt to restore the deleted record after confirming the user identity. Depending on the application, restoring a deleted record might not be viable due to relationship complexity, business rules, and data history.

In other scenarios, the new record that uses the same unique value might not represent the deleted one, and therefore restoring would be wrong. For example, we could have a unique constraint on articles titles and different users might try to use the same title.

How to reuse the value from the deleted record?

Partial Indexes

Some databases, e.g. PostgreSQL, have partial indexes which is an index applied to a subset of entries according to a conditional expression. We can create the unique index only for non-deleted records.

CREATE UNIQUE INDEX “users_email_unique”
ON users(email, deleted_at)
WHERE deleted_at IS NULL;

This way we would be able to have the same email on multiple records as long as only one is not deleted.

Virtual Columns

If your database does not have partial indexes, e.g. MySQL, you could use a different approach with virtual columns.

Considering that in most SQL databases, the UNIQUE constraint ignores NULL values, we can use a composite key in the UNIQUE constraint that includes a nullable column that indicates if a record was deleted.

To do so, we can add a virtual column not_archived that gets its value from the soft delete column.

ALTER TABLE users
ADD not_archived BOOLEAN
GENERATED ALWAYS AS (IF(deleted_at IS NULL, 1, NULL)) VIRTUAL;

This will result in a field that gets automatically updated according to the deleted_at column. It will have the value 1 when the record is not deleted (deleted_at=NULL), else it will be NULL. Now we just need to add it to the UNIQUE constraint. Remember to drop the old constraint.

ALTER TABLE users
ADD CONSTRAINT UNIQUE (email, not_archived);

Conclusion

Now you have two approaches to deal with UNIQUE constraints and soft deletes. A few considerations:

• Make sure that your database ignores NULL on UNIQUE constraints;
• Only one non-deleted UNIQUE value can exist. Restoring soft deleted records might fail due to this constraint;
• Composite keys impact performance.