One-to-One Relationships

Objectives

By the end of this lesson, you should be able to:

• Explain what a one-to-one relationship is
• Implement one using a foreign key with a UNIQUE constraint
• Explain why a plain foreign key alone allows one-to-many, not one-to-one

💡 Why this matters: Some data belongs with a row but doesn’t need to live in the same table, an employee’s profile bio, a user’s account settings. A one-to-one relationship keeps that data separate while guaranteeing exactly one match per row.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the employees table from the previous lesson.

A Plain Foreign Key Isn’t One-to-One

The department_id foreign key from the previous lesson allows many employees rows to point at the same departments row, that’s exactly what made it one-to-many. Nothing about REFERENCES alone limits how many child rows can point at the same parent, a foreign key by itself never guarantees one-to-one.

Adding UNIQUE for One-to-One

CREATE TABLE employee_profiles (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL UNIQUE REFERENCES employees(id),
    bio TEXT,
    linkedin_url VARCHAR(200)
);

INSERT INTO employee_profiles (employee_id, bio, linkedin_url)
VALUES (1, 'Engineer at Example Co', 'linkedin.com/in/erincastillo');

employee_id INTEGER NOT NULL UNIQUE REFERENCES employees(id) combines two things from earlier modules: REFERENCES (this lesson’s foreign key) and UNIQUE (Module 3). The UNIQUE constraint is what actually makes this one-to-one, without it, this would just be a second one-to-many relationship in the other direction.

SELECT e.first_name, p.bio
FROM employees e
JOIN employee_profiles p ON e.id = p.employee_id;

 first_name |           bio
------------+-------------------------
 Erin       | Engineer at Example Co

(JOIN syntax is covered in full in the next module, this is a preview of how a one-to-one relationship is queried.)

UNIQUE Blocks the Second Match

INSERT INTO employee_profiles (employee_id, bio) VALUES (1, 'Duplicate');

ERROR:  duplicate key value violates unique constraint "employee_profiles_employee_id_key"

Erin (employee_id = 1) already has a profile, the UNIQUE constraint rejects a second one. This is the enforcement mechanism: without UNIQUE, both rows would insert successfully, quietly turning “one profile per employee” into “many profiles per employee,” the exact opposite of what a one-to-one relationship is supposed to guarantee.

Try It

1. Create a passports table with a citizen_id column that’s both UNIQUE and a foreign key referencing a citizens(id) table.
2. Insert one citizen and one matching passport.
3. Try inserting a second passport row for the same citizen_id, and read the resulting error.
4. Explain, in your own words, what would happen to this relationship’s meaning if the UNIQUE constraint were removed but REFERENCES were kept.

Recap

• A one-to-one relationship means each row on one side matches exactly one row on the other.
• REFERENCES alone only enforces that a value exists in the parent table, it doesn’t limit how many child rows can point at it.
• Adding UNIQUE to the foreign key column is what actually enforces one-to-one, at most one matching child row per parent.

Next lesson: many-to-many relationships, and why they need a table of their own to implement.