CYBERTEC PostgreSQL Logo

Foreign keys in PostgreSQL: Circular dependencies

06.2024 / Category: / Tags: |

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.

Foreign keys and circular dependencies

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:

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:

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.

INITIALLY DEFERRED coming to the rescue

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:

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:

As you can see, this transaction works just fine and can be used to handle complex dependencies without having to worry about insertion order.

Finally...

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 XFacebook, or LinkedIn.

Leave a Reply

Your email address will not be published. Required fields are marked *

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf
Austria

+43 (0) 2622 93022-0
office@cybertec.at

Get the newest PostgreSQL Info & Tools


    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    ©
    2024
    CYBERTEC PostgreSQL International GmbH
    phone-handsetmagnifiercrosscross-circle
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram