You need to swap the display order of two items — one is position 1 and the other position 2, and they should trade places. There is a unique constraint on position.
Update the first to 2 and the constraint fires immediately: two rows now have position 2. It does not matter that the next statement fixes it. The check ran per statement, and the intermediate state was invalid.
What changed in 2026
- Awareness improved without adoption growing much. The mechanism stayed well-supported and relatively rarely used.
- ORM support remained inconsistent. Framework-level control over constraint timing continued to be uneven.
- The debugging cost got documented. Deferred errors reported at commit, far from the causing statement, became a recognised drawback.
- Alternative patterns spread. Sequence-gap approaches and temporary sentinel values remained common workarounds.
Three timing modes
| Mode |
Checked |
Use |
| Not deferrable |
After each statement |
Default; best error locality |
| Deferrable initially immediate |
After each statement, unless deferred |
Flexible; opt in per transaction |
| Deferrable initially deferred |
At commit |
When intermediate violations are routine |
The middle mode is the useful default when you need deferral at all. The constraint behaves normally — errors arrive at the statement that caused them — and a transaction that needs to pass through an invalid state can explicitly request deferral for its duration.
Declaring a constraint deferred by default makes every transaction pay the debugging cost, which is rarely justified.
Where deferral genuinely helps
Swapping unique values. The example above. With deferral, both updates run, the intermediate duplicate is tolerated, and the check at commit sees a valid state.
Circular foreign keys. Two tables referencing each other cannot both be inserted first. Deferring the foreign key checks lets both inserts happen and validates at commit.
Bulk reordering. Rewriting an entire ordered sequence passes through many transient duplicates.
Complex multi-table updates where consistency holds at the end but not at each step.
The unifying property: the final state is valid, the path there is not, and the intermediate state is never visible to anyone else because it exists only inside an uncommitted transaction.
That last point is what makes deferral safe rather than a loosening of guarantees. Other transactions never observe the invalid state — isolation already prevented that. Deferral simply stops the database from objecting to something nobody could see.
The debugging cost
The real drawback, and the reason not to defer by default.
With immediate checking, a violation is reported by the statement that caused it. You know exactly which operation was wrong.
With deferred checking, the error arrives at commit. The transaction may have executed dozens of statements, and the error says a constraint was violated without saying which statement did it. Attributing it means reasoning about the whole transaction.
In a long transaction with several deferred constraints, that becomes genuinely difficult — which is why "deferrable initially immediate" is the right shape: you get normal error locality, and the ability to defer where a specific transaction needs it.
Alternatives worth knowing
Deferral is not the only answer to the swap problem.
Use a temporary value. Move the first row to an unused position, move the second, then move the first into place. Three statements, no deferral, and it works everywhere.
Leave gaps in the ordering. Use positions 10, 20, 30 rather than 1, 2, 3, so reordering rarely needs a swap. Widely used and it degrades over time as gaps fill.
Use a fractional or sortable ordering key. Insert between two items without touching either. More sophisticated and avoids the problem structurally.
For circular foreign keys, the alternative is usually to reconsider the model — mutually required references frequently indicate the two tables should be one, or that one reference should be nullable.
Common mistakes
- Deferring by default. Loses error locality for every transaction.
- Long transactions with deferred constraints. Errors become hard to attribute.
- Assuming deferral relaxes isolation. It does not; the invalid state was never visible.
- Using deferral where a temporary value would do. More portable and clearer.
- Not checking ORM support. Frameworks may not expose the control.
- Circular foreign keys as a design. Usually a modelling problem.
FAQ
Does deferral weaken guarantees?
No. The constraint still holds for anything committed, and the intermediate state was never visible to other transactions. It changes when the check runs, not what is guaranteed.
Can I defer any constraint?
It must be declared deferrable when created. Changing an existing constraint means dropping and recreating it, which is a migration.
Do all databases support this?
Support and syntax vary; foreign keys are more widely deferrable than unique constraints. Check your engine.
Is this related to isolation levels?
Different mechanism. Isolation governs what transactions see of each other; deferral governs when a transaction's own constraints are checked — see database isolation levels.
Where to go next
For constraint types generally, read check constraints. For overlap rules, exclusion constraints, and for the isolation model, database isolation levels.