KAHIBARO
Discord Login Register ☰
☰

9.7. One-to-One Relationships

Table of Contents

Understanding One-to-One Relationships

One-to-one relationships are a specific way to connect data between two tables in a relational database. In this chapter, you will learn what they are, why they are useful, and how to design and query them with clear examples.

What Is a One-to-One Relationship?

A one-to-one relationship connects two tables so that each row in the first table is related to at most one row in the second table, and each row in the second table is related to at most one row in the first.

You can think of it as "exactly one or zero related row on each side".

Examples from real applications:

Table ATable BRelationship idea
usersuser_profilesEach user has at most one profile
personspassportsEach person has at most one passport
employeesemployee_salariesEach employee has at most one salary row
customerscustomer_settingsEach customer has at most one settings row

If a one-to-one relationship is mandatory, then each row in table A must have exactly one row in table B, and vice versa. If it is optional, then some rows in table A might not have a related row in table B.

Core definition
In a one-to-one relationship, if row $a$ from table A is related to row $b$ from table B, then:

  • $a$ is not related to any other row from B, and
  • $b$ is not related to any other row from A.

When Should You Use a One-to-One Relationship?

One-to-one relationships are less common than one-to-many, but they are very useful in some situations.

Separating Optional or Rare Data

Sometimes, most rows do not need certain columns. You can move these columns into a separate table.

Example:

Instead of having many NULL columns in users, you can:

This makes reads and writes on the users table smaller and can be better for performance.

Separating Sensitive Information

You might want to separate sensitive or restricted data from general data.

Example:

You can tighten permissions on employee_private_info so only some parts of your application or some database users can read it.

Splitting Very Large Tables

If one table is growing "wide" with many columns, some of which are rarely used, you can split it into two tables connected with a one-to-one relationship.

Example:

This can keep the main table smaller and faster to query.

Design Options for One-to-One Relationships

There are two main patterns to model a one-to-one relationship:

  1. Shared primary key pattern.
  2. Unique foreign key pattern.

You should understand both and know when to use each.

Pattern 1: Shared Primary Key

In the shared primary key pattern, the second table uses the same value as the primary key of the first table.

Example: users and user_profiles.

sql 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL
);
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),
    full_name TEXT,
    bio TEXT,
    date_of_birth DATE
);

Here:

Since user_id is primary key in user_profiles, there can be at most one profile row for each user.

This pattern is very strict. It makes the relationship very clear:

You can insert a user first, then optionally create a profile later.

Key points:

FeatureDescription
IdentityProfile uses the same ID as the user
Enforces max one profile per userYes, by primary key
Can a profile exist without a user?No, foreign key prevents it
Typical useStrong ownership, same lifecycle as parent

Pattern 2: Unique Foreign Key

In the unique foreign key pattern, the second table has its own primary key, but also has a foreign key that is marked as UNIQUE.

Example:

sql 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL
);
CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER UNIQUE REFERENCES users(id),
    full_name TEXT,
    bio TEXT,
    date_of_birth DATE
);

Here:

The UNIQUE constraint on user_id ensures that one user can have at most one profile row.

This pattern allows the child table to have its own identity (its own id). It can be useful if:

Key points:

FeatureDescription
IdentityChild table has its own primary key
Enforces max one profile per userYes, due to UNIQUE (user_id)
Can a profile exist without a user?No, foreign key prevents it
Typical useSlightly more flexible, extra relationships

Important rule
To enforce a one-to-one relationship:

  • Either make the foreign key column a PRIMARY KEY in the child table,
  • Or add a UNIQUE constraint on the foreign key column in the child table.
    If you forget the UNIQUE or PRIMARY KEY constraint, the relationship becomes one-to-many.

Choosing the Parent and Child Table

In a one-to-one relationship, you still think in terms of a parent and a child.

Usually:

Typical rules:

You choose which table is the parent based on the meaning in your application, not just on the database structure.

Examples of One-to-One Relationship Designs

Example 1: User and User Profile

This is a common design in web applications.

Shared primary key version:

sql 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL
);
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),
    full_name TEXT,
    bio TEXT,
    avatar_url TEXT
);

Usage:

  1. Create a user:
sql 
INSERT INTO users (email, password_hash)
VALUES ('alice@example.com', 'hash123')
RETURNING id;

Assume this returns id = 1.

  1. Later, create a profile for that user:
sql 
INSERT INTO user_profiles (user_id, full_name, bio, avatar_url)
VALUES (1, 'Alice Doe', 'Loves cats and coding', 'https://example.com/avatar1.png');

There can never be a second row in user_profiles with user_id = 1, because user_id is a primary key.

Example 2: Person and Passport

A real world example for strong one-to-one.

sql 
CREATE TABLE persons (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE passports (
    person_id INTEGER PRIMARY KEY REFERENCES persons(id),
    passport_number TEXT NOT NULL UNIQUE,
    country_code CHAR(2) NOT NULL
);

Here:

Enforcing Optional vs Mandatory One-to-One

A one-to-one relationship can be:

Optional One-to-One

This is the default in most designs. The foreign key in the child table can be NULL or missing row.

Example:

sql 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id),
    full_name TEXT
    -- no NOT NULL here makes the relationship optional in practice
);

Actually, the relationship is optional because you do not need to create a row in user_profiles at all. A user without a profile simply has no row in user_profiles.

Mandatory One-to-One

If you want to guarantee that every parent has a child row, the database cannot directly enforce this in a simple way across two tables. The foreign key ensures the child references a valid parent, but it does not ensure that each parent has a child.

Common approaches:

For beginners, it is usually enough to:

Querying One-to-One Relationships

A one-to-one relationship is queried with JOINs, similar to one-to-many relationships, but you expect at most one row on each side.

Basic JOIN

Example with users and user_profiles:

sql 
SELECT
    u.id,
    u.email,
    p.full_name,
    p.bio
FROM users AS u
LEFT JOIN user_profiles AS p
    ON p.user_id = u.id
WHERE u.id = 1;

Explanation:

If you only want users that have a profile, you can use INNER JOIN:

sql 
SELECT
    u.id,
    u.email,
    p.full_name
FROM users AS u
INNER JOIN user_profiles AS p
    ON p.user_id = u.id;

Checking Whether the Child Exists

You can check whether each user has a profile:

sql 
SELECT
    u.id,
    u.email,
    (p.user_id IS NOT NULL) AS has_profile
FROM users AS u
LEFT JOIN user_profiles AS p
    ON p.user_id = u.id;

Example result:

idemailhas_profile
1alice@example.comtrue
2bob@example.comfalse
3carol@example.comtrue

Inserting, Updating, and Deleting in One-to-One

Inserting Parent and Child

  1. Insert parent, get its ID.
  2. Insert child using that ID as the foreign key.

Example:

sql 
-- Insert parent
INSERT INTO users (email, password_hash)
VALUES ('bob@example.com', 'hash456')
RETURNING id;

Assume the returned id is 2.

sql 
-- Insert child
INSERT INTO user_profiles (user_id, full_name)
VALUES (2, 'Bob Smith');

Updating the Child

Updating the child is like updating any other table.

sql 
UPDATE user_profiles
SET bio = 'Backend developer and musician'
WHERE user_id = 2;

Deleting Parent or Child

Deleting the child is simple:

sql 
DELETE FROM user_profiles
WHERE user_id = 2;

Deleting the parent can cause referential integrity problems if the child still exists. The foreign key constraint controls what happens.

Common options:

Example with cascading delete:

sql 
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY
        REFERENCES users(id) ON DELETE CASCADE,
    full_name TEXT
);

Now:

sql 
DELETE FROM users WHERE id = 1;

This will automatically delete the user_profiles row where user_id = 1 if it exists.

Foreign key delete rule
If you delete a parent row that has a child row and there is no ON DELETE rule, the database will reject the delete to protect data integrity.
Common safe choice: ON DELETE CASCADE when the child data belongs strictly to the parent.

One-to-One vs One-to-Many: Design Considerations

Sometimes a relationship that looks one-to-one can become one-to-many in the future. It is important to think about this when designing your schema.

Example: Employee and Address

You might first think:

So you design:

sql 
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE employee_addresses (
    employee_id INTEGER PRIMARY KEY REFERENCES employees(id),
    address_line TEXT NOT NULL,
    city TEXT NOT NULL,
    country TEXT NOT NULL
);

Later, the business rules change:

Now your one-to-one is no longer correct. You need a one-to-many relationship.

New design:

sql 
CREATE TABLE employee_addresses (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES employees(id),
    address_type TEXT NOT NULL,
    address_line TEXT NOT NULL,
    city TEXT NOT NULL,
    country TEXT NOT NULL
);

There is no UNIQUE on employee_id, so each employee can have multiple rows in employee_addresses.

Lesson:

Performance Notes

One-to-one relationships have some performance characteristics you should know.

Table Width and Query Speed

Splitting frequently used and rarely used columns into separate tables can:

However, this benefit comes at the cost of extra JOINs when you do need the extra data.

Indexes

The primary key and unique constraints used in one-to-one designs automatically create indexes, which are helpful for lookups.

Typical useful indexes:

You usually do not need extra indexes just to support a one-to-one relationship.

Summary

In this chapter, you learned:

Understanding one-to-one relationships helps you design cleaner schemas and decide when to split data across multiple tables in your backend applications.

Views: 6

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 2994 / 24h
Impressum / Imprint
Datenschutz / Data Policy