Deferrable Constraints in SQL

Jun 04, 2026

Database-level deferrable constraints let integrity checks move to the transaction boundary instead of requiring every check to pass right after a single statement runs. That changes how certain writes can be arranged because the rule still has to be true before the transaction finishes, but the database can allow a temporary mismatch while the transaction is still open. This helps with circular references, staged imports, and multi-step changes where rows depend on other rows that have not been written yet. Not every SQL engine supports deferrable constraints, and the supported constraint types vary by engine.

What Deferrable Constraints Mean

Constraint timing is the central idea behind a deferrable constraint. The rule does not become weaker, optional, or ignored. Instead, we can define the rule so its check waits until a later point in the same transaction. That later point is usually COMMIT, which is when the database decides if the full set of changes can become permanent.

Regular constraints usually reject a bad statement right away. Deferred constraints allow a transaction to pass through a temporary invalid state, then validate the finished result before the transaction closes. If the final data still breaks the rule, the database rejects the commit. If the final data satisfies the rule, the transaction can finish normally.

The Timing Choice

Most constraints are checked immediately, which means the database checks the rule as soon as the statement finishes. If an INSERT, UPDATE, or DELETE leaves a row in a state that breaks the constraint, the statement fails before the transaction can continue. That default behavior is useful because it keeps invalid data from moving past the statement that caused it. Deferrable constraints add a second timing option. We can still let the database check the rule immediately, or we can defer the check until the transaction reaches COMMIT. The data has to be valid by the end, but the database does not have to reject every temporary mismatch in the middle of the transaction.

PostgreSQL expresses this timing through DEFERRABLE, INITIALLY IMMEDIATE, INITIALLY DEFERRED, and NOT DEFERRABLE. Constraints marked DEFERRABLE can move between immediate checking and deferred checking inside a transaction. Constraints marked NOT DEFERRABLE cannot be moved to deferred mode. PostgreSQL treats IMMEDIATE constraints as statement-level checks and DEFERRED constraints as commit-time checks.

This definition creates a uniqueness rule that starts in immediate mode, while still allowing the transaction to defer it later:

CREATE TABLE warehouse_slot (
    slot_id integer PRIMARY KEY,
    slot_code text NOT NULL,
    CONSTRAINT warehouse_slot_code_unique
        UNIQUE (slot_code)
        DEFERRABLE INITIALLY IMMEDIATE
);

This constraint starts by checking slot_code after each statement. The word DEFERRABLE tells us the timing can change during a transaction, while INITIALLY IMMEDIATE tells us the default timing is still immediate. The uniqueness rule itself stays the same. Duplicate slot_code values still fail if the check runs before the transaction has been given deferred timing.

Inside a transaction, we can ask the database to defer that named constraint before we run a group of related changes:

BEGIN;

SET CONSTRAINTS warehouse_slot_code_unique DEFERRED;

UPDATE warehouse_slot
SET slot_code = 'TEMP-A'
WHERE slot_id = 1;

UPDATE warehouse_slot
SET slot_code = 'BIN-17'
WHERE slot_id = 2;

UPDATE warehouse_slot
SET slot_code = 'BIN-18'
WHERE slot_id = 1;

COMMIT;

We are not changing what UNIQUE means here. We are changing when the check happens. The transaction can pass through an intermediate state while we rearrange values, but the final slot_code values still have to be unique when COMMIT runs. If any duplicate remains, the transaction fails instead of becoming permanent.

INITIALLY DEFERRED changes the starting point. The constraint begins each transaction in deferred mode, so we do not need a separate SET CONSTRAINTS statement unless we want to switch the check back to immediate timing.

CREATE TABLE badge_assignment (
    badge_id integer PRIMARY KEY,
    badge_number text NOT NULL,
    CONSTRAINT badge_assignment_number_unique
        UNIQUE (badge_number)
        DEFERRABLE INITIALLY DEFERRED
);

With this definition, the database waits until commit time before checking the uniqueness rule unless the transaction asks for immediate timing. That can fit a group of related updates where duplicate values appear temporarily, then disappear before the transaction finishes.

The opposite choice is NOT DEFERRABLE. That setting locks the constraint into immediate checking.

CREATE TABLE storage_label (
    label_id integer PRIMARY KEY,
    label_text text NOT NULL,
    CONSTRAINT storage_label_text_unique
        UNIQUE (label_text)
        NOT DEFERRABLE
);

This constraint cannot be moved to deferred mode inside a transaction. If we try to defer it later, the database rejects that request because the constraint was not created with deferred timing available. The timing choice belongs in the constraint definition first, then transaction statements can use only the timing options that definition allows.

The Transaction Boundary

Commit time is where deferred validation finally has to pass. We can let the transaction hold unfinished changes for a short period, but the database checks the rule before those changes become permanent. The transaction has to end with valid data, or it does not commit.

Turning a constraint off is different from deferring it. Disabled rules stop protecting the data during the period when they are off, while deferred rules still protect the data because the final check happens before the transaction closes. The database is not accepting broken relationships or duplicate protected values permanently. It gives us room to complete a group of related writes before judging the finished result.

SQLite’s deferred foreign constraint behavior follows the same transaction-boundary idea. Deferred foreign constraints can be violated while the transaction is open, but COMMIT fails if the referenced row is still missing when the transaction tries to finish. SQLite also treats deferred constraints like immediate constraints when there is no explicit transaction, because an implicit transaction commits as soon as the statement finishes.

The next example keeps the focus on the boundary itself so we can see it a bit better. We insert a note that refers to a request id that does not exist, then try to commit without adding the missing request row:

PRAGMA foreign_keys = ON;

CREATE TABLE service_request (
    request_id integer PRIMARY KEY
);

CREATE TABLE service_note (
    note_id integer PRIMARY KEY,
    request_id integer NOT NULL,
    note_text text NOT NULL,
    FOREIGN KEY (request_id)
        REFERENCES service_request(request_id)
        DEFERRABLE INITIALLY DEFERRED
);

BEGIN;

INSERT INTO service_note (
    note_id,
    request_id,
    note_text
)
VALUES (
    1,
    500,
    'Waiting for approval'
);

COMMIT;

The insert can run because the foreign constraint is deferred. The commit fails because service_request still has no row with request_id 500. That is the core transaction-boundary rule. Temporary mismatch during the transaction is allowed, but invalid final data is rejected.

Transaction control matters because deferred timing needs an open transaction. If every statement commits right away, then there is no meaningful delay window. The database reaches the final validation point as soon as the statement ends, so deferred behavior has no room to help arrange related changes.

PostgreSQL also lets us check pending deferred changes before the transaction reaches COMMIT. If we switch a deferrable constraint from deferred mode back to immediate mode, the database checks any pending changes at that point. If those changes break the rule, the switch fails there instead of waiting for the final commit.

BEGIN;

SET CONSTRAINTS warehouse_slot_code_unique DEFERRED;

UPDATE warehouse_slot
SET slot_code = 'TEMP-A'
WHERE slot_id = 1;

SET CONSTRAINTS warehouse_slot_code_unique IMMEDIATE;

COMMIT;

When we ask for IMMEDIATE, we are asking the database to validate the pending changes right then. That can be useful when we want an earlier checkpoint inside a larger transaction, rather than waiting until the final COMMIT to learn that a deferred rule still fails.

The main idea is that deferrable constraints move validation from single-statement timing to transaction-level timing. We still have the same integrity rule, but we get a controlled window where temporary states can exist while the transaction is being completed.

How Deferred Checking Works in Practice

Deferred checking becomes useful when a transaction needs to build a valid final state in more than one write. The middle of the transaction can contain temporary gaps, but the final data still has to satisfy every deferred rule before the transaction can commit. This is why the feature fits certain cases very well, while other cases should stay with normal immediate checking.

The examples in this section keep the focus on practical transaction flow. We’re not changing the meaning of FOREIGN KEY, UNIQUE, or PRIMARY KEY. We’re only changing the point where the database checks the rule.

Circular References

Two tables can depend on each other in a way that makes the first insert hard to write. The first row needs a related row that does not exist yet, while the second row needs the first row to exist too. Immediate checking blocks that sequence because the database tries to validate the first reference before the related row has been added.

We can handle that by creating both foreign constraints as deferrable, then inserting both rows inside the same transaction. By the time COMMIT runs, both referenced rows exist, so the final state can pass validation.

The table definitions below use a project that points to its primary report, while the report also belongs to that project:

CREATE TABLE project_record (
    project_id integer PRIMARY KEY,
    project_name text NOT NULL,
    primary_report_id integer NOT NULL
);

CREATE TABLE report_record (
    report_id integer PRIMARY KEY,
    report_title text NOT NULL,
    project_id integer NOT NULL
);

ALTER TABLE project_record
ADD CONSTRAINT project_primary_report_fk
FOREIGN KEY (primary_report_id)
REFERENCES report_record(report_id)
DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE report_record
ADD CONSTRAINT report_project_fk
FOREIGN KEY (project_id)
REFERENCES project_record(project_id)
DEFERRABLE INITIALLY DEFERRED;

Both constraints are created after the tables exist because each table refers to the other. The important part is DEFERRABLE INITIALLY DEFERRED. That means each foreign constraint waits until commit time by default, giving the transaction time to add both rows before either relationship has to pass its final check.

Now we can insert the paired rows in a single transaction:

BEGIN;

INSERT INTO project_record (
    project_id,
    project_name,
    primary_report_id
)
VALUES (
    25,
    'Release Tracking',
    80
);

INSERT INTO report_record (
    report_id,
    report_title,
    project_id
)
VALUES (
    80,
    'Launch Readiness Report',
    25
);

COMMIT;

After the first insert, project_record.primary_report_id points to report 80, but that report has not been inserted yet. The deferred constraint lets the transaction continue. After the second insert, report 80 exists and points back to project 25. When COMMIT runs, the database checks both relationships against the finished data.

This technique should match a true data relationship, not cover up a schema that could be cleaner. Sometimes a nullable column or a separate linking table can represent the relationship with less friction. Still, when the final data really does require both rows to reference each other, deferred checking keeps the integrity rule in place while letting the transaction build both sides.

Staged Data Loads

Bulk imports and migration scripts sometimes receive data in an order that differs from the order required by immediate constraints. Child rows can arrive before parent rows, or related tables can be loaded from separate files. Deferred checking lets the load happen as a controlled transaction, then checks the completed data before anything becomes permanent.

This is useful when the import has all the needed rows, but they are not written in parent-first order. The database still rejects the transaction if a parent row is missing at the end, so the import does not get permission to leave broken references behind.

The following tables store shipments and shipment scans. A scan belongs to a shipment, but an import file may contain scan rows before the shipment row:

CREATE TABLE shipment_batch (
    shipment_id integer PRIMARY KEY,
    tracking_code text NOT NULL UNIQUE
);

CREATE TABLE shipment_scan (
    scan_id integer PRIMARY KEY,
    shipment_id integer NOT NULL,
    scan_status text NOT NULL,
    scanned_at timestamp NOT NULL,
    CONSTRAINT shipment_scan_batch_fk
        FOREIGN KEY (shipment_id)
        REFERENCES shipment_batch(shipment_id)
        DEFERRABLE INITIALLY DEFERRED
);

During a staged load, we can insert dependent rows first, then add the parent row before commit:

BEGIN;

INSERT INTO shipment_scan (
    scan_id,
    shipment_id,
    scan_status,
    scanned_at
)
VALUES
    (301, 7001, 'Packed', TIMESTAMP '2026-06-02 09:15:00'),
    (302, 7001, 'Loaded', TIMESTAMP '2026-06-02 10:05:00');

INSERT INTO shipment_batch (
    shipment_id,
    tracking_code
)
VALUES (
    7001,
    'EC-7001-WI'
);

COMMIT;

We are letting the transaction hold scans that temporarily reference shipment 7001 before the shipment row exists. By the end of the transaction, the missing parent has been inserted, so the deferred foreign constraint can pass. If the shipment row were never inserted, COMMIT would fail.

Deferred checking can also help with controlled value swaps under a deferrable UNIQUE constraint. A statement-by-statement swap may create a temporary duplicate value, even though the final values are unique. The table below makes the label position unique, but allows the check to wait:

CREATE TABLE display_panel (
    panel_id integer PRIMARY KEY,
    display_order integer NOT NULL,
    panel_title text NOT NULL,
    CONSTRAINT display_panel_order_unique
        UNIQUE (display_order)
        DEFERRABLE INITIALLY IMMEDIATE
);

For a batch update, we can defer the uniqueness check for the transaction, then finish with unique values:

BEGIN;

SET CONSTRAINTS display_panel_order_unique DEFERRED;

UPDATE display_panel
SET display_order = 2
WHERE panel_id = 10;

UPDATE display_panel
SET display_order = 1
WHERE panel_id = 11;

COMMIT;

The final state still has to contain unique display_order values. Deferral only changes the timing, which gives the transaction room to perform the swap without being stopped by a temporary duplicate in the middle.

Switching Check Timing

Some engines let a transaction change deferrable constraints between deferred mode and immediate mode. This gives us a way to delay checks during a group of related writes, then ask the database to validate that group before the final commit. In PostgreSQL and Oracle Database, SET CONSTRAINTS can set deferrable constraints to DEFERRED or IMMEDIATE for the current transaction.

That transaction-level control is useful when a longer transaction has natural checkpoints. We can defer the rule during one part of the change, then switch it back to immediate mode to catch any remaining violation before moving further.

The following sequence uses a deferrable foreign constraint named shipment_scan_batch_fk from the earlier staged load example:

BEGIN;

SET CONSTRAINTS shipment_scan_batch_fk DEFERRED;

INSERT INTO shipment_scan (
    scan_id,
    shipment_id,
    scan_status,
    scanned_at
)
VALUES (
    401,
    8100,
    'Received',
    TIMESTAMP '2026-06-02 11:30:00'
);

INSERT INTO shipment_batch (
    shipment_id,
    tracking_code
)
VALUES (
    8100,
    'EC-8100-WI'
);

SET CONSTRAINTS shipment_scan_batch_fk IMMEDIATE;

COMMIT;

When we run SET CONSTRAINTS shipment_scan_batch_fk IMMEDIATE, the database checks the pending foreign constraint at that point. If shipment 8100 were still missing, the switch back to immediate mode would fail before COMMIT. If the row exists, the transaction can continue with that relationship already validated.

PostgreSQL also allows SET CONSTRAINTS ALL DEFERRED, which changes all deferrable constraints in the transaction. That can be convenient during a migration, but naming the exact constraint is usually safer because it limits the timing change to the rule involved in the current set of writes.

BEGIN;

SET CONSTRAINTS ALL DEFERRED;

UPDATE display_panel
SET display_order = 3
WHERE panel_id = 12;

UPDATE display_panel
SET display_order = 4
WHERE panel_id = 13;

SET CONSTRAINTS ALL IMMEDIATE;

COMMIT;

This form can affect more constraints than intended if the transaction touches several tables. For that reason, named constraints usually make transaction behavior easier to read and review.

The timing switch does not make a nondeferrable constraint deferrable. If a constraint was created as NOT DEFERRABLE, SET CONSTRAINTS cannot move it into deferred mode. The transaction can only change timing for constraints that were defined with deferral available.

Engine Support

SQL engines do not all treat deferrable constraints the same way. The general concept is portable, but the syntax and supported constraint types depend on the database. That difference matters when writing schema code meant for a specific engine.

PostgreSQL supports the DEFERRABLE clause for UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES constraints. PostgreSQL does not support deferrable NOT NULL constraints or table CHECK constraints, and deferrable constraints cannot be used as conflict arbiters for INSERT statements that use ON CONFLICT.

Oracle Database supports deferrable constraints through the DEFERRABLE and INITIALLY clauses. The default is NOT DEFERRABLE, and a deferrable constraint can be checked after each DML statement or at commit time through SET CONSTRAINTS. Oracle also treats the deferability property as part of the constraint definition, so changing that property requires dropping and recreating the constraint.

SQLite supports deferred checking for foreign constraints. Foreign constraint enforcement must be active, and a foreign constraint can be declared with DEFERRABLE INITIALLY DEFERRED. SQLite also provides PRAGMA defer_foreign_keys, which can temporarily defer foreign constraint checks for a transaction.

MySQL needs a careful distinction. Common InnoDB foreign constraints are checked immediately, and MySQL documentation states that foreign checks are not deferred to transaction commit. For InnoDB, NO ACTION behaves like RESTRICT. The NDB storage engine supports deferred checks for NO ACTION, but that is not the usual behavior developers mean when talking about MySQL tables backed by InnoDB.

The practical takeaway is to write deferrable constraint examples for the engine being used. PostgreSQL syntax is not a promise that MySQL will behave the same way, and SQLite’s support is focused on foreign constraints rather than the wider set of constraint types available in PostgreSQL. Engine details should be checked before relying on commit-time validation in schema code.

Conclusion

Deferrable constraints keep the same integrity rules but change when the checks run. Instead of forcing every intermediate statement to be valid on its own, they let a transaction arrange related rows, value swaps, or staged loads before the final check at COMMIT. If the finished data still violates the rule, the transaction fails, but if the final state satisfies the rule, the database keeps the protection while allowing valid multi-step changes. The main mechanical detail is to define the constraint as deferrable first, then use transaction timing deliberately for the engines and constraint types that support it.

PostgreSQL SET CONSTRAINTS Documentation
PostgreSQL CREATE TABLE Constraint Documentation
SQLite Foreign Constraint Documentation
Oracle Database SET CONSTRAINTS Documentation
MySQL Foreign Constraint Documentation

Share Alexander Obregon's Substack

Alexander Obregon's Substack

Discussion about this post

Ready for more?