One-to-Many Relationships
FK on the Many Side, Cascades, and Indexing
LinkedIn Hook
"A user has many orders. An order belongs to one user. Where does the foreign key go?"
If you hesitated for even half a second, you are in good company. One-to-many is the most common relationship in every application database on Earth — users to orders, posts to comments, customers to invoices, companies to employees — and it is also the one that beginners get backwards most often. They put the foreign key on the "one" side, end up with a column that holds a list of IDs as a comma-separated string, and an entire afternoon disappears trying to JOIN against
'17,42,88'.The rule is brutally simple and it never changes: the foreign key lives on the many side, pointing back to the one side. Orders carry
user_id. Comments carrypost_id. Invoices carrycustomer_id. The "one" table never knows about its children — it does not store a list, it does not need a column, it does not change shape when a new child appears. The relationship is fully owned by the child row, which holds a single integer pointing at its parent.Get this wrong and you fight the database every single day. Get it right and JOINs are free, indexes work, ON DELETE CASCADE does what you expect, and your schema scales from 100 users to 100 million without changing shape.
In Lesson 8.2, I break down one-to-many end to end: where the FK goes and why, ON DELETE behaviors (CASCADE, SET NULL, RESTRICT, NO ACTION), why you must index every foreign key column, the users -> orders example end to end, and the differences between PostgreSQL and MySQL.
Read the full lesson -> [link]
#SQL #Database #PostgreSQL #MySQL #DataModeling #BackendDevelopment #InterviewPrep
What You'll Learn
- Why the foreign key always lives on the "many" side of a one-to-many relationship
- How to model users -> orders end to end with PRIMARY KEY, FOREIGN KEY, and NOT NULL constraints
- The four ON DELETE behaviors — CASCADE, SET NULL, RESTRICT, NO ACTION — and when to use each
- ON UPDATE behavior and why it matters less than ON DELETE in practice
- Why every foreign key column must be indexed, and what happens if you forget
- How the parent side queries its children with a simple JOIN, no list column required
- PostgreSQL vs MySQL differences in deferred constraints, storage engines, and FK enforcement
- How to spot a "many" relationship in the wild and translate it into a schema in 30 seconds
The Parent and Children Analogy — One Parent, Many Kids, One Last Name
Picture a family. A mother has three children. Each child has exactly one biological mother. If you had to write this on paper, where would you record the relationship?
You could try writing it on the mother's record: "Mother: Sarah. Children: Alex, Beth, Carl." This works for one family, but the moment Sarah has a fourth child you have to rewrite the line. If Carl moves out you have to delete him from the middle of a string. If you want to ask "who is Beth's mother?" you have to scan every mother's record looking for the name "Beth" inside a list. The data is technically there, but it is structured wrong for the questions you actually need to ask.
Now flip it. Write the mother's name on each child's record instead: "Alex, mother = Sarah. Beth, mother = Sarah. Carl, mother = Sarah." Sarah's own record never changes when a new child is born — you just create a new child record that points back at her. To find all of Sarah's children, scan the children for mother = Sarah. To find Beth's mother, read one field on Beth's record. Every question is one lookup, and the mother's record stays stable forever.
That is exactly the rule for one-to-many in SQL. The "many" side carries a single column — the foreign key — pointing at the primary key of the "one" side. The "one" side has no idea its children exist; it does not need a column, a list, or any change of shape. Every parent-child relationship in every database on Earth follows this same pattern, whether you call them users and orders, posts and comments, or customers and invoices.
+-----------------------------------------------------------------+
| ONE-TO-MANY -- THE FK LIVES ON THE MANY SIDE |
+-----------------------------------------------------------------+
| |
| users (the ONE side) orders (the MANY side) |
| +----+---------+ +----+---------+----------+ |
| | id | email | | id | user_id | total | |
| +----+---------+ +----+---------+----------+ |
| | 1 | a@x.io | <----------- | 10 | 1 | 49.00 | |
| | 2 | b@x.io | <-+ <------- | 11 | 1 | 12.50 | |
| | 3 | c@x.io | | <------- | 12 | 1 | 99.00 | |
| +----+---------+ +--------- | 13 | 2 | 7.25 | |
| +----+---------+----------+ |
| ^ |
| | |
| FOREIGN KEY -> users.id |
| |
| The users table NEVER stores a list of orders. |
| The orders table holds ONE user_id per row. |
| This shape never changes as orders grow from 10 to 10M. |
| |
+-----------------------------------------------------------------+Napkin AI Visual Prompt: "Dark gradient (#0a0f1f -> #111a2e). Two side-by-side tables: LEFT shows a small 'users' table with id and email columns and 3 rows highlighted in sky blue (#4fc3f7). RIGHT shows a taller 'orders' table with id, user_id, total columns and 8 rows. Rose (#ff5c8a) arrows flow from the user_id column of multiple orders rows back into the matching id row in users. A large rose label 'FK' sits over the user_id column. White monospace labels '1' over users and 'N' over orders. Subtle grid overlay. No emojis."
The Rule — Foreign Key on the Many Side, Always
The rule has no exceptions in a clean schema. Whenever you see a "has many" relationship, the table on the "many" end carries a column that holds the primary key of the table on the "one" end. That column is called a foreign key (FK), and it is constrained to either be NULL (if the relationship is optional) or to match an existing row in the parent table (if the relationship is required).
| Relationship in plain English | One side | Many side | FK column lives on |
|---|---|---|---|
| A user has many orders | users | orders | orders.user_id |
| A blog post has many comments | posts | comments | comments.post_id |
| A customer has many invoices | customers | invoices | invoices.customer_id |
| A company employs many people | companies | employees | employees.company_id |
| A category contains many products | categories | products | products.category_id |
| A thread holds many messages | threads | messages | messages.thread_id |
The mental shortcut: read the sentence "X has many Y" and the FK lives on Y, pointing at X. Every single time. If you ever feel tempted to put an array or comma-separated list on the "one" side, you are about to introduce a bug that will follow you for years.
Example 1 — Users and Orders End to End
Let us build the canonical one-to-many schema and watch every part work.
-- PostgreSQL: the canonical users -> orders schema
-- The users table is the ONE side. It has no idea about orders.
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- The orders table is the MANY side. It carries user_id.
-- NOT NULL means every order MUST belong to a user (required relationship).
-- REFERENCES makes the database enforce that user_id matches a real users.id.
-- ON DELETE CASCADE means deleting a user also deletes their orders.
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL
REFERENCES users(id) ON DELETE CASCADE,
total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
status TEXT NOT NULL DEFAULT 'pending',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- CRITICAL: index the foreign key column.
-- Postgres does NOT auto-index FKs. Without this, every JOIN from
-- users to orders does a full sequential scan of orders.
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Seed a couple of users and a handful of orders.
INSERT INTO users (email) VALUES
('alice@example.com'),
('bob@example.com'),
('carol@example.com');
INSERT INTO orders (user_id, total_cents, status) VALUES
(1, 4900, 'paid'),
(1, 1250, 'paid'),
(1, 9900, 'pending'),
(2, 725, 'paid');
-- carol (id=3) has no orders yet, and that is perfectly fine.Now we can ask every question that a one-to-many shape supports, with no list columns and no exotic syntax.
-- Q1: All orders belonging to alice (id=1)
SELECT id, total_cents, status
FROM orders
WHERE user_id = 1
ORDER BY id; id | total_cents | status
----+-------------+---------
1 | 4900 | paid
2 | 1250 | paid
3 | 9900 | pending
(3 rows)-- Q2: Every user with the count of orders they have placed.
-- LEFT JOIN ensures users with zero orders still appear (carol).
SELECT u.email,
COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
ORDER BY u.id; email | order_count
-------------------+-------------
alice@example.com | 3
bob@example.com | 1
carol@example.com | 0
(3 rows)-- Q3: Watch ON DELETE CASCADE in action.
DELETE FROM users WHERE id = 1;
SELECT COUNT(*) AS remaining_orders FROM orders; remaining_orders
------------------
1
(1 row)Deleting alice removed her three orders automatically. Bob's single order remains. The database enforced the integrity rule for us — there is no way to leave an order behind that points at a deleted user.
ON DELETE Behaviors — The Four Choices
When you declare a foreign key, you also tell the database what should happen to the child rows when the parent row is deleted. There are four standard options, and picking the right one is a real design decision.
+-----------------------------------------------------------------+
| ON DELETE BEHAVIORS |
+-----------------------------------------------------------------+
| |
| CASCADE Delete the parent -> delete every child row too. |
| Use when children cannot exist without the parent.|
| Example: a user and their session tokens. |
| |
| SET NULL Delete the parent -> set child.fk = NULL. |
| Use when children outlive the parent and the |
| link is optional. Requires the FK column to be |
| nullable. Example: a deleted author on a post. |
| |
| RESTRICT Block the DELETE if any child rows exist. |
| Checked immediately. Use when you want a hard |
| guard against orphaning data by mistake. |
| |
| NO ACTION Block the DELETE if any child rows exist. |
| Same as RESTRICT, but checked at the END of the |
| statement (or end of transaction if DEFERRABLE). |
| This is the SQL default if you specify nothing. |
| |
+-----------------------------------------------------------------+-- Example: each behavior on a different child table
-- Sessions die with the user -> CASCADE
CREATE TABLE sessions (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
token TEXT NOT NULL
);
-- Posts outlive the user; if author is deleted, mark as anonymous -> SET NULL
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
author_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
body TEXT NOT NULL
);
-- Invoices must NEVER orphan; block the user delete if any exist -> RESTRICT
CREATE TABLE invoices (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
amount INTEGER NOT NULL
);The default in SQL is NO ACTION, which behaves like RESTRICT for almost every workload. Most teams prefer to write the behavior explicitly anyway — future readers of the schema should never have to guess what happens on delete.
A note on ON UPDATE: the same four options exist for ON UPDATE, fired when the parent's primary key changes. In practice, you should never change a primary key, so ON UPDATE rules rarely fire. They exist for legacy schemas that use natural keys (like a country code) which can change over time. With surrogate integer keys (the modern default), you can leave ON UPDATE unspecified and it will not matter.
Why You Must Index Every Foreign Key Column
This is the silent killer of slow databases, and it deserves its own section because PostgreSQL does not create the index for you automatically.
When you declare user_id BIGINT REFERENCES users(id), Postgres creates a constraint that enforces referential integrity, but it does not create an index on user_id. The two are different objects with different jobs. The constraint says "every value here must exist in users.id." The index says "I can find rows by user_id quickly." You need both.
What goes wrong without the index:
-
Every JOIN slows down. A query like
SELECT * FROM users u JOIN orders o ON o.user_id = u.id WHERE u.id = 42has to scan the entireorderstable to find the rows whereuser_id = 42. With 10 million orders, that is 10 million row reads to return three rows. -
DELETEs on the parent become catastrophic. When you delete a user, Postgres must check whether any orders still reference that user (or, with CASCADE, find all of them and delete them). Without an index on
orders.user_id, this check is a full table scan ofordersfor every parent row deleted. Deleting 100 users from a 10M-row child table without the FK index is a 1-billion-row operation. -
Lock contention explodes. During the unindexed CASCADE delete, Postgres holds locks on every row it scans. Other writers queue up behind it. A "quick" admin cleanup turns into a multi-minute outage.
-- ALWAYS pair the FK with its index
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_invoices_user_id ON invoices(user_id);MySQL difference: InnoDB does automatically create an index on every foreign key column when the constraint is declared. This is a meaningful divergence — you can write the same REFERENCES clause in MySQL and Postgres and get fast JOINs in one and 10-second JOINs in the other. Always index FKs explicitly so the schema works identically across engines and so future readers do not have to remember which engine auto-indexes what.
Example 2 — Posts and Comments with SET NULL
A second worked example, this time with a different cascade behavior so you can see the contrast.
-- Posts table is the ONE side
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Comments table is the MANY side.
-- author_id is nullable: an anonymous comment is allowed.
-- ON DELETE SET NULL means deleting a user keeps the comment but
-- removes the author link, so the body of the discussion is preserved.
CREATE TABLE comments (
id BIGSERIAL PRIMARY KEY,
post_id BIGINT NOT NULL
REFERENCES posts(id) ON DELETE CASCADE,
author_id BIGINT
REFERENCES users(id) ON DELETE SET NULL,
body TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Index BOTH foreign keys
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_author_id ON comments(author_id);
-- Seed
INSERT INTO posts (title, body) VALUES ('Hello world', 'first post');
INSERT INTO users (email) VALUES ('dave@example.com');
INSERT INTO comments (post_id, author_id, body) VALUES
(1, 4, 'Great post!'),
(1, 4, 'I learned something.'),
(1, NULL, 'anonymous reply');
-- Delete the user; comments survive but author_id becomes NULL
DELETE FROM users WHERE id = 4;
SELECT id, post_id, author_id, body FROM comments ORDER BY id; id | post_id | author_id | body
----+---------+-----------+----------------------
1 | 1 | | Great post!
2 | 1 | | I learned something.
3 | 1 | | anonymous reply
(3 rows)Two design choices are visible here. First, comments.post_id is NOT NULL and uses CASCADE — a comment cannot exist without its post, and deleting a post wipes the thread. Second, comments.author_id is nullable and uses SET NULL — comments outlive their authors, so the discussion stays intact when a user account is removed. Same shape, different cascade rules, different intent.
Example 3 — Required vs Optional Relationships
The "many" side can be either required (FK is NOT NULL) or optional (FK allows NULL). The choice changes what the relationship means.
-- Required: every order MUST belong to a user
CREATE TABLE orders_required (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
total INTEGER NOT NULL
);
-- Optional: a draft order can exist before a guest signs up
CREATE TABLE orders_optional (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
total INTEGER NOT NULL
);
-- Watch what each one rejects
INSERT INTO orders_required (total) VALUES (100);
-- ERROR: null value in column "user_id" violates not-null constraint
INSERT INTO orders_optional (total) VALUES (100);
-- INSERT 0 1 (allowed: this is a draft order)
INSERT INTO orders_required (user_id, total) VALUES (999, 100);
-- ERROR: insert or update on table "orders_required" violates foreign
-- key constraint -- Key (user_id)=(999) is not present in users. id | user_id | total
----+---------+-------
1 | | 100
(1 row)The NOT NULL constraint catches missing parents at insert time, and the foreign key constraint catches references to nonexistent parents. Together they make it impossible to insert a row that breaks the relationship — the database refuses bad data instead of silently storing it.
Example 4 — Aggregating Children per Parent
The single most useful query against a one-to-many shape is "summarize each parent by its children." Order counts per user, comment counts per post, total invoice value per customer — all the same pattern.
-- For each user: number of orders, total spent, last order date.
-- LEFT JOIN keeps users with zero orders in the result.
-- COALESCE turns the NULL aggregates from no-match users into 0.
SELECT u.id,
u.email,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total_cents), 0) AS total_spent_cents,
MAX(o.created_at) AS last_order_at
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
ORDER BY total_spent_cents DESC; id | email | order_count | total_spent_cents | last_order_at
----+-------------------+-------------+-------------------+------------------------
2 | bob@example.com | 1 | 725 | 2026-04-14 10:12:03+00
3 | carol@example.com | 0 | 0 |
(2 rows)This single query pattern — LEFT JOIN child ON child.fk = parent.id GROUP BY parent.id — answers a huge fraction of all reporting questions you will ever write against an OLTP database. It works only because the FK lives on the many side, the join column is indexed, and GROUP BY collapses the children back to one row per parent.
PostgreSQL vs MySQL — Key Differences
| Behavior | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| Auto-index on FK column | No — you must CREATE INDEX yourself | Yes — InnoDB creates an index automatically |
| Default ON DELETE | NO ACTION (immediate, like RESTRICT) | NO ACTION (alias for RESTRICT in InnoDB) |
| Deferrable constraints | Yes (DEFERRABLE INITIALLY DEFERRED) | No |
| FK enforcement engine | Always on | Only InnoDB; MyISAM ignores FKs entirely |
| Naming the constraint | CONSTRAINT fk_orders_user FOREIGN KEY ... | Same syntax |
| Cascading on TRUNCATE | TRUNCATE ... CASCADE required | Cannot TRUNCATE a referenced parent |
The single most important difference for day-to-day work is the FK index. Always index FKs explicitly so your schema behaves identically across engines and so reviewers do not have to remember which engine auto-creates what.
Common Mistakes
1. Putting a list on the "one" side instead of an FK on the "many" side.
The classic beginner schema has users.order_ids TEXT storing '10,11,12'. This breaks JOINs, breaks indexing, breaks referential integrity, and breaks every aggregate query. Every comma you add multiplies the pain. The fix is mechanical: delete the column, add user_id BIGINT REFERENCES users(id) to the orders table, and migrate the data with one INSERT...SELECT statement.
2. Forgetting to index the FK column in PostgreSQL.
Postgres creates the constraint but not the index. Your JOINs work but they sequential-scan the child table on every query, and CASCADE deletes scan the entire child table for each parent row deleted. Always pair REFERENCES users(id) with CREATE INDEX idx_orders_user_id ON orders(user_id) in the same migration. Make it muscle memory.
3. Using ON DELETE CASCADE when you actually want SET NULL or RESTRICT. CASCADE is dangerous when the children carry independently valuable data. Deleting a user should not silently destroy years of forum posts, financial records, or audit logs. Pick the cascade behavior based on whether the child can stand alone: CASCADE for sessions and tokens, SET NULL for content authored by the parent, RESTRICT for anything financial or legally retained.
4. Making the FK nullable when the relationship is required.
If every order must belong to a user, the column must be NOT NULL. A nullable FK silently allows orphan rows that satisfy the foreign key check (NULL is always allowed even with a FK constraint) but break every report and every business rule. Pick NOT NULL by default and only relax it when the relationship is genuinely optional.
5. Confusing the constraint with the index.
REFERENCES users(id) is a rule — the database refuses to store invalid values. CREATE INDEX is a lookup structure — the database can find rows quickly. They are independent objects. You can have a constraint without an index (slow) or an index without a constraint (no integrity guarantee). You almost always want both, on every FK column, every time.
Interview Questions
1. "In a one-to-many relationship, which side carries the foreign key, and why?"
The foreign key lives on the "many" side, pointing at the primary key of the "one" side. If a user has many orders, the user_id column lives on the orders table, not on users. The reason is that each child row has exactly one parent, so a single integer column can fully express the relationship. Putting the relationship on the "one" side would force the parent table to store a variable-length list of child IDs, which breaks indexing, breaks JOINs, breaks referential integrity, and forces a schema change every time a child is added or removed. Storing the FK on the child side makes the relationship symmetric to query, scales cleanly from one row to a billion, and lets the database enforce integrity with a single REFERENCES constraint.
2. "Walk me through the four ON DELETE behaviors and when you would use each."
ON DELETE CASCADE deletes every child row when the parent is deleted — use it when the children cannot exist without the parent, like sessions, tokens, or per-user cache entries. ON DELETE SET NULL keeps the children but nulls out the FK column — use it when the children outlive the parent and the link is optional, like blog posts that should survive their author being deleted. ON DELETE RESTRICT blocks the parent delete immediately if any child rows exist — use it when orphaning would be a serious bug, like invoices, payments, or audit records. ON DELETE NO ACTION is the SQL default and is essentially the same as RESTRICT, except the check happens at the end of the statement (or transaction, if deferrable). The choice expresses intent — future readers should be able to look at the constraint and immediately understand whether the child is dependent, independent, or load-bearing.
3. "Why must you index foreign key columns in PostgreSQL, and what happens if you forget?"
PostgreSQL creates a foreign key constraint when you write REFERENCES, but it does not create an index on the column — those are two different objects with different jobs. Without the index, three things go wrong. First, every JOIN from parent to child becomes a sequential scan of the child table, so a query that should return three rows reads ten million. Second, every DELETE or UPDATE of a parent row triggers a check against the child table, and that check is also a sequential scan when the index is missing — deleting a hundred users from a ten-million-row child table becomes a billion-row operation. Third, those scans hold locks that block other writers, so an admin cleanup can become a multi-minute outage. The fix is to always pair the FK declaration with an explicit CREATE INDEX in the same migration. MySQL InnoDB hides this footgun by auto-creating the index, but writing it explicitly makes the schema portable.
4. "What is the difference between a NULL foreign key and a foreign key pointing at a row that does not exist?"
A NULL foreign key is a row that explicitly has no parent — the relationship is absent. The foreign key constraint allows this by default (NULLs are always permitted), and you use it when the relationship is optional, like a guest order with no user account yet. A foreign key pointing at a nonexistent parent row is a violation of referential integrity — the database refuses to insert it and raises a foreign key violation error. The two situations look similar in conversation but the database treats them very differently: NULL is "no parent," nonexistent is "broken pointer." If you want to forbid the no-parent case, declare the column NOT NULL. If you want to forbid the broken-pointer case, declare the foreign key — that is the entire point of the constraint.
5. "I have a users table and an orders table with orders.user_id REFERENCES users(id) ON DELETE CASCADE, but no index on user_id. Why is deleting a user slow?"
When you delete a user, Postgres must find and delete every order that references that user, and to do that it has to look up rows in orders by user_id. Without an index on orders.user_id, that lookup is a sequential scan of the entire orders table, and Postgres must perform it for every parent row deleted. Deleting one user scans ten million rows; deleting a hundred users scans a billion. The CASCADE itself is fine — the cost is in finding the children. Adding CREATE INDEX idx_orders_user_id ON orders(user_id) turns each lookup into a millisecond index scan, and a delete that previously took minutes finishes instantly. This is the canonical example of why every FK column needs an index in Postgres, even when you do not intend to query the child table directly.
Quick Reference — One-to-Many Cheat Sheet
+-----------------------------------------------------------------+
| ONE-TO-MANY CHEAT SHEET |
+-----------------------------------------------------------------+
| |
| THE RULE: |
| FK lives on the MANY side, points at the ONE side. |
| "X has many Y" -> Y carries x_id BIGINT REFERENCES X(id) |
| |
| REQUIRED RELATIONSHIP: |
| user_id BIGINT NOT NULL REFERENCES users(id) |
| |
| OPTIONAL RELATIONSHIP: |
| user_id BIGINT REFERENCES users(id) -- nullable |
| |
| ALWAYS PAIR WITH AN INDEX (Postgres does NOT auto-create): |
| CREATE INDEX idx_orders_user_id ON orders(user_id); |
| |
| ON DELETE OPTIONS: |
| CASCADE -- children die with the parent |
| SET NULL -- keep children, null the FK |
| RESTRICT -- block parent delete if children exist |
| NO ACTION -- SQL default; like RESTRICT but deferrable |
| |
| PARENT QUERIES CHILDREN VIA JOIN: |
| SELECT u.email, COUNT(o.id) |
| FROM users u LEFT JOIN orders o ON o.user_id = u.id |
| GROUP BY u.id, u.email; |
| |
+-----------------------------------------------------------------+
+-----------------------------------------------------------------+
| KEY RULES |
+-----------------------------------------------------------------+
| |
| 1. FK on the MANY side. Always. No exceptions. |
| 2. Never store a list of IDs on the ONE side. |
| 3. Index every FK column explicitly in PostgreSQL. |
| 4. Pick ON DELETE based on whether children can stand alone. |
| 5. NOT NULL the FK if the relationship is required. |
| 6. Same shape from 100 rows to 100 million -- never reshape. |
| 7. Use LEFT JOIN to keep parents with zero children visible. |
| 8. The constraint and the index are different objects -- need |
| both on every FK column. |
| |
+-----------------------------------------------------------------+| Question | Right Way | Wrong Way |
|---|---|---|
| Where does the FK go? | On the many side | On the one side as a list |
| Index the FK? | Always, explicitly | Hope MySQL does it for you |
| Required relationship? | NOT NULL + REFERENCES | Nullable column with no constraint |
| Delete parent with children? | Pick CASCADE / SET NULL / RESTRICT | Default and hope for the best |
| Find children of one parent? | WHERE user_id = ? (indexed) | Scan and parse a comma list |
| Aggregate children per parent? | LEFT JOIN ... GROUP BY parent.id | One query per parent in app code |
| Add a new child? | INSERT INTO orders (user_id, ...) | Append to a string column |
| Storage shape grows? | Same shape forever | Re-migrate every order of magnitude |
Prev: Lesson 8.1 -- One-to-One Relationships Next: Lesson 8.3 -- Many-to-Many Relationships
This is Lesson 8.2 of the Database Interview Prep Course -- 12 chapters, 58 lessons.