sql / expert
Snippet
Integrity Management via Constraint Deferrability
Deferrable constraints allow integrity checks to be postponed until the transaction is committed. This is essential for circular references or complex migrations where consistency is temporarily violated during the process.
snippet.sql
1
2
3
4
SET CONSTRAINTS ALL DEFERRED;UPDATE master_table SET id = 500 WHERE id = 100;UPDATE child_table SET master_id = 500 WHERE master_id = 100;COMMIT;
Breakdown
1
SET CONSTRAINTS ALL DEFERRED
Commands the engine to wait until the end of the transaction to validate foreign keys and unique constraints.
2
COMMIT
The point at which all deferred constraints are finally validated simultaneously.