The key a deleted row still holds
A soft-deleted row is still a row, so it still occupies the unique key — which is why a plain UNIQUE (owner_id, slug) blocks users from re-creating something they deleted. The fix is a generated column that projects dead rows to NULL, so the index only covers live rows: slug_live = IF(deleted_at IS NULL, slug, NULL). It also means the liveness predicate must be deleted_at IS NULL, never status <> "deleted".
The report is always the same shape: “I deleted it, and now it says it already exists.”
Why the delete did not free the key
A soft delete is an UPDATE, not a DELETE. The row stays, its columns stay, and the unique index keeps its entry. So the second insert — the one a user expects to succeed after deleting — collides with a row that is, in their mind, gone.
The instinct is “NULL does not equal NULL, so the index will ignore deleted rows”. That is true only when the constrained column itself is NULL. slug is not NULL.
The liveness predicate people get wrong
Before designing anything, decide what “live” means, from the schema and not from the enum you remember:
-- the liveness test is this, and only this
SELECT COUNT(*) FROM projects WHERE deleted_at IS NULL;
In one real table, status never had a deleted value — the soft delete only wrote deleted_at. Counting with WHERE status <> 'deleted' classified 13 deleted rows as live duplicates, which produced the wrong conclusion: “the data must be cleaned before an index can be added”. The data was fine; the predicate was fiction.
Why a plain UNIQUE cannot work
Two steps, and both matter:
- Deleted rows keep
(owner_id, slug), so a hard constraint rejects legitimate re-creation. - The “NULL trick” only applies to a column that is actually NULL — and the columns you care about are the identity columns.
So project the dead rows into NULL and constrain that instead.
The DDL
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);
What the run produced, in order:
ERROR 1062 (23000) at line 24: 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) at line 43: Duplicate entry '1-demo' for key 'projects_naive.uk_naive'
Three facts in one log: a second live row is refused; the deleted row’s projection is NULL so re-creation succeeds; and the same sequence against a naive UNIQUE (owner_id, slug) fails — that last error is the one users report.
Two ALTER pitfalls
| Pitfall | Symptom | Fix |
|---|---|---|
Adding ALGORITHM=INPLACE |
ERROR 1846 … INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions |
Drop the hint; take the default COPY path |
VARCHAR length copied from an example |
Index rejected, message not obviously about length | Match the source column’s length exactly |
And a third that only appears later: generated columns are not writable (error 3105). Any INSERT built dynamically from INFORMATION_SCHEMA will now try to write one.
Three checks before you run it
- Does anything
SELECT *from this table? A virtual column adds one to*, so positional scans shift by one — silently, in a language where that compiles. - Does anything build an
INSERTfromINFORMATION_SCHEMA? That is the 3105 path above. - Are there live duplicates already? The index refuses to build. Count them with the correct predicate, and dump the table first:
mysqldump projects | gzip > projects.sql.gz.
Map 1062 in every write path
The constraint is half the fix. The other half is the message users see:
if err != nil && (strings.Contains(err.Error(), "Duplicate entry") || strings.Contains(err.Error(), "1062")) {
return 0, fmt.Errorf("slug %q already exists for this account — refresh and retry", slug)
}
Enumerate every INSERT into the table. In our case there were two: an automatic creation path and a generic CRUD handler. Patching one of two is the same as patching neither.
How you know it worked
- Live duplicate refused:
START TRANSACTION; INSERT …; ROLLBACK;→ expect 1062. - Deleted row does not block: same trick after a soft delete → expect success.
- The index only covers live rows:
SELECT COUNT(*) FROM projects WHERE slug_live IS NOT NULLequals the live row count. - Concurrency: eight simultaneous inserts of the same key, exactly one row lands, the rest get 1062. Use
xargs -P 8rather than shell&— many shells and CI wrappers reject background jobs, and a serial “concurrency test” proves nothing.
How to undo it
- Drop the index and the column together. Dropping only the index leaves a column that
SELECT *callers are already scanning. - Restore the data, not just the schema:
DROP COLUMNon a virtual generated column is safe (nothing is stored), but if you also cleaned duplicates during the rollout, that delete is the part you cannot revert from DDL. - Keep the pre-DDL dump until you have seen one full write cycle succeed against the new constraint. The dump is the only artifact that can restore rows, not just structure.
- If the fix was
ON DUPLICATE KEY UPDATEon this table, re-test it now: the table has a generated column it did not have before.
What we have not verified
- Behaviour on a table big enough for the COPY ALTER to matter. Ours was small; a 50M-row table needs a different rollout (gh-ost / pt-online-schema-change), and we have not run one.
- Whether the generated column confuses MySQL’s own
ON DUPLICATE KEY UPDATEin this exact shape. - The lock footprint of the COPY path under production write load. We measured correctness, not blocking.
The runnable reproduction, with the naive-constraint contrast in the same file, is in the repo.
Related
Questions people actually ask
Why not just delete the row for real?
Because the audit trail, foreign keys and reporting usually need it. Once it is a soft delete, the unique index has to be made aware of that, and MySQL has no partial index.
Does the generated column cost storage?
A VIRTUAL generated column is computed on read and not stored; it participates in the index. The index entry is what costs space, and it now only exists for live rows.
What is the liveness predicate?
deleted_at IS NULL. Not status <> "deleted" — in one real table that enum value never existed, so the wrong predicate counted 13 deleted rows as live duplicates and produced a wrong conclusion about cleaning data.
Can the index be added online?
Not with ALGORITHM=INPLACE: adding a virtual column together with other ALTER actions fails with 1846. Let it take the default COPY path, which is seconds on a small table.
Sources
Last checked: 2026-11-18 · Reproduced on MySQL 8.4.13 (Ubuntu 26.04, WSL) on 2026-11-18: duplicate live rows raise 1062 on the generated-column index, a soft-deleted row does not block re-creation, and the same sequence fails on a naive UNIQUE — output pasted below.