Soft Delete

Giriş

Açıklaması şöyle

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.

Virtual Columns

Açıklaması şöyle

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.

Şöyle yaparız

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

Açıklaması şöyle

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.

Şöyle yaparız

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