A table stores several rows per user but at most one of them may be flagged active at a time. How would you enforce that with a unique index carrying a WHERE predicate, and what are the limits of that approach?

## The problem "At most one X per Y" is a partial uniqueness rule: unique within a subset of rows, unconstrained outside it. A plain unique constraint cannot express it, because it applies to every row. Enforcing it in application code means a read-then-write, which two concurrent transactions can both pass, producing exactly the duplicate you forbade. ## The solution A **unique partial index** — a unique index whose definition carries a predicate — contains one entry per qualifying row and enforces uniqueness only among those entries. Rows outside the predicate are absent from the index, so they cannot collide with anything. This puts the invariant where it belongs: in the storage engine, checked as part of the write, under the same concurrency control as the data. Two concurrent inserts of an active row for the same user cannot both succeed; the second blocks on the index entry and then fails. ## Why the concurrency story works A unique index enforces its rule by refusing to hold two entries with the same key. When a transaction inserts an entry, a concurrent transaction inserting the same key waits until the first commits or rolls back, then either fails or proceeds. This is engine machinery, not application logic, so there is no window between check and write. Any application-level guard — select, then decide, then insert — has that window by construction unless it takes an explicit lock, which is more code and more contention for a weaker guarantee. ## Choosing the predicate shape Two common encodings of "active": - A boolean or status column: the predicate selects the active value. - A nullable timestamp such as an end-of-validity column: the predicate selects rows where it has no value, meaning the row is current. Either works. The important part is that the predicate be a simple, deterministic condition on stored columns — the engine must be able to decide membership from the row alone. ## Limits worth naming **One rule per index.** The index enforces exactly the subset it names. Additional rules — one primary address, one default payment method — each need their own index. That is fine, but each is another index to maintain. **Transactional swap.** Making a different row active usually means deactivating one and activating another. If the new activation is written before the old deactivation, the constraint sees two active rows at that instant. Whether that fails depends on the engine's checking model — some check immediately per statement, some can defer to commit if the constraint is declared deferrable, and index-backed rules are not always deferrable. The portable habit is to order the statements so the old row is cleared first within one transaction. **Hot-spot contention.** The index is small — one entry per user — which is exactly what makes it fast, but a workload that flips the active row constantly concentrates inserts and deletes into few pages, producing lock contention and page-level churn. **Not a foreign key or a check.** It says nothing about whether *at least* one row is active. "Exactly one" is a different, harder invariant that a partial unique index cannot express — it enforces the upper bound only. **Engine support varies.** Not every relational engine supports predicates on indexes, and the syntax differs where they do. Some engines instead require a computed column trick or a filtered unique constraint. The concept transfers; the spelling does not. ## Bonus: it is still an index Because it is a real index over active rows keyed by user, the planner can use it for lookups of a user's active row — a very common query. You get the constraint and the access path from one structure. Consider whether extra key columns would make it more useful for reads, remembering that adding columns to a unique index changes what is unique, so anything added must not weaken the rule. ## Alternatives and why they are worse - **Application check.** Racy, as described. - **Advisory or row lock on the user before writing.** Correct but hand-rolled, and it serialises unrelated work. - **A separate table holding the current row per user, keyed by user.** This is a legitimate design — uniqueness becomes an ordinary primary key — but it splits the data across two tables and adds joins. - **A trigger.** Correct only if it locks properly; it is more code doing what the index does natively. ## How to answer State the index, state why it is concurrency-safe (the check lives with the write), then volunteer the limits: one subset per index, the swap-ordering pitfall, contention on a hot subset, and that it bounds above but does not require at least one.