A table keeps soft-deleted rows using a deleted_at timestamp, and the business rule is 'at most one live account per email address' while deleted rows must be retained. How do you enforce that rule in the database?

## Why the obvious constraint is wrong Soft deletion keeps a row physically present with a marker column. A `UNIQUE (email)` constraint has no idea about that marker: it enforces uniqueness across every stored row, live or not. The visible symptom is a support ticket — "I deleted my account and now I cannot sign up again" — and the usual bad fix is to move the check into application code, which then races under concurrency. The rule you actually want is conditional: *unique among rows satisfying a predicate*. Standard constraint syntax cannot express a predicate. This is the canonical reason to reach for a unique **index** instead of a unique **constraint**. ## The partial (filtered) unique index A partial index indexes only the rows matching a `WHERE` clause. Made unique, it enforces uniqueness exactly over that subset: ``` CREATE UNIQUE INDEX account_live_email_idx ON account (email) WHERE deleted_at IS NULL; ``` Properties: - **Correct semantics.** Two live rows with the same email collide; a live row and any number of deleted rows coexist. - **Smaller and cheaper.** Deleted rows are never inserted into the index. On a table where most rows are archived, the index can be a fraction of the full size, and writes that only touch archived rows skip it entirely. - **Usable for reads** — but only for queries whose predicate the optimizer can prove implies the index predicate. `WHERE email = ? AND deleted_at IS NULL` matches; `WHERE email = ?` alone generally does not, so a query that must search all rows needs its own index. This surprises people who assume the partial index covers every email lookup. ## The portable alternative If the engine has no partial indexes, encode the condition into a value and index that. A stored generated column works well: ``` ALTER TABLE account ADD COLUMN live_email text GENERATED ALWAYS AS (CASE WHEN deleted_at IS NULL THEN email END) STORED; ALTER TABLE account ADD CONSTRAINT account_live_email_key UNIQUE (live_email); ``` For live rows the column holds the email and collisions are caught; for deleted rows it is NULL, and because most engines treat NULLs as distinct for uniqueness, unlimited deleted rows are fine. Note the dependency on that NULL rule — on an engine that treats NULLs as equal for uniqueness, the trick collapses to "only one deleted row ever", and you need a different encoding (for example, folding the row's surrogate id into the value so archived rows are unique by construction). ## What this costs you elsewhere **Foreign keys.** A partial unique index cannot be a foreign-key target: it proves nothing about rows outside the predicate, and children may legitimately point at archived rows. The table therefore needs a surrogate key — an identity or UUID primary key — as the stable reference. This is not a workaround; it is the correct model, because a business identifier that can be recycled should never be a child's reference. **Error handling.** Violations surface as a duplicate-key error naming the *index*, not a friendly constraint name. Name the index deliberately (`account_live_email_idx`) so error-mapping code and logs stay readable, and so a reviewer does not mistake it for a stray performance index. **Undelete.** If rows can be restored, restoring is an insert into the partial index and can fail — someone else may have taken the address in the meantime. Restore paths must handle the duplicate-key error rather than assume success. **Migration onto a live table.** Building the index will fail outright if the current data already violates the rule, which on a soft-delete table it frequently does (several live rows sharing an address created while the rule lived only in application code). Deduplicate first, then build. ## When not to do this If the archived rows have no further business meaning, the cleaner model is to move them to a history table and put a plain `UNIQUE (email)` on the live table. That restores a normal constraint, restores foreign-key targetability, keeps the hot table small, and removes the `deleted_at IS NULL` predicate from every query — a predicate that, once forgotten anywhere, is a data-leak bug. The partial index is the right tool when archived rows genuinely must stay in the same table, not a default.