Relational databases provide one very essential functionality which is key to integrity, data quality and consistency: foreign keys. If you want to build a professional application that relies on correct data, there is basically no way around the concept of referential integrity. The same is, of course, true in PostgreSQL.
Table of Contents
However, there is a corner case many people are not aware of: circular dependencies. Now, how can that ever happen? Consider the following example which has been tested in PostgreSQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE department ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL UNIQUE, leader bigint NOT NULL ); CREATE TABLE employee ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL, department bigint REFERENCES department NOT NULL ); ALTER TABLE department ADD FOREIGN KEY (leader) REFERENCES employee; |
In this case, we want to store departments and employees. Every department will need a leader, and every employee will need a department. We cannot have a department without a department leader - but we cannot have an employee without a department either.
The problem which arises is that we cannot insert into those two tables anymore without violating the foreign keys. The next listing shows what happens:
|
1 2 3 4 |
INSERT INTO department (name, leader) VALUES ('hans', 1); ERROR: insert or update on table "department" violates foreign key constraint "department_leader_fkey" DETAIL: Key (leader)=(1) is not present in table "employee". |
The problem is the same if we start to insert into the other table first - both operations will cause a similar error, so we are basically stuck with two tables into which we cannot insert any data.
The solution to the problem is to use “INITIALLY DEFERRED”. The idea is simple. Consider the following constraint instead of the one we created above:
|
1 2 3 |
ALTER TABLE department ADD FOREIGN KEY (leader) REFERENCES employee DEFERRABLE INITIALLY DEFERRED; |
The purpose of INITIALLY DEFERRED is to tell PostgreSQL to NOT perform the constraint check immediately while the write operation is happening, but to delay it until COMMIT. The advantage is that within the transaction, we can perform operations in any order and just make sure that all constraints are satisfied when COMMIT happens.
If we create the foreign key constraint om department like above, we can insert data nicely in a single transaction without violating any constraints at all. Here is how it works:
|
1 2 3 4 5 6 7 |
BEGIN; INSERT INTO department (name, leader) VALUES ('hq', 0) RETURNING id; INSERT INTO employee (name, department) VALUES ('hans', 1); UPDATE department SET leader = 1 WHERE id = 1; COMMIT; |
As you can see, this transaction works just fine and can be used to handle complex dependencies without having to worry about insertion order.
If you want to make sure that you get good performance when deleting data where foreign keys are involved, we recommend checking out Laurenz Albe’s blog post about indexing foreign keys.
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on X, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at
You are currently viewing a placeholder content from Facebook. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More InformationYou are currently viewing a placeholder content from X. To access the actual content, click the button below. Please note that doing so will share data with third-party providers.
More Information
Leave a Reply