Soft Delete e Unique Constraint

ícones criados por Pixel perfect — Flaticon

Ao utilizar o mecanismo de soft delete no banco de dados, você pode encontrar uma situação em que um registro com unique contraint foi deletado e você deseja reutilizar o valor único.

Vamos considerar um cenário onde o valor único é o email de um usuário e uma pessoa com a conta desativada solicitou reativação. Você (desenvolvedor) pode restaurar o registro no banco de dados após confirmar a identidade da pessoa. Dependendo da situação, restaurar um registro deletado pode não ser viável devido a relações complexas entre entidades do banco de dados, regras de negócio e histórico de dados.

Em outros cenários, o novo registro que utiliza o mesmo valor único pode não representar um registro antigo, logo sua restauração seria incorreta. Por examplo, pode haver um unique constraint em títulos de arquivos e diferentes autores tentam utilizar o mesmo título.

Como reutilizar o valor de um registro deletado?

Índice Parcial

Alguns banco de dados como PostgreSQL possuem índices parciais, que é um índice aplicado somente à um subconjunto de registros de uma tabela de acordo com uma expressão condicional. É possível criar um índice parcial com unique constraint somente para registros não deletados.

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

Dessa forma seria possível utilizar o mesmo email em registros diferentes contanto que apenas um não tenha sido deletado.

Coluna virtual

Caso o banco de dados não possua índice parcial, como MySQL, é possível utilizar uma abordagem diferente com com colunas virtuais.

Considerando que na maioria dos bancos de dados SQL, o unique constraint ignora valores nulos, é possível utilizar uma chave composta na unique constraint que inclua uma coluna nullable.

Para tal, basta adicionar uma coluna virtual, que recebe seu valor a partir da coluna utilizado no soft delete.

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.

Isso resultará em uma coluna not_archived cujo valor em registros é automaticamente atualizado conforme a coluna deleted_at. O valor sera 1 quando o registro não estiver deletado ou NULL caso contrário. Agora basta adicionar essa coluna ao unique constraint. Lembre-se de remover o constraint antigo, caso exista.

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

Conclusão

Foram apresentadas duas abordagens para lidar com a combinação de unique constraint com soft delete. Algumas considerações:

• Verifique que seu banco de dados ignora NULL em unique constraint;
• Apenas um registro não deletado com valor único pode existir. Enquanto isso, não é possível restaurar registros deletados com esse valor;
• Chaves compostas impactam a performance.