One-to-One Relationship

A one-to-one relationship means one row in table A maps to at most one row in table B, and vice versa.

Common examples: user-to-profile, employee-to-badge.

Core Concepts

erDiagram
  USER ||--|| USER_PROFILE : has

  USER {
    integer user_id PK
    text email
  }

  USER_PROFILE {
    integer profile_id PK
    integer user_id FK
    text bio
  }

Pattern	How it enforces one-to-one
FK + UNIQUE	Child table has user_id as UNIQUE foreign key
Shared PK	Child uses same PK value as parent

FK + UNIQUE

CREATE TABLE users_11 (
  user_id INTEGER PRIMARY KEY,
  email   TEXT NOT NULL UNIQUE
);

CREATE TABLE profiles_11 (
  profile_id INTEGER PRIMARY KEY,
  user_id    INTEGER NOT NULL UNIQUE,
  bio        TEXT,
  FOREIGN KEY (user_id) REFERENCES users_11(user_id)
);

Expected output
One-to-one structure created via unique child foreign key.

Shared PK

CREATE TABLE users_11_shared (
  user_id INTEGER PRIMARY KEY,
  email   TEXT NOT NULL UNIQUE
);

CREATE TABLE profiles_11_shared (
  user_id INTEGER PRIMARY KEY,
  bio     TEXT,
  FOREIGN KEY (user_id) REFERENCES users_11_shared(user_id)
);

Expected output
One-to-one structure created with shared primary key.

Code Examples

-- Insert parent then child profile.
INSERT INTO users_11 (user_id, email)
VALUES (1, 'ada@example.com');

INSERT INTO profiles_11 (user_id, bio)
VALUES (1, 'Mathematician and writer.');

Expected output
User and matching profile inserted successfully.

-- Read combined one-to-one data.
SELECT u.user_id, u.email, p.bio
FROM users_11 AS u
LEFT JOIN profiles_11 AS p
  ON u.user_id = p.user_id;

user_id	email	bio
1	ada@example.com	Mathematician and writer.

SQLite-Specific Nuances

SQLite Nuance

SQLite does not have a special "one-to-one" keyword.

You create one-to-one behavior by combining FOREIGN KEY with UNIQUE (or shared primary key design).

Common Pitfalls / Best Practices

Pitfall

Defining only a foreign key on the child table without UNIQUE, which silently becomes one-to-many.

Best Practice

When modeling one-to-one, verify it with a constraint check: child reference column must be unique.

Quick Challenge

Create a one-to-one pair employees and employee_badges where each employee can have only one badge.

View Solution

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL
);

CREATE TABLE employee_badges (
  badge_id     INTEGER PRIMARY KEY,
  employee_id  INTEGER NOT NULL UNIQUE,
  badge_code   TEXT NOT NULL,
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Expected output
Exactly one badge row per employee is enforced by UNIQUE(employee_id).