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.
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.
mysqldumpthe 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.