SQL Reference - DEFERRABLE

DEFERRABLE

DEFERRABLE is a constraint attribute in SQL that specifies that the enforcement of the constraint can be postponed until the end of a transaction. This enables a transaction to insert data to a database even though it initially violates a constraint, provided that the violation is corrected before the transaction commits. When a constraint is declared as DEFERRABLE, a temporary violation is allowed in a specific scope of the transaction, known as the deferrable time.

Example

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    supervisor_ID INT REFERENCES Employees(ID) DEFERRABLE INITIALLY DEFERRED
);

INSERT INTO Employees (ID, supervisor_ID)
VALUES (1, 2), (2, 1);

Output

INSERT 0 2

Explanation

In the above code, a circular self-referential constraint is implemented on the Employees table, where supervisor_ID references the ID of the same table. The DEFERRABLE keyword makes it possible to delay the checking of the constraint until the end of the transaction. By further specifying INITIALLY DEFERRED, it enforces that the constraint checking will always be deferred till the transaction’s end. The data insertion doesn’t violate the foreign key constraint as the check is performed after both insertions are executed.

Example

CREATE TABLE test_table (
  person_id INT PRIMARY KEY,
  manager_id INT,
  CONSTRAINT fk_manager_id
    FOREIGN KEY (manager_id)
    REFERENCES test_table(person_id) DEFERRABLE INITIALLY DEFERRED
);

Output

The above SQL command will create a test_table if it doesn’t already exist.

Explanation

The keyword DEFERRABLE means that the integrity checks for a foreign key constraint can be deferred until the end of the transaction. INITIALLY DEFERRED means that in every transaction, the enforcement of the constraint is deferred by default.

This can be useful when the manager_id needs to reference a person_id that hasn’t been inserted yet. If the constraint was not deferrable, this process could fail due to the foreign key constraint. However, by making it deferrable, the check is delayed until the end of the transaction, allowing all the necessary inserts to happen first.