skip to content

A users table has a UNIQUE constraint on email and uses a deleted_at column for soft deletes. Someone deletes their account, then signs up again with the same address, and the insert fails. Why does it fail, and how do you model uniqueness so it only applies to live rows?

level: middleimportance: must knowfreq 52%

answer

  1. unique index sees dead rows too
  2. partial index WHERE deleted_at IS NULL
  3. NOT NULL epoch sentinel + UNIQUE(email, deleted_at)
  4. nullable marker in key = no uniqueness at all
  5. or archive/anonymize on delete

basics

~20 s

A UNIQUE constraint covers every row in the table, including soft-deleted ones, so the dead row still occupies the email. Fix it with a partial (filtered) unique index restricted to live rows, or by making the marker part of the key so dead rows differ.

solid answer

~60 s

Uniqueness is enforced by the engine over all stored rows; it has no idea `deleted_at` means "ignore me". The dead row still owns `[email protected]`, so the re-signup collides. Three workable models: 1. **Partial / filtered unique index** — `CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL`. Cleanest: uniqueness applies only to live rows, dead rows may duplicate freely. Available in PostgreSQL, SQL Server and SQLite; MySQL has no partial indexes. 2. **Put the marker in the key** — make the column `NOT NULL` with a sentinel for "alive" (e.g. `deleted_at TIMESTAMPTZ NOT NULL DEFAULT 'epoch'`) and declare `UNIQUE (email, deleted_at)`. Works on every engine. Beware the nullable variant `UNIQUE (email, deleted_at)` with `NULL` for live rows: in standard SQL, NULLs are distinct, so live rows would not be unique at all. 3. **Move dead rows out** — hard delete into an archive table, or anonymize the email on delete (`alice+deleted-42@…`). Live constraints then keep their plain meaning. Choose deliberately: rule 2 also permits two deletions at the same instant to collide.

code

sql · 11 lines
sql
-- A: partial unique index (PostgreSQL / SQL Server / SQLite)
CREATE UNIQUE INDEX users_email_live_uk
  ON users (email)
  WHERE deleted_at IS NULL;

-- B: sentinel in the key (works everywhere, incl. MySQL)
ALTER TABLE users
  ALTER COLUMN deleted_at SET NOT NULL,
  ALTER COLUMN deleted_at SET DEFAULT TIMESTAMP '1970-01-01 00:00:00';
ALTER TABLE users
  ADD CONSTRAINT users_email_uk UNIQUE (email, deleted_at);

go deeper

for a junior

Explain that the constraint applies to all rows, so the deleted row still holds the email, and name the partial-index fix.

for a middle

Show both the partial index and the sentinel-in-key variant, and explain why the nullable version of the composite key silently enforces nothing.

for a senior

Discuss engine support differences, the upsert/foreign-key limitations of an index-not-constraint, and the archive-or-anonymize alternative that removes the problem instead of patching it.

for a principal

Lead with the business question — must the identifier ever be reusable? — and treat identity reuse as a security and correctness decision, not a schema workaround.

## Why the insert fails A `UNIQUE` constraint is implemented by a unique index over the table's stored rows. The index does not know about your application's notion of "deleted" — `deleted_at` is just another column. So after `UPDATE users SET deleted_at = now() WHERE email = '[email protected]'`, the index entry for that email is still there and still enforced. The new signup violates it. This is the single most common concrete bug caused by soft delete, and it shows up on any column with a business-level uniqueness rule: emails, usernames, SKUs, external ids, `(tenant_id, slug)` pairs. ## Option 1 — partial (filtered) unique index The direct expression of the rule "unique among live rows": ``` CREATE UNIQUE INDEX users_email_live_uk ON users (email) WHERE deleted_at IS NULL; ``` Only rows satisfying the predicate are indexed, so any number of soft-deleted rows may share an email while at most one live row may. As a bonus, the index is smaller than a full one and is exactly the index most of your lookups want, since they also filter on `deleted_at IS NULL`. Support differs by engine: PostgreSQL and SQLite call these *partial indexes*, SQL Server calls them *filtered indexes*, Oracle has function-based indexes that can emulate it (index an expression that is NULL for deleted rows, since fully-NULL keys are not stored). MySQL/InnoDB has neither partial nor filtered indexes, so option 2 or 3 applies there. One caveat: a partial unique index is not a declared table constraint in some engines, which means it cannot be the target of a foreign key and may not be usable by `INSERT … ON CONFLICT`/upsert clauses that require a constraint name. Check before relying on it as a conflict target. ## Option 2 — make the marker part of the key If the engine cannot filter, encode the state in the key instead. The trap is doing it with a nullable column: ``` UNIQUE (email, deleted_at) -- deleted_at NULL for live rows ``` In standard SQL, two rows whose keys contain NULL are *not considered duplicates*, so this constraint does nothing for live rows — you could insert a thousand live rows with the same email. That is a genuinely dangerous silent failure. (PostgreSQL 15 added `UNIQUE NULLS NOT DISTINCT` which fixes it, and some engines such as SQL Server treat NULLs as equal in a unique index, so the behaviour is engine-specific — never rely on it without checking.) The portable version makes the column `NOT NULL` with a sentinel meaning "alive": ``` deleted_at TIMESTAMPTZ NOT NULL DEFAULT TIMESTAMP '1970-01-01 00:00:00Z', UNIQUE (email, deleted_at) ``` Live rows all carry the epoch sentinel, so they collide with each other exactly as intended; deleted rows carry distinct real timestamps and drop out of the conflict. Residual risk: two rows deleted in the *same* transaction/instant can produce identical `(email, deleted_at)` pairs and fail. Teams work around this with a `deleted_seq`/`deleted_uuid` column in the key, or by using the row's own id as the tiebreaker. A variant many schemas use is a generated column: `alive_email` = `email` when live, `NULL` otherwise (in engines where a fully-NULL key is not indexed), with `UNIQUE (alive_email)`. ## Option 3 — do not keep dead rows in the live table The cleanest structural answer is to stop mixing states in one table. On delete, move the row to `users_deleted` (an archive table with no unique constraints) inside the same transaction, then hard delete it from `users`. Every constraint in the live table then means what it says, indexes stay small, and "restore" is a documented move-back path. A lighter variant is **anonymize on delete**: rewrite the unique column to a guaranteed-unique tombstone value, e.g. `email = 'deleted+42@invalid'` or the id-prefixed original. This releases the address immediately and doubles as a privacy measure — at the cost of losing the original value, which may be exactly what you wanted to keep. ## Choosing Ask what "unique" means to the business. If the address must be reusable after deletion, the partial index (or the archive) is right. If it must *never* be reused — the address is a permanent identity anchor, or reuse would let a new person read an old account's mail — then the original all-rows constraint is correct and the failing signup is a feature, not a bug; you handle it with a clear error, not a schema change. Getting that question answered first is what separates a fix from a guess.

  • Why is UNIQUE (email, deleted_at) with a nullable deleted_at usually wrong?
    Standard SQL considers rows whose unique key contains NULL to be distinct from each other, so live rows — which all have `deleted_at IS NULL` — are never compared and duplicates are accepted silently. The constraint appears to exist but enforces nothing on exactly the rows that matter. Making the column NOT NULL with a sentinel, or using an engine feature such as PostgreSQL's NULLS NOT DISTINCT, restores the intended behaviour.
  • Does a partial unique index have any downsides compared with a table constraint?
    In some engines it is an index rather than a declared constraint, so it cannot be referenced by a foreign key and may not be usable as an upsert conflict target that requires a constraint name. It also only helps queries that include the same predicate, so a query without `deleted_at IS NULL` cannot use it. Both are usually fine, because reads of a soft-deleted table should carry that predicate anyway.

saying these in an interview costs you the question

  • Claiming the existing UNIQUE constraint keeps working because deleted rows are "logically gone"
  • Adding UNIQUE (email, deleted_at) with a nullable column and believing live rows are still protected
  • Dropping the unique constraint entirely and enforcing uniqueness in application code with a SELECT-then-INSERT
  • Assuming partial indexes exist on every engine, notably MySQL
  • Never asking whether the business actually wants the identifier to be reusable after deletion

context

resolving links…