← All skills

UNIQUE on a soft-deleted table

A deleted row still holds its unique key. A generated column projects dead rows to NULL so only live rows are constrained.

REPO gengyue-ai/soft-delete-aware-unique-constraintLICENSE MITWHAT WE VERIFIED Reproduced · 2026-11-18
The problem

A user deletes a record and then cannot create it again, because the soft-deleted row still occupies the unique index.

Soft delete and a unique index are natural enemies: the deleted row is still a row, so it still holds the key. “NULL never equals NULL, so the index ignores deleted rows” is true only when the constrained column itself is NULL — and slug is not.

The fix is to make it NULL:

ALTER TABLE projects
  ADD COLUMN slug_live VARCHAR(100)
    GENERATED ALWAYS AS (IF(deleted_at IS NULL, slug, NULL)) VIRTUAL,
  ADD UNIQUE KEY uk_projects_owner_slug_live (owner_id, slug_live);

The index now covers live rows only. A deleted row keeps its slug for the audit trail and projects NULL into the index.

What the run shows

ERROR 1062 (23000): Duplicate entry '1-demo' for key 'projects.uk_projects_account_slug_live'
| id | account_id | slug | live | slug_live |
|  1 |          1 | demo |    0 | NULL      |
|  3 |          1 | demo |    1 | demo      |
ERROR 1062 (23000): Duplicate entry '1-demo' for key 'projects_naive.uk_naive'

A second live row is refused; a deleted row does not block re-creation; and the same sequence against a naive UNIQUE (owner_id, slug) fails — that last error is the bug users report.

Before the DDL

  • Does anything SELECT * from this table? A virtual column adds one to * and positional scans shift.
  • Does anything build an INSERT from INFORMATION_SCHEMA? Generated columns are not writable (3105).
  • Any live duplicates already? The index will refuse to build.
  • mysqldump the table. It is DDL.

What we verified

examples/repro.sql executed on MySQL 8.4.13 (Ubuntu 26.04, WSL); the 1062 errors, the NULL projection and the naive-constraint contrast are all pasted from that run.

Install

Copy SKILL.md into skills/<category>/soft-delete-aware-unique-constraint/, or run examples/repro.sql against a scratch MySQL.

Repository ↗

Sources