Constraints & Foreign KeysAdvanced
1Question 1 of 1
sqlHow do Foreign Key constraints with DEFERRABLE work in PostgreSQL?
Explanation & Concept
By default, foreign keys are checked immediately after each SQL statement runs. If an operation temporarily violates referential integrity during a multi-row insert or cyclic dependency, the statement immediately fails.
Declaring a constraint `DEFERRABLE INITIALLY DEFERRED` postpones the foreign key check until the very end of the transaction (`COMMIT`), enabling complex circular data loading.
Code Example
sql
CREATE TABLE nodes (
node_id INT PRIMARY KEY,
parent_id INT,
CONSTRAINT fk_parent
FOREIGN KEY (parent_id)
REFERENCES nodes(node_id)
DEFERRABLE INITIALLY DEFERRED
);
BEGIN;
-- Both nodes can reference each other before both rows are created
INSERT INTO nodes (node_id, parent_id) VALUES (1, 2);
INSERT INTO nodes (node_id, parent_id) VALUES (2, 1);
COMMIT; -- Foreign keys validated here; passes successfully!How It Works & Key Takeaways
Because foreign key validation is deferred until `COMMIT`, temporary circular relationships between node 1 and node 2 do not abort the transaction.
Mock Test · 1 Questions · Optional
Ready for EXPLAIN ANALYZE?
Take a quick test to evaluate yourself, or move straight to the next module.
Advanced1 questions