The unique constraint trap in reordering logic (and how DEFERRABLE constraints fix it)
Say you have a rules table with a unique index on (shopId, priority), plus a reorder feature in the UI:
shopId | priority | name
101 | 1 | Rule A
101 | 2 | Rule B
101 | 3 | Rule C
Two requirements, both non-negotiable: (1) shopId + priority stays unique, and (2) users can reorder rules. Try to satisfy both with the obvious approach and you'll hit a wall fast.
Why the simple update fails
You want to swap Rule A and Rule B — Rule B moves to priority 1, Rule A moves to priority 2. Wrap it in a transaction and it still fails:
BEGIN;
UPDATE rules SET priority = 1 WHERE name = 'Rule B';
-- At this point, two rows have priority = 1
-- DB throws "Unique Constraint Violation"
UPDATE rules SET priority = 2 WHERE name = 'Rule A';
COMMIT;
A regular unique index validates after every individual statement — it cannot temporarily bypass the constraint, so the data has to be valid at every single step, not just at the end. The transaction wrapper doesn't help, because the transaction was never the problem; the ordering of statements inside it was.
The other obvious move — dropping the index and validating uniqueness in application code — trades this problem for a worse one. Now nothing stops two rows from actually ending up with the same priority if two requests race, or if someone touches the table directly. You've satisfied "reorder works" by giving up "uniqueness is guaranteed."
The fix: DEFERRABLE constraints
PostgreSQL lets you mark a constraint DEFERRABLE, which moves the uniqueness check to COMMIT time instead of after every statement:
ALTER TABLE public.rules
ADD CONSTRAINT "UQ_ShopId_Priority"
UNIQUE ("ShopId", "Priority")
DEFERRABLE INITIALLY DEFERRED;
With that in place, the exact same swap transaction just works:
BEGIN;
UPDATE rules SET priority = 1 WHERE name = 'Rule B';
-- DB does NOT throw "Unique Constraint Violation" — the constraint is deferred
UPDATE rules SET priority = 2 WHERE name = 'Rule A';
COMMIT;
-- Uniqueness is validated right here, against the final state
Nothing about the guarantee changed. The constraint still gets enforced, and the transaction still fails if the final state has a duplicate — you've only moved when it checks, from "after every statement" to "once, against the state that actually matters."
IMMEDIATE vs. DEFERRED
A deferrable constraint gives you two modes, and you can flip between them mid-transaction with SET CONSTRAINTS:
A plain UNIQUE index/constraint (no DEFERRABLE at all) checks after every statement, permanently — you cannot change this per-transaction.
DEFERRABLE INITIALLY IMMEDIATE checks after every statement by default, same as a plain constraint, but any transaction can flip it to DEFERRED on the fly with a command.
DEFERRABLE INITIALLY DEFERRED checks at COMMIT time by default, and any transaction can flip it back to IMMEDIATE the same way.
For a table whose writes are mostly ordinary single-row inserts and updates, INITIALLY IMMEDIATE is the safer default — you opt into deferred checking only for the transactions that need it. INITIALLY DEFERRED makes more sense when swaps and reorders are most of what happens to that table anyway.
Where this beats the usual workarounds
The two workarounds people reach for instead — and where they fall short, especially once an ORM like EF Core is in the picture:
The temp-value hack — move a row to a dummy value like
priority = -1to free up the slot, then move it again. It works, but it's 3 updates instead of 2, and now your application logic has to know about a hack that has nothing to do with the actual business rule.The single-statement shift — shove an entire range with one UPDATE, e.g.
SET priority = priority + 1 WHERE priority >= X. Clean when it applies, but it only works for continuous blocks — inserting into the middle of a list. An arbitrary swap between two non-adjacent rows isn't a range shift, and this approach doesn't cover it.
DEFERRABLE isn't a replacement for either workaround in every case — it's specifically better for the case both of them handle badly: an arbitrary multi-row swap that needs to pass through a temporarily invalid state on the way to a valid one.
Key takeaways
When to use it: complex, multi-statement transactions that need to pass through a temporarily invalid state — reorders, swaps, anything in that shape.
Performance: a slight overhead at COMMIT time for the deferred check. Worth weighing on a high-throughput table — this is a tool for specific swap/reorder cases, not a default for every unique constraint.
Data integrity: unchanged. The constraint still guarantees no duplicates exist once the transaction finishes — you've only changed when it's checked, not whether.
Compatibility: standard SQL, but support is split — Postgres and Oracle have it; MySQL and SQL Server don't.
Thanks to Angelos Constantinides for the discussion that sharpened this one.