1-to-1 relationship in PostgreSQL for real

Years ago

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

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.

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

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:

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!

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!

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!

SELECT * FROM Users, uProfiles;

uid|uname      |uid|payload|
---|-----------|---|-------|
  1|Pavlo Golub|  1|{}     |

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!