One-to-Many Relationships (1:N)

One customer, many orders. One blog post, many comments. One teacher, many classes. The one-to-many relationship is the most common shape in every database, and it is expressed with exactly one foreign key — always on the many side.

The pattern

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total DECIMAL(10,2) NOT NULL
);

Read it from the child up: each order points at exactly one customer. Read it from the parent down: each customer can be behind zero, one or many orders. The FK lives on orders because an order cannot exist without its customer, but a customer can exist without orders.

Notice what is absent: no list of order ids on the customer row. Relational databases never store children as arrays or comma-separated lists on the parent. The parent does not know its children by name; the children know their parent. That inversion is the whole trick — it means the number of children can grow to millions without ever widening the parent row.

Querying a 1:N

Two queries cover most day-to-day use: the children of one parent, and the aggregate count per parent.

-- All orders of one customer, newest first
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

-- Every customer with their order count (including zero-order customers)
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY order_count DESC;

The LEFT JOIN matters. An INNER JOIN drops customers with no orders, which is rarely what a report wants. And when you join 1:N and also select columns from both sides, rows multiply — one customer row per order — so aggregate before you join, or you will overcount.

What to decide, every time

• Nullability — can the child exist alone? If not, NOT NULL on the FK.
• On delete — CASCADE (remove children), SET NULL (orphan them deliberately), or RESTRICT (refuse to delete a parent with children).
• Direction — the edge in the diagram goes from the parent's PK to the child's FK; label it 1:N.

-- Deleting a customer is refused while orders remain
CREATE TABLE orders (
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
);

-- Deleting a customer deletes their cart items with them
CREATE TABLE cart_items (
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE
);

-- Deleting a user keeps the ticket, but nobody is assigned
CREATE TABLE tickets (
  assignee_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);

A rule of thumb that holds up: CASCADE for data that is meaningless without its parent (cart items, comments, line items), RESTRICT for data with independent value (invoices, payments — you should not be able to delete a customer out from under an invoice), SET NULL for optional attributions. ON DELETE SET NULL requires the FK column to be nullable, which is also your signal that the relationship is optional.

Chains of 1:N are normal

Real schemas are mostly chains: customers → orders → order_items → (products). Notice order_items, which has two incoming 1:N edges — from orders and from products. Tables with several FKs are not a design smell; they are how facts ('this line of that order sells this product') get recorded.

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity SMALLINT NOT NULL CHECK (quantity > 0),
  unit_price_cents INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

The composite primary key here does double duty: it prevents the same product appearing twice on one order, and it doubles as the index that makes 'all items of this order' fast. When quantity or price changes are tracked per line, give the table its own surrogate id instead — the composite key stops being enough.

When 1:N stops being enough

Some relationships start as 1:N and grow. Tags are the classic case: first an article has one category (a plain FK on articles), then it needs several, and the single FK column cannot hold two values. The fix is not a comma-separated list — it is a join table, which turns one 1:N into two of them (articles → article_tags ← tags). If you catch yourself wanting to store several parent ids in one child column, the relationship has become many-to-many and the pivot table guide below shows the standard construction.

Common mistakes

• Putting the FK on the one side, as a customers.order_id column. One column cannot hold many children, so the design caps every customer at one order — the opposite of the intent.
• Storing children as a list: orders TEXT with values like '12,19,44'. It cannot be joined, indexed, or constrained, and it breaks referential integrity the moment any id is deleted.
• Not indexing the FK column. PostgreSQL builds an index for the primary key but not for the referencing column, so WHERE customer_id = ? and parent deletes both scan the whole table.
• Using INNER JOIN for reports and silently losing parents with zero children.
• Choosing CASCADE at the top of a deep chain. delete from customers can then take orders, order_items, and shipments with it in one unbounded cascade.
• Aggregating after a 1:N join: joining customers to orders to count something per order double-counts every customer with more than one order.

Best practices

• One FK column per 1:N relationship, on the many side, typed exactly like the parent's PK.
• Index every FK column: CREATE INDEX ON orders (customer_id). This single habit fixes more slow queries than any other.
• Decide the delete behavior deliberately per relationship, and default to RESTRICT when in doubt — data you can still delete manually is safer than data you deleted by accident.
• Use LEFT JOIN when listing parents, and COUNT(child.id) rather than COUNT(*) so parents with zero children report 0 instead of 1.
• Give child tables that are queried 'latest first' a composite index like (customer_id, created_at DESC) — it serves both the filter and the sort.
• In the diagram, walk each 1:N edge and check the FK actually exists in the schema; an edge without a constraint is a relationship the database will not protect.

How do I know which side is 'many'?

Ask which row could plausibly be duplicated. For customers and orders, a customer exists once but a customer can place hundreds of orders — customers is the one side. The FK then goes on orders. If the sentence 'one X has many Y' is as true as 'one Y has many X', it is many-to-many and needs a join table, not a single FK.

Should the FK column be NOT NULL?

Only if every child must have a parent. A comments.post_id should be NOT NULL — an orphan comment is meaningless. An employees.manager_id should be nullable — the CEO has no manager, and that is correct data, not a bug. Making a column NOT NULL costs nothing and prevents an entire class of half-written rows, so apply it whenever the relationship is truly mandatory.

Does the database index foreign keys automatically?

No, and this trips up PostgreSQL users in particular. The PK side gets an index because primary keys are always indexed; the referencing side does not. MySQL's InnoDB creates an index for foreign keys when none exists, which makes switching between the two engines a common source of surprise. Treat an FK index as an explicit, deliberate part of the schema.

Can one child have two parents of the same kind?

Yes — just add two FK columns. A flights table has departure_airport_id and arrival_airport_id, both referencing airports. Each is its own independent 1:N relationship, and the diagram shows two edges between the same pair of tables. Distinguish them by role in the column names; 'airport_id' alone would leave the meaning of each edge guessable but not known.