SQL INITIALLY

What does the INITIALLY keyword do in SQL constraint definitions?

INITIALLY sets the default time (DEFERRED or IMMEDIATE) at which a DEFERRABLE constraint is checked when a transaction begins.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL INITIALLY: Supports INITIALLY: PostgreSQL, Oracle, SQLite, IBM Db2, Standard SQL-compliant engines. Not supported: MySQL, MariaDB, SQL Server.

SQL INITIALLY Full Explanation

INITIALLY is used inside a table or constraint definition together with the DEFERRABLE clause. It tells the database engine whether a DEFERRABLE constraint should be evaluated right after each statement (INITIALLY IMMEDIATE) or postponed until the end of the transaction (INITIALLY DEFERRED) unless explicitly overridden with SET CONSTRAINTS. If the parent DEFERRABLE clause is omitted or NOT DEFERRABLE is specified, INITIALLY may not be used. The keyword affects FOREIGN KEY, UNIQUE, CHECK, and EXCLUDE constraints that support deferrability. Developers can later override the default timing for the current transaction by issuing SET CONSTRAINTS ALL IMMEDIATE or SET CONSTRAINTS constraint_name DEFERRED. INITIALLY does not influence performance beyond when validation occurs, but getting the timing wrong can surface constraint-violation errors earlier or later than intended.

SQL INITIALLY Syntax

constraint_definition
    [ DEFERRABLE | NOT DEFERRABLE ]
    [ INITIALLY { DEFERRED | IMMEDIATE } ]

SQL INITIALLY Parameters

Mode (string) - allowed values:
• DEFERRED - validate the constraint at COMMIT time
• IMMEDIATE - validate the constraint after each statement

Example Queries Using SQL INITIALLY

--Create a table with a foreign key that is checked at commit
CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INT    NOT NULL,
  CONSTRAINT fk_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
    DEFERRABLE INITIALLY DEFERRED
);

--Change the default timing of an existing constraint
ALTER TABLE orders
  ALTER CONSTRAINT fk_customer
  INITIALLY IMMEDIATE;

--Temporarily defer all deferrable constraints in a transaction
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO orders (customer_id) VALUES (9999); --allowed for now
COMMIT; --fails if customer 9999 does not exist

Expected Output Using SQL INITIALLY

In the first example, the fk_customer foreign key is checked at COMMIT, letting related rows be inserted or updated in any order within the same transaction
The ALTER statement switches the default to immediate checking
The SET CONSTRAINTS command overrides the default only for the current transaction

Use Cases with SQL INITIALLY

Loading related data where the insert order is unknown.
Bulk updates that temporarily break referential integrity.
Staging complex migrations that need intermediate invalid states.
Unit tests that need flexible row ordering without disabling constraints entirely.

Common Mistakes with SQL INITIALLY

Adding INITIALLY without DEFERRABLE produces a syntax error.
Assuming MySQL or SQL Server accept INITIALLY (they do not).
Forgetting to reset SET CONSTRAINTS, causing later statements to behave differently.
Believing INITIALLY changes runtime behavior permanently—SET CONSTRAINTS can override it per transaction.

First Introduced In

SQL-92 standard; implemented in PostgreSQL 7.3

Frequently Asked Questions

What is the difference between INITIALLY DEFERRED and INITIALLY IMMEDIATE?

INITIALLY DEFERRED waits until COMMIT to validate the constraint. INITIALLY IMMEDIATE validates right after each statement, catching violations sooner.

Can I switch a constraint from DEFERRED to IMMEDIATE later?

Yes. Use ALTER TABLE ... ALTER CONSTRAINT ... INITIALLY IMMEDIATE or INITIALLY DEFERRED. You can also override the timing inside a single transaction with SET CONSTRAINTS.

Is INITIALLY available in MySQL or SQL Server?

No. These systems do not implement DEFERRABLE constraints, so INITIALLY is unrecognized and will raise a syntax error.

Does INITIALLY affect performance?

Only indirectly. DEFERRED constraints postpone checks, which can make individual statements faster but commit slower if violations exist. IMMEDIATE constraints detect issues earlier but may slow each statement slightly.