# When does a soft-delete flag prevent a key from ever being reused?

**Date of Record:** 2026 November 1

**Scope:** Applies to tables that retain deleted rows with a deleted_at or is_deleted marker while enforcing uniqueness on the natural key. Excludes tables that hard-delete or that scope uniqueness per tenant without a tombstone.

## Core Distinction

**Predicate.** A uniqueness constraint over key alone is compatible with soft delete only if deleted rows are excluded from the constraint. A unique index on key that includes deleted rows makes the key permanently consumed: recreation fails with a uniqueness violation the application interprets as a duplicate, even though the user can no longer see the original.

**Failure.** The user deletes an account, then re-registers with the same email. The insert fails. The application sees a uniqueness violation and reports "email already in use," which is true at the storage layer and false at the semantic layer.

**Asymmetry.** The deleted row is invisible to every read path, so no one can find the cause. Only a query that deliberately includes deleted rows reveals the blocking row, and support tooling rarely does.

## What Was Observed

**Signature.** Uniqueness violation on insert for a key that no visible row holds. Re-creation succeeds for keys that were never used, fails for keys that were once used, and the failure is permanent.

**Trace.** Unique index on email. Row (email=a@b, deleted_at=t1). Attempted insert (email=a@b) at t2 > t1 violates the index. Application catches the violation and returns a duplicate-email error. The user has no account and cannot create one, and no visible record exists to explain why.

**Why monitoring stays green.** The violation is a normal, expected outcome for the application, which correctly reports a duplicate. The error rate is indistinguishable from genuine duplicate registrations.

## Condition of Validity

Key reuse is possible only while at least one holds:

1. **Partial index.** The uniqueness constraint is partial over live rows only (for example, unique on key where deleted_at is null).
2. **Tombstone discriminator.** The key includes a discriminator that changes on delete, so the tombstoned row occupies a distinct key space.
3. **Purge.** Deleted rows are removed after a retention window, so the constraint eventually frees the key.

Condition 3 is a delay, not a solution, and it conflicts with any audit or legal-hold requirement.

## Decision Boundary

- Partial unique index over live rows: reuse works, history preserved.
- Unique over key including tombstones, no purge: key is permanently consumed. Recreation fails forever.
- Unique over key including tombstones, purge after N days: reuse works after N days, fails before. Must be documented to support.
- Key includes tenant plus tombstone discriminator: reuse works within a tenant without a partial index, but cross-tenant lookups need care.

## Operational Effect

The defect is a silent interaction between two features that are individually correct: soft delete preserves history, and a unique index prevents duplicates. Together they create a permanent key reservation that no one intended. Remediation is a partial index or a tombstone discriminator. The trap is to hard-delete instead, which frees the key and destroys the audit trail, and which usually surfaces as a compliance finding some time later.

```python
from dataclasses import dataclass


class KeyReuseBlockedError(RuntimeError):
    """Raised when a soft-deleted row permanently reserves a natural key."""


@dataclass(frozen=True)
class SoftDeleteGate:
    unique_includes_tombstones: bool
    partial_index: bool = False
    tombstone_discriminator: bool = False
    purge_days: int = 0

    def verify(self, audit_required: bool = True) -> bool:
        if self.partial_index or self.tombstone_discriminator:
            return True
        if self.unique_includes_tombstones and self.purge_days > 0 and not audit_required:
            return True
        if self.unique_includes_tombstones:
            raise KeyReuseBlockedError(
                "unique index covers deleted rows with no purge: key cannot be reused"
            )
        return True


def dispatch_delete_strategy(gate: SoftDeleteGate, audit_required: bool = True) -> str:
    try:
        gate.verify(audit_required)
    except KeyReuseBlockedError:
        return "partial-index"
    return "unique-with-purge" if gate.purge_days else "unique-live-only"
```