Table of Contents
Years ago I wrote this post describing how to implement 1-to-1 relationship in PostgreSQL. The trick was simple and obvious:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE UserProfiles ( UProfileID BIGSERIAL PRIMARY KEY, ... ); CREATE TABLE Users ( UID BIGSERIAL PRIMARY KEY, UProfileID int8 NOT NULL, ... UNIQUE(UProfileID), FOREIGN KEY(UProfileID) REFERENCES Users(UProfileID) ); |
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.
A lot of time is gone and now we can do this trick much simpler using modern features or PostgreSQL. Let's check
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
BEGIN; CREATE TABLE uProfiles ( uid int8 PRIMARY KEY, payload jsonb NOT NULL ); CREATE TABLE Users ( uid int8 PRIMARY KEY, uname text NOT NULL, FOREIGN KEY (uid) REFERENCES uProfiles (uid) ); ALTER TABLE uProfiles ADD FOREIGN KEY (uid) REFERENCES Users (uid); INSERT INTO Users VALUES (1, 'Pavlo Golub'); INSERT INTO uProfiles VALUES (1, '{}'); COMMIT; |
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:
|
1 2 3 |
SQL Error [23503]: ERROR: insert or update on table "users" violates foreign key constraint "users_uid_fkey" Detail: Key (uid)=(1) is not present in table "uprofiles". |
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!
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!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
BEGIN; CREATE TABLE uProfiles ( uid int8 NOT NULL PRIMARY KEY, payload jsonb NOT NULL ); CREATE TABLE Users ( uid int8 NOT NULL PRIMARY KEY, uname text NOT NULL ); ALTER TABLE Users ADD FOREIGN KEY (uid) REFERENCES uProfiles (uid) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE uProfiles ADD FOREIGN KEY (uid) REFERENCES Users (uid) DEFERRABLE INITIALLY DEFERRED; INSERT INTO Users VALUES (1, 'Pavlo Golub'); INSERT INTO uProfiles VALUES (1, '{}'); COMMIT; |
Neat! Works like a charm!
|
1 |
SELECT * FROM Users, uProfiles; |
|
1 2 3 |
uid|uname |uid|payload| ---|-----------|---|-------| 1|Pavlo Golub| 1|{} | |
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!
+43 (0) 2622 93022-0
office@cybertec.at
You need to load content from reCAPTCHA to submit the form. Please note that doing so will share data with third-party providers.
More InformationYou 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
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?
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.
... 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.
oh, really? Thanks for pointing this out. I will check
Thanks. Post updated. Have no idea what was wrong with me writing the post 😀