DataslopeOne-to-One Relationships
When and how to split one entity across two tables while enforcing that each row can have at most one matching row.
Most relationships are not one-to-one. Customers have many orders; posts have many comments; students take many courses. But sometimes a single entity is best split across two tables while keeping a strict one-row-to-one-row connection.
That is a one-to-one relationship. It is rarer, but useful when a separate table makes the design clearer or safer.
Why split a one-to-one?
Imagine a users table that starts small, then grows a large set of
profile details. Some users have those details, many do not.
A separate table can help when:
- The extra attributes are optional or rarely used.
- The attributes represent a separate concern.
- The data is large, such as a biography or document blob.
- You want clearer ownership of which columns describe which idea.
The split should still describe one real-world thing. A user has at most one profile; a profile belongs to exactly one user.
The exact-one version
If both rows must exist, the ER shape is one-to-one on both sides.
The important database rule is not just the foreign key. It is the
foreign key plus a unique constraint. user_profiles.user_id
points to users.id, and UNIQUE says the same user id cannot appear
in two profile rows.
Optional one-to-one
Often the second row is optional: every profile must belong to a user, but not every user has filled out a profile yet.
The o| side means zero or one. This is a common way to model optional
details without filling the main table with null columns.
Quick check
What enforces that each user can have at most one profile row?
A foreign key from user_profiles.user_id to users.id by itself.
A foreign key plus UNIQUE (user_id) on the profile table.
A CHECK (display_name IS NOT NULL) constraint.
Putting profile_ids as a list on users.
Building a one-to-one
This example models optional user profiles. Ada and Grace are users; Ada has a profile, and Grace does not yet.
The LEFT JOIN keeps users who do not have a profile row. That matches
the optional one-to-one design: the main entity can exist before the
optional detail row exists.
Where should the foreign key go?
In a one-to-one relationship, either table could technically hold the foreign key. Choose the table whose row is dependent or optional.
For optional profiles, the profile table is dependent: a profile makes
no sense without a user, but a user can exist without a profile. That
is why user_profiles.user_id is the usual choice.
If the split is mandatory in both directions, the design can get more subtle because SQL constraints are checked row by row. In beginner schemas, start with the optional-or-dependent side unless you have a strong reason not to.
Check your understanding
Why are one-to-one relationships rarer than one-to-many relationships?
Because PostgreSQL does not support unique constraints.
Because most real-world relationships naturally involve multiple related rows, while strict one-to-one splits solve narrower design problems.
Because one-to-one relationships cannot be queried.
Because every one-to-one should be converted into many-to-many.
In users ||--o| user_profiles, what does the profile side mean?
Every user must have many profiles.
A user may have zero or one profile.
A profile may belong to many users.
Users and profiles cannot be joined.
Why might user_profiles be separated from users?
To make every query slower on purpose.
To keep optional or separate-concern profile details out of the core identity table.
To allow two profiles for the same user.
To avoid using primary keys.