
By Fanis Mahmalat · · Updated
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 checks each row the moment it changes — 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 lets the uniqueness check wait until COMMIT instead of running as each row changes:
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 "as each row changes" 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 each row as it changes, permanently — you cannot change this per-transaction.
DEFERRABLE INITIALLY IMMEDIATE checks at the end of each statement by default, which is later than a plain constraint, and 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.
A NOT DEFERRABLE unique constraint is checked as each row changes, so swapping two rules fails on the first update, and shifting every rule down can fail partway through a single statement. DEFERRABLE INITIALLY IMMEDIATE checks at the end of each statement, so the shift passes but the two-step swap still fails. DEFERRABLE INITIALLY DEFERRED checks at COMMIT, so the swap passes, and a duplicate that is still there at COMMIT fails the whole transaction.
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. On a plain unique constraint this is less clean than it looks: rows are updated one at a time in storage order, so the shift can hit a duplicate partway through and fail. It needs a DEFERRABLE constraint, and even INITIALLY IMMEDIATE is enough, because the check waits for the end of the statement. It also 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: deferrable uniqueness checks cost more than plain ones, and PostgreSQL's docs warn they can be significantly slower. 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.
Upserts: a deferrable constraint can't be the target of INSERT ... ON CONFLICT, so PostgreSQL rejects upserts that rely on it.
Thanks to Angelos Constantinides for the discussion that sharpened this one.
