Identify One-to-One Relationships (Part 2)

A one-to-one (1:1) relationship exists when each instance of Entity A can relate to at most one instance of Entity B, and each instance of Entity B can relate to at most one instance of Entity A. This lesson focuses on recognizing a true 1:1 from business rules, spotting “false 1:1” patterns, and picking a sound physical design.

Translate natural-language rules into testable statements:

1. Name entities and verbs. “Each Employee is assigned one Office.”
2. Check uniqueness in both directions. Is it ≤1 on A→B and ≤1 on B→A?
3. Decide optionality (participation). 0..1 or 1..1 on each side? See mandatory vs. optional.
4. Consider time. “One at a time” across history is not 1:1; that’s typically 1:N with dates.
5. Stress test the rule. Will future policy or scale turn one side into many?

• ✅ Both directions unique (A→B ≤1, B→A ≤1).
• ✅ Optionality explicit and enforceable with NOT NULL/FK constraints.
• ✅ No hidden groups (e.g., “primary address” among many is still 1:N).
• ✅ Stability (growth won’t introduce multiples).
• ✅ Splitting serves security, sparsity, or lifecycle (otherwise keep one table).

• Capacity-limited exclusivity: An office “fits one person” today; policy allows sharing tomorrow ➜ model 1:N now.
• Time-sliced roles: “A company has one CEO at a time.” Use a 1:N RoleAssignment with effective dates and a unique “current” constraint.
• Primary-of-many: Multiple addresses with one primary flag ➜ 1:N + IsPrimary and a filtered unique index.

After confirming 1:1, place the foreign key based on participation and ownership:

• Mandatory on B, optional on A: Put the FK in B, mark it NOT NULL.
• Mandatory on both: Either side can host the FK; enforce creation order in a transaction.
• Optional on both: Place the FK on the smaller/colder table to reduce storage and I/O.

See integrity background in data integrity elements and constraint types.

Pattern A — Shared Primary Key (tight coupling) — ideal for sensitive/sparse columns split off the main entity.

CREATE TABLE A (
  A_ID INT PRIMARY KEY,
  -- base attributes
);

CREATE TABLE B (
  B_ID INT PRIMARY KEY,               -- equals A_ID
  -- sensitive/sparse attributes
  CONSTRAINT fk_b_a FOREIGN KEY (B_ID) REFERENCES A(A_ID)
);

Pattern B — Unique Foreign Key (looser coupling) — preserves independent PKs while enforcing 1:1.

CREATE TABLE A (
  A_ID INT PRIMARY KEY
);

CREATE TABLE B (
  B_ID INT PRIMARY KEY,
  A_ID INT UNIQUE,                    -- at most one B per A
  CONSTRAINT fk_b_a FOREIGN KEY (A_ID) REFERENCES A(A_ID)
);

Optionality & lifecycle: Add ON DELETE CASCADE or SET NULL to match retention rules.

-- Strict 1:1 with shared key
CREATE TABLE User (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL
);

CREATE TABLE UserProfile (
  user_id INT PRIMARY KEY,                   -- also FK to User(id)
  full_name VARCHAR(100),
  bio TEXT,
  profile_picture_url VARCHAR(255),
  CONSTRAINT fk_profile_user
    FOREIGN KEY (user_id) REFERENCES User(id)
    ON DELETE CASCADE
);

• Why shared key? Ensures 1:1 and simplifies joins; profile cannot exist without user.
• Alternative: Make UserProfile.user_id UNIQUE (not PK) if you need an independent UserProfile PK.

• Split (use 1:1) when columns are sensitive (access control), rarely accessed (row width), or have a different lifecycle (archival/purge cadence).
• Keep together when attributes are always queried together, share the same retention/audit policies, and do not bloat the base row.

Consider the rule “Employees are each assigned their own office.” That yields EMPLOYEE:OFFICE = 1:1 if both directions are unique and policy forbids sharing. If analysis of participation is inconclusive, either side may carry the foreign key; prefer the side that minimizes storage and reduces write amplification.

Stability check: Confirm with stakeholders that growth, space constraints, or new policies won’t introduce sharing. If they might, design it as 1:N now and avoid rework later.

In a registry of small-town airports: “The airport is located in exactly one town, and the town has exactly one airport.” This is a true 1:1 for that domain. Be sure it’s a business guarantee, not just an assumption that could change.

Before moving on to the next lesson, click the Exercise link below to check your understanding of one-to-one relationships.
one-to-one Relationships -Exercise
The next lesson defines one-to-many relationships.