CYBERTEC Logo

1-to-1 relationship in PostgreSQL for real

05.2019 / Category: / Tags: |

Years ago

Years ago I wrote this post describing how to implement 1-to-1 relationship in PostgreSQL. The trick was simple and obvious:

You put a unique constraint on a referenced column and you're fine. But then one of the readers noticed, that this is the 1-to-(0..1) relationship, not a true 1-to-1. And he was absolutely correct.

Keep it simple stupid!

A lot of time is gone and now we can do this trick much simpler using modern features or PostgreSQL. Let's check

Things are obvious. We create two tables and reference each other using the same columns in both ways.
Moreover, in such model both our foreign keys are automatically indexed!
Seems legit, but executing this script will produce the error:

Oops. And that was the pitfall preventing the easy solutions years ago during my first post.

UPD: Andrew commented that DEFERRABLE was in PostgreSQL for ages. My bad. I got it mixed up. Thanks for pointing this out!

What about now?

But now we have DEFERRABLE constraints:

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. Note that deferrable constraints cannot be used as conflict arbitrators in an INSERT statement that includes an ON CONFLICT DO UPDATE clause.

So, the trick is we do not check data consistency till the end of the transaction. Let's try!

Neat! Works like a charm!

Conclusion

I am still eager to see the real-life situation where such a 1-to-1 model is necessary. From my perspective, this method may help in splitting wide tables into several narrow, where some columns are heavily read. If you have any other thoughts on your mind, shoot them up!

May ACID be with you!

0 0 votes
Article Rating
Subscribe
Notify of
guest
5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Achilleas Mantzios
Achilleas Mantzios
3 years ago

Thanx for the write-up Pavlo!

IMHO those should be the same table, unless we are talking about bytea or large text. BTW wouldn't a rule or a trigger be used to handle inserts/updates to either table?

Zsolt Juhasz
Zsolt Juhasz
5 years ago

1-to-1 model is frequently used when you want to keep core system tables separate from customization (Extension development model in modern ERP like Microsoft Business Central).

Customer table with fields for the core system (developed by Microsoft). If you make an extension for Customer related data, all you new fields will be populated into a "CustomerExt1" table with 1-to-1 relation to Customer core. The fields of a second extension developed by a third company will be populated into "CustomerExt2" table. Etc.

The result will be a database/development model, where every extension developer takes care only his own system (and the core system) without knowing anything about other extensions.
On an average installation the maintenance advantage (keep in mind, you have to have releases quite often) of such a model is bigger than the performance cost.

Andrew Gierth
Andrew Gierth
5 years ago

... new? modern?

DEFERRABLE foreign keys have been around for far longer than 16 years; I'd have to check the commit history (the release notes aren't specific enough) but it looks like they were added when foreign keys were, in 7.0.

Now, there actually is a "new"—as in only 7 or so years old—alternative approach, which is to use wCTEs to insert the two mutually-referencing rows in the same statement.

Pavlo Golub
Pavlo Golub
5 years ago
Reply to  Andrew Gierth

oh, really? Thanks for pointing this out. I will check

Pavlo Golub
Pavlo Golub
5 years ago
Reply to  Andrew Gierth

Thanks. Post updated. Have no idea what was wrong with me writing the post 😀

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
    5
    0
    Would love your thoughts, please comment.x
    ()
    x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram