A table has a unique constraint on (list_id, position). A user reorders two items, so the application issues one UPDATE that exchanges their position values. Why can that statement fail, and what are the ways to make the reorder work?

## Why a valid final state still errors A unique constraint is backed by a unique index. When you update a row's indexed columns, the engine maintains the index as part of updating that row — it inserts the new key and marks the old one dead. It does not wait for the whole statement to finish and then re-check the table. So an UPDATE that exchanges two rows' `position` values has an intermediate moment: row A now holds position 2, row B still holds position 2. The index insert for the second entry collides and the statement aborts. The end state you asked for was perfectly valid; you were rejected for the path, not the destination. The SQL standard actually describes constraint checking as happening at the *end of the statement*, which would make the swap legal. Implementations differ in how close they get to that: some optimisers reorder or buffer index maintenance so certain shapes of the statement happen to succeed, which produces the worst kind of behaviour — a query that works in staging with three rows and fails in production with a different plan. Do not rely on it. The same problem appears in the shift form, which is more common than the swap: `UPDATE items SET position = position + 1 WHERE list_id = 7 AND position >= 3` collides with itself as soon as two rows are processed in ascending order. ## Option 1 — defer the unique check Declare the constraint `DEFERRABLE`, and in the reorder transaction switch it to deferred (or declare it `INITIALLY DEFERRED` if reorders dominate). The pending uniqueness checks are evaluated at COMMIT against the final state, so the intermediate duplicate never matters, and the single-statement swap works as written. Shifts work too. Two caveats. First, the usual deferral costs: a duplicate that survives to the end raises its error at COMMIT, not at the statement. Second, an engine-specific but important one — in PostgreSQL a deferrable unique constraint cannot be used to infer the conflict target of `INSERT ... ON CONFLICT`, so if any code path upserts on `(list_id, position)`, making that constraint deferrable will break it. ## Option 2 — a sentinel value Without deferral (MySQL, SQL Server), route around the collision with a value nobody else uses: 1. Move A to a position that cannot collide — a negative number, or an offset well beyond the list. 2. Move B into A's old position. 3. Move A into B's old position. Three statements in one transaction; every intermediate state satisfies the unique index. It generalises to a whole-list reshuffle: offset every affected row by a large constant, then write the final numbers. The cost is more round-trips and more index churn, and you need the sentinel range to be genuinely impossible for real data (a `CHECK (position >= 0)` and negatives as sentinels do not mix). ## Option 3 — delete and reinsert Inside one transaction, delete both rows and insert them with the swapped values. It works because the deletes clear the index entries first. It is heavier than it looks — foreign keys pointing at those rows may cascade, triggers fire, and identity/surrogate keys have to be preserved explicitly — so it is a fallback, not a first choice. ## Option 4 — stop making the order column unique The deeper question is whether `position` needs to be unique at all. A unique dense integer sequence is the most expensive possible representation of "order": every insertion in the middle renumbers the tail, and every reorder is a multi-row write under a constraint that fights you. The common alternative is a sparse or fractional ordering key: store positions as 1000, 2000, 3000 and insert between two neighbours at their midpoint; or use a lexicographic string key (the technique behind ranking schemes used by issue trackers) that always has room between any two values. Order the query with `ORDER BY position, id` for a deterministic tiebreak, index the column non-uniquely, and a reorder becomes a **single-row UPDATE** with no constraint interaction at all. Occasional rebalancing handles key exhaustion. That is usually the right production answer: the interviewer is often listening for whether you reach past the mechanical fix to the modelling decision that removed the problem. ## Summary of the tradeoff Deferral is the smallest change and keeps the invariant strict. Sentinels are portable and need no schema change but add statements. Fractional keys drop strict positional uniqueness — you trade an exact invariant for cheap, contention-free reordering, which for a user-facing list is almost always the better bargain.