One-to-One Relationships

LinkedIn Hook

"Your users table has 47 columns. Half of them are NULL for half the rows. You did not need a wider table — you needed a one-to-one relationship."

Most developers learn about one-to-many and many-to-many relationships on day one of database class, then quietly skip past one-to-one because it feels redundant. "If every user has exactly one profile, why not just put the profile columns in the users table?" That instinct is correct exactly often enough to be dangerous, and wrong exactly often enough to ruin a schema.

The honest answer is that one-to-one is a real, useful relationship — and the moment you need it, refusing to model it produces tables with dozens of mostly-NULL columns, hot rows that cannot fit on a single page, and migrations that lock the entire user table for hours because you added an instagram_handle column to a 200-million-row monster.

One-to-one shows up everywhere once you start looking for it: a users row paired with a user_profile row that holds the optional bio, avatar, and social links. An orders row paired with a shipping_label row that only exists once the order ships. A customer row paired with an enterprise_contract row that only the 3% of paying customers actually have. In each case, splitting the table is not a normalization tax — it is the right model.

There are two patterns to know. The FK plus UNIQUE pattern uses a foreign key with a uniqueness constraint to enforce "at most one." The shared primary key pattern reuses the parent's primary key as both the PK and the FK of the child, making the relationship physically impossible to violate. Pick the wrong one and you ship a schema that allows duplicates, orphans, or both.

In Lesson 8.1, I break down one-to-one relationships end to end: when to split a table versus keep it wide, the FK plus UNIQUE pattern, the shared PK pattern, optional one-to-one with NULLable references, and the real-world cases (user plus profile, order plus shipping label, account plus billing) where each pattern earns its keep.

Read the full lesson -> [link]

#SQL #Database #PostgreSQL #DataModeling #ERDiagram #BackendDevelopment #InterviewPrep

---

---

What You'll Learn

• What a one-to-one relationship is and how it differs from one-to-many and many-to-many
• When splitting a table improves the schema and when it just adds joins for no gain
• The FK plus UNIQUE pattern: how a foreign key with a uniqueness constraint enforces "at most one"
• The shared primary key pattern: reusing the parent PK as the child PK and FK in one column
• Mandatory versus optional one-to-one and how NULLable foreign keys model "may or may not exist"
• Real-world cases like user plus profile, order plus shipping label, and account plus enterprise contract
• How PostgreSQL and MySQL differ on deferrable constraints, ON DELETE CASCADE, and partial unique indexes
• How to read EXPLAIN to confirm the join is using the unique index and not a sequential scan

---

The Passport and Person Analogy — Why Some Things Belong on a Separate Page

Picture a government records office. Every citizen has exactly one passport, and every passport belongs to exactly one citizen. You could glue the passport data onto the citizen file — name, address, passport number, passport photo, expiry date, biometric chip ID — all in one folder. But the office quickly notices a few problems. Most citizens never apply for a passport, so the passport fields sit empty in their folder. The passport photo is large and the records clerk has to page past it every time she looks up an address. When the passport office issues new biometric chips, every single citizen folder needs to be touched, even the millions who do not have a passport at all.

So the office switches to two folders connected by a thin wire. The citizen folder holds identity data that everyone has. The passport folder holds passport-only data, and it has the citizen's ID number stamped on the front. There is a strict rule: a citizen ID can appear on at most one passport folder. That rule is the entire point of a one-to-one relationship — every citizen has zero or one passports, and every passport has exactly one citizen.

That is exactly what a one-to-one relationship does in SQL. The parent table holds the columns every row needs. The child table holds columns that only some rows need, or columns that are big, or columns that are touched by a different team or a different page of your app. The child table has a foreign key back to the parent, and a uniqueness constraint on that foreign key enforces the "at most one" rule. Without the uniqueness constraint, you have a one-to-many relationship by accident.

+---------------------------------------------------------------+
|           ONE-TO-ONE RELATIONSHIP                              |
+---------------------------------------------------------------+
|                                                                |
|        users                          user_profile            |
|  +-------------+               +-----------------------+      |
|  | id (PK)     | <-----------  | user_id (PK,FK,UNIQ)  |      |
|  | email       |       1:1     | bio                   |      |
|  | created_at  |               | avatar_url            |      |
|  +-------------+               | twitter_handle        |      |
|                                +-----------------------+      |
|                                                                |
|  Rule: every user_profile row points at exactly one user,     |
|        and no two profile rows share the same user_id.        |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           THE THREE CARDINALITIES SIDE BY SIDE                |
+---------------------------------------------------------------+
|                                                                |
|  ONE-TO-ONE   (1:1)   user  ->  profile                        |
|     -> child FK is UNIQUE                                      |
|                                                                |
|  ONE-TO-MANY  (1:N)   user  ->  posts                          |
|     -> child FK is NOT unique                                  |
|                                                                |
|  MANY-TO-MANY (M:N)   user  ->  user_roles  <-  roles          |
|     -> join table with composite PK on both FKs                |
|                                                                |
+---------------------------------------------------------------+

---

When to Split a Table — and When Not To

The first question is not "how do I model one-to-one" but "should I model it at all?" A one-to-one split is worth doing in four situations. Outside of these, leave the columns in the parent table.

1. Optionality. Most rows do not have the extra columns. If only 5% of users have an enterprise contract, do not put 12 contract columns on the users table where they are NULL for everyone else. Split them into an enterprise_contract table with a foreign key.

2. Width and hot rows. PostgreSQL stores rows in 8 KB pages. A row with 60 wide columns might not fit on one page and gets pushed into TOAST storage, slowing every read of that row even when you only need three columns. Splitting rarely-read columns (long bios, JSON blobs, large text) into a side table keeps the hot table small and dense.

3. Access patterns and security. If one team writes profile data and another team writes billing data, splitting them lets you grant table-level permissions, audit them separately, and run independent migrations. You can put users on the main schema and user_billing on a restricted schema only the payments service can touch.

4. Lifecycle differences. A user is created at signup. A shipping_label is created the moment an order ships. A passwordless_login_token lives for ten minutes and then disappears. When two pieces of data have wildly different lifetimes, modeling them as one row makes upserts ugly and TTL deletes impossible.

+---------------------------------------------------------------+
|           WHEN TO SPLIT vs KEEP IN ONE TABLE                  |
+---------------------------------------------------------------+
|                                                                |
|  SPLIT THE TABLE WHEN:                                         |
|   - The extra columns are NULL for most rows                   |
|   - The extra columns are large (text, JSON, blob)             |
|   - Access patterns differ (different teams, services)         |
|   - Lifecycle differs (parent forever, child temporary)        |
|   - Security needs separate grants or audit trails             |
|                                                                |
|  KEEP IT IN ONE TABLE WHEN:                                    |
|   - Every row has every column populated                       |
|   - The columns are read together on every query               |
|   - The split would force a join on every page load            |
|   - The total row width is small (few hundred bytes)           |
|                                                                |
|  RULE OF THUMB:                                                |
|   "Would I be happy with NULL here?" -> No: split.             |
|   "Will I always join to fetch this?" -> Yes: do not split.    |
|                                                                |
+---------------------------------------------------------------+

The trap is splitting reflexively in the name of normalization. If you end up writing JOIN user_profile USING (user_id) on every single query, you have just paid a performance and complexity cost for zero benefit. Split when there is a real reason — optionality, width, access, lifecycle — and not because a textbook said so.

---

Pattern 1 — Foreign Key Plus UNIQUE

The first pattern is the most common and the most flexible. The child table has its own primary key (usually a serial or UUID) and a foreign key back to the parent. A UNIQUE constraint on the foreign key column enforces the "at most one" rule.

-- Parent table: every user has these fields
CREATE TABLE users (
  id          BIGSERIAL PRIMARY KEY,
  email       TEXT NOT NULL UNIQUE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Child table: only some users have a profile, and at most one each
CREATE TABLE user_profile (
  id              BIGSERIAL PRIMARY KEY,
  -- Foreign key back to the parent. NOT NULL because every profile
  -- row must belong to a user. ON DELETE CASCADE means deleting a
  -- user wipes their profile automatically.
  user_id         BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  bio             TEXT,
  avatar_url      TEXT,
  twitter_handle  TEXT,
  -- The crucial line. Without UNIQUE, this is one-to-many by mistake.
  CONSTRAINT user_profile_user_id_unique UNIQUE (user_id)
);

-- Insert a user and a matching profile
INSERT INTO users (email) VALUES ('ada@example.com') RETURNING id;
-- Suppose the returned id is 1
INSERT INTO user_profile (user_id, bio, twitter_handle)
VALUES (1, 'Mathematician and programmer.', '@ada');

-- Try to insert a SECOND profile for the same user
INSERT INTO user_profile (user_id, bio) VALUES (1, 'Second profile?');

Sample output:

INSERT 0 1
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "user_profile_user_id_unique"
DETAIL:  Key (user_id)=(1) already exists.

The UNIQUE constraint did its job. The second insert is rejected at the database level, no matter what the application code tries to do. This is the entire point of putting the rule in SQL instead of in your service layer — a buggy worker, a race condition, or a manual psql session can never violate it.

Joining the two tables to fetch a user with their profile:

-- Read a user along with their profile if one exists
SELECT
  u.id,
  u.email,
  u.created_at,
  p.bio,
  p.avatar_url,
  p.twitter_handle
FROM users u
LEFT JOIN user_profile p ON p.user_id = u.id
WHERE u.id = 1;

Sample output:

 id |     email       |          created_at           |             bio              | avatar_url | twitter_handle
----+-----------------+-------------------------------+------------------------------+------------+----------------
  1 | ada@example.com | 2026-04-14 09:12:33.451+00    | Mathematician and programmer.| NULL       | @ada
(1 row)

The LEFT JOIN is important. A plain JOIN would silently drop users who have no profile yet — a one-to-one relationship is often optional, and a user without a profile is not a bug. Use LEFT JOIN whenever the child side is not guaranteed to exist.

Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Two side-by-side table cards in white monospace: 'users' on the left with rows id=1, id=2, id=3, and 'user_profile' on the right with rows pointing back via user_id. A glowing sky blue (#4fc3f7) line connects users.id=1 to user_profile.user_id=1 and another connects users.id=3 to user_profile.user_id=3, but users.id=2 has NO line going right. A rose (#ff5c8a) badge labeled 'UNIQUE(user_id)' floats above the right table. Bottom caption in white monospace: 'Optional 1:1 -- user 2 has no profile yet, and that is fine.'"

---

Pattern 2 — Shared Primary Key

The second pattern is more elegant and slightly stricter. Instead of giving the child table its own primary key, reuse the parent's primary key as both the PK and the FK of the child. There is exactly one column, it is the primary key (so unique by definition), and it is also a foreign key to the parent.

-- Parent stays the same
CREATE TABLE accounts (
  id          BIGSERIAL PRIMARY KEY,
  company     TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Child uses the parent's id as its own PK and FK in a single column
CREATE TABLE enterprise_contract (
  -- One column does the work of three: PK, UNIQUE, and FK
  account_id    BIGINT PRIMARY KEY
                REFERENCES accounts(id) ON DELETE CASCADE,
  contract_pdf  TEXT NOT NULL,
  signed_at     DATE NOT NULL,
  arr_usd       NUMERIC(12, 2) NOT NULL
);

-- Insert an account and its contract
INSERT INTO accounts (company) VALUES ('Acme Corp') RETURNING id;
-- Returned id = 1
INSERT INTO enterprise_contract (account_id, contract_pdf, signed_at, arr_usd)
VALUES (1, 's3://contracts/acme.pdf', '2026-03-01', 250000.00);

-- Attempting a second contract for account 1
INSERT INTO enterprise_contract (account_id, contract_pdf, signed_at, arr_usd)
VALUES (1, 's3://contracts/acme-v2.pdf', '2026-04-01', 300000.00);

Sample output:

INSERT 0 1
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "enterprise_contract_pkey"
DETAIL:  Key (account_id)=(1) already exists.

The shared PK pattern has three advantages over FK plus UNIQUE:

1. No accidental duplication. The PK constraint is automatic — you cannot forget to add UNIQUE.
2. One less column. No separate id serial column means smaller rows and a smaller index.
3. Trivial joins. The join column has the same name on both sides: JOIN ... USING (account_id).

Its downside is rigidity. The child cannot exist before the parent (it has nowhere to point), and it cannot be re-parented to a different account without deleting and reinserting. That is usually a feature, not a bug.

Joining with the shared PK pattern is delightfully clean:

-- Get every account that has signed a contract along with the ARR
SELECT
  a.id,
  a.company,
  c.signed_at,
  c.arr_usd
FROM accounts a
JOIN enterprise_contract c USING (id)
ORDER BY c.arr_usd DESC;

Wait — that does not quite work because the columns are named id in accounts and account_id in enterprise_contract. You either name them the same on both sides (rename account_id to id in the child) or use ON:

SELECT
  a.id,
  a.company,
  c.signed_at,
  c.arr_usd
FROM accounts a
JOIN enterprise_contract c ON c.account_id = a.id
ORDER BY c.arr_usd DESC;

Sample output:

 id |  company  | signed_at  |  arr_usd
----+-----------+------------+-----------
  1 | Acme Corp | 2026-03-01 | 250000.00
(1 row)

Napkin AI Visual Prompt: "Dark navy gradient (#0a0f1f -> #111a2e). Two table cards: 'accounts' on the left with id (PK in sky blue), company, created_at. 'enterprise_contract' on the right with account_id labeled with TWO badges — a sky blue 'PK' badge and a rose 'FK' badge stacked. A single thick sky blue arrow connects accounts.id directly to enterprise_contract.account_id. Title in white monospace at top: 'Shared Primary Key Pattern'. Caption at bottom: 'One column. PK + FK + UNIQUE for free.'"

---

Mandatory vs Optional One-to-One

A one-to-one relationship can be mandatory on either side, both, or neither. The difference is encoded in NOT NULL and in how you write the join.

+---------------------------------------------------------------+
|           CARDINALITY MATRIX                                   |
+---------------------------------------------------------------+
|                                                                |
|  PARENT side       CHILD side       Example                    |
|  -----------       ----------       -------                    |
|  optional   <- 1:1 -> mandatory     user -> profile (most apps)|
|     (user without profile is fine; profile must have a user)  |
|                                                                |
|  optional   <- 1:1 -> optional      order -> shipping_label    |
|     (label only exists once shipped; label needs an order)    |
|                                                                |
|  mandatory  <- 1:1 -> mandatory     person -> identity_card    |
|     (every person has one card; every card has one person)    |
|                                                                |
+---------------------------------------------------------------+

The most common case is optional parent, mandatory child: the user might not have a profile yet, but every profile row must belong to exactly one user. You enforce this with NOT NULL on the child's foreign key and LEFT JOIN from the parent when reading.

If the child side is also optional (the order has not shipped, so no label exists yet), nothing changes in the schema — LEFT JOIN handles it. The difference is in your application code, which must be ready for shipping_label.tracking_number to come back as NULL.

If both sides are mandatory in a strict sense — every parent must have a child — SQL cannot enforce that with a simple constraint, because you cannot insert the parent and the child in the same statement. You either accept a brief moment where the parent exists alone (and rely on application code to insert the child immediately), or you wrap both inserts in a transaction and use a DEFERRABLE constraint that is only checked at commit time. PostgreSQL supports deferrable constraints; MySQL does not.

-- Postgres-only: defer the FK check until COMMIT so a circular
-- mandatory 1:1 can be inserted in a single transaction.
CREATE TABLE person (
  id   BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  card_id BIGINT UNIQUE
);

CREATE TABLE identity_card (
  id        BIGSERIAL PRIMARY KEY,
  person_id BIGINT NOT NULL UNIQUE,
  number    TEXT NOT NULL
);

ALTER TABLE person
  ADD CONSTRAINT person_card_fk
  FOREIGN KEY (card_id) REFERENCES identity_card(id)
  DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE identity_card
  ADD CONSTRAINT card_person_fk
  FOREIGN KEY (person_id) REFERENCES person(id)
  DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  INSERT INTO person (name, card_id) VALUES ('Grace', NULL) RETURNING id;
  -- Suppose returned id = 1
  INSERT INTO identity_card (person_id, number) VALUES (1, 'X-998877') RETURNING id;
  -- Suppose returned id = 1
  UPDATE person SET card_id = 1 WHERE id = 1;
COMMIT;
-- Both rows now reference each other; constraints checked at COMMIT.

In practice, very few real schemas need this. Most one-to-one relationships are happy with optional-on-one-side and a plain LEFT JOIN.

---

Real-World Cases

User plus profile. The textbook example. The users table holds login fields (id, email, password_hash, created_at) used by every authentication request. The user_profile table holds display data (bio, avatar_url, social links) used only when rendering a profile page. Splitting them keeps the auth hot path narrow and lets the profile table grow new columns without touching the users table.

Order plus shipping label. The orders table is created when the user clicks Buy. The shipping_labels table is populated only after the warehouse generates a label. The two have different lifecycles and different writers (the checkout service vs the fulfillment service). Modeling shipping label as a separate one-to-one table lets fulfillment own its data.

Account plus enterprise contract. Most accounts are self-serve and have no contract. The 3% that are enterprise customers have a signed contract, an ARR figure, and a CSM assignment. Putting all of that on the accounts table means 97% of rows have NULL contract columns. Splitting it into enterprise_contract keeps the model clean and lets the sales team manage their data without grants on the accounts table.

Employee plus salary. Salary data is sensitive and should be readable only by HR and payroll. Splitting it into a separate employee_salary table lets you grant SELECT on employees to everyone and SELECT on employee_salary only to the payroll service account.

-- Real-world example: orders + shipping_label with shared PK
CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  total_cents BIGINT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending'
);

CREATE TABLE shipping_label (
  -- Same id as the parent order; PK enforces 1:1
  order_id        BIGINT PRIMARY KEY
                  REFERENCES orders(id) ON DELETE CASCADE,
  carrier         TEXT NOT NULL,
  tracking_number TEXT NOT NULL,
  shipped_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, total_cents) VALUES (42, 9900) RETURNING id;
-- Suppose returned id = 1

-- Order is created but not yet shipped; no label row exists.
SELECT o.id, o.status, l.tracking_number
FROM orders o
LEFT JOIN shipping_label l ON l.order_id = o.id
WHERE o.id = 1;

Sample output:

 id | status  | tracking_number
----+---------+-----------------
  1 | pending | NULL
(1 row)

-- Warehouse ships the order and creates the label
INSERT INTO shipping_label (order_id, carrier, tracking_number)
VALUES (1, 'UPS', '1Z999AA10123456784');
UPDATE orders SET status = 'shipped' WHERE id = 1;

SELECT o.id, o.status, l.carrier, l.tracking_number
FROM orders o
LEFT JOIN shipping_label l ON l.order_id = o.id
WHERE o.id = 1;

Sample output:

 id | status  | carrier |  tracking_number
----+---------+---------+--------------------
  1 | shipped | UPS     | 1Z999AA10123456784
(1 row)

---

PostgreSQL vs MySQL Notes

PostgreSQL and MySQL agree on the basics — both support FOREIGN KEY, UNIQUE, ON DELETE CASCADE, and PRIMARY KEY constraints. The differences are in the corners.

• Deferrable constraints. PostgreSQL supports DEFERRABLE INITIALLY DEFERRED so you can create circular 1:1 relationships in a single transaction. MySQL does not — you must either allow a NULL pointer briefly or use triggers.
• Partial unique indexes. PostgreSQL lets you write CREATE UNIQUE INDEX ON child (parent_id) WHERE deleted_at IS NULL, which is essential if you soft-delete profiles and want to allow re-creation. MySQL has no partial indexes (use functional indexes or generated columns as a workaround).
• ON DELETE CASCADE. Both support it. Both have a footgun: cascade on a one-to-one with a billion rows can lock for a long time. Prefer SET NULL or RESTRICT for tables you cannot afford to lock.
• Foreign key validation. MySQL's InnoDB checks foreign keys on every write. PostgreSQL does the same but lets you defer the check. If you batch-insert millions of rows in PostgreSQL, you can drop and recreate the FK to skip per-row checks.

---

Common Mistakes

1. Forgetting the UNIQUE constraint on the child's FK. You wrote user_id BIGINT NOT NULL REFERENCES users(id) and called it a one-to-one relationship. It is not — it is one-to-many, and nothing stops a buggy job from inserting two profile rows for the same user. The fix is one line: add UNIQUE (user_id) or use the shared PK pattern. Always test the constraint by attempting a duplicate insert on a fresh schema before declaring the model done.

2. Splitting tables that should not be split. Normalization purity is not a goal. If every user has every profile field populated and every read fetches both, splitting the table forces a join on every query and saves nothing. The split should pay for itself in clearer access patterns, narrower hot rows, or eliminated NULLs. If none of those apply, keep the columns together.

3. Using INNER JOIN when the child is optional. You wrote SELECT * FROM users JOIN user_profile USING (user_id) and now the user list silently excludes everyone who has not filled in their profile. Use LEFT JOIN whenever the relationship is optional on the child side, and remember to handle NULL columns in the application.

4. Picking serial PK for the child when shared PK would do. Adding a separate id BIGSERIAL column to a one-to-one child wastes 8 bytes per row, adds a second index, and makes the join syntax uglier. If the child cannot exist without its parent, use the parent's PK as the child's PK and FK in a single column.

5. Cascading deletes on a huge child without thinking about locks. ON DELETE CASCADE is convenient until you delete a user with 10 million child rows and the table is locked for ten minutes. Either delete the children explicitly in batches first, or use ON DELETE RESTRICT and force the application to clean up before deleting the parent.

---

Interview Questions

1. "What is a one-to-one relationship and how do you enforce it in SQL?"

A one-to-one relationship means each row in the parent table is associated with at most one row in the child table, and each child row belongs to exactly one parent. You enforce it in SQL with two constraints on the child: a FOREIGN KEY pointing at the parent so you cannot orphan a child, and a UNIQUE constraint on the foreign key column so you cannot have two children pointing at the same parent. The cleaner alternative is the shared primary key pattern, where the child's primary key column is also the foreign key — the PK constraint provides uniqueness automatically, no separate UNIQUE needed. Without one of these uniqueness mechanisms, you have a one-to-many relationship by accident, and your application code becomes the only line of defense against duplicates.

2. "When should you split a table into a one-to-one relationship instead of just adding columns?"

Split when the new columns are NULL for most rows (optionality), when they are large or rarely read (width and TOAST), when a different team or service writes them (access patterns), or when they have a different lifecycle than the parent (ephemeral data versus permanent). Do not split just because a textbook says normalize — splitting reflexively forces a join on every query and pays no benefit if every column is read together. The litmus test is: would you be unhappy seeing NULL in this column for most rows, and would the split eliminate either NULLs or hot-row width? If yes to either, split. If the columns are tightly coupled, small, and always read together, leave them in the parent.

3. "Compare the FK plus UNIQUE pattern with the shared primary key pattern. When would you use each?"

The FK plus UNIQUE pattern gives the child its own surrogate primary key (a serial or UUID) and adds a separate FK column with a UNIQUE constraint. It is more flexible: the child has its own identity, can be re-parented in theory, and can be referenced by other tables via its own ID. The shared PK pattern reuses the parent's primary key as the child's PK, FK, and uniqueness guarantee in a single column. It is more compact, makes the constraint impossible to forget, and produces cleaner join syntax. Use FK plus UNIQUE when the child needs its own ID for external references (other tables linking to it). Use shared PK when the child is a pure extension of the parent and only ever accessed via the parent's ID — most one-to-ones fall into this bucket.

4. "How do you handle a one-to-one relationship where the child might not exist yet, like a user profile that is filled in later?"

You make the relationship optional on the parent side and mandatory on the child side. The child table has NOT NULL on the foreign key, so you cannot insert a profile without a user. The parent does not need any special declaration — the absence of a profile row is the absence. When reading, you use LEFT JOIN from users to user_profile, and your application has to handle the case where the profile columns come back as NULL. This is the most common shape of a one-to-one in practice. It is also why you should not use INNER JOIN by default — that would silently drop every user without a profile from the result set, which is almost never what you want.

5. "What is a shared primary key in a one-to-one and what are its trade-offs versus a separate surrogate key?"

A shared primary key means the child table uses the same column as both its primary key and its foreign key to the parent. For example, enterprise_contract.account_id is declared as BIGINT PRIMARY KEY REFERENCES accounts(id). The PK constraint guarantees uniqueness, the FK constraint guarantees referential integrity, and there is no separate surrogate id column. The advantages are smaller rows, one fewer index, automatic uniqueness (no chance of forgetting UNIQUE), and conceptually clearer joins. The trade-offs are rigidity — the child cannot exist before its parent, cannot easily be re-parented, and cannot be referenced from other tables by its own ID since it has none. For pure extension tables that never live independently, shared PK is the right default. For child rows that need to be referenced by other tables or might migrate parents, FK plus UNIQUE wins.

---

Quick Reference — One-to-One Cheat Sheet

+---------------------------------------------------------------+
|           ONE-TO-ONE CHEAT SHEET                               |
+---------------------------------------------------------------+
|                                                                |
|  PATTERN 1 -- FK + UNIQUE                                      |
|  CREATE TABLE child (                                          |
|    id        BIGSERIAL PRIMARY KEY,                            |
|    parent_id BIGINT NOT NULL                                   |
|              REFERENCES parent(id) ON DELETE CASCADE,          |
|    UNIQUE (parent_id)                                          |
|  );                                                             |
|                                                                |
|  PATTERN 2 -- SHARED PRIMARY KEY                               |
|  CREATE TABLE child (                                          |
|    parent_id BIGINT PRIMARY KEY                                |
|              REFERENCES parent(id) ON DELETE CASCADE,          |
|    ...                                                          |
|  );                                                             |
|                                                                |
|  READING (optional child):                                     |
|    SELECT ... FROM parent LEFT JOIN child ON ...               |
|                                                                |
|  READING (mandatory child):                                    |
|    SELECT ... FROM parent JOIN child ON ...                    |
|                                                                |
+---------------------------------------------------------------+

+---------------------------------------------------------------+
|           DECISION TREE                                        |
+---------------------------------------------------------------+
|                                                                |
|  Need to model "at most one X per Y"?                          |
|     -> Yes: one-to-one                                         |
|                                                                |
|  Does the child need its own ID for other tables to reference? |
|     -> Yes: FK + UNIQUE                                        |
|     -> No:  SHARED PRIMARY KEY                                 |
|                                                                |
|  Is the child optional?                                        |
|     -> Yes: LEFT JOIN when reading                             |
|     -> No:  INNER JOIN (and NOT NULL on FK)                    |
|                                                                |
|  Are there many rows on the parent side?                       |
|     -> Yes: avoid ON DELETE CASCADE on huge child tables       |
|                                                                |
+---------------------------------------------------------------+

Concern	FK + UNIQUE	Shared PK
Uniqueness enforced by	UNIQUE constraint	PRIMARY KEY constraint
Extra columns	child has its own id	none
Child can be referenced by other tables	yes (via its own id)	only via parent_id
Risk of forgetting uniqueness	high (must remember UNIQUE)	impossible
Join syntax	ON child.parent_id = parent.id	same
Re-parenting	possible with UPDATE	requires delete + reinsert
Best for	child with its own identity	pure extension tables

Mistake	Symptom	Fix
Forgot UNIQUE on FK	duplicate child rows appear	add UNIQUE or switch to shared PK
Split table that should not be split	every query is a join	merge columns back into parent
INNER JOIN on optional child	rows silently disappear	switch to LEFT JOIN
Cascade delete on huge child	long lock during DELETE	batch delete first, then parent
Surrogate id when shared PK fits	wasted bytes and index	drop id column, promote FK to PK

---

Prev: Lesson 7.3 -- UUID Primary Keys Next: Lesson 8.2 -- One-to-Many Relationships

---

This is Lesson 8.1 of the Database Interview Prep Course -- 12 chapters, 58 lessons.