In a one-to-many relationship such as customers and orders, which table gets the foreign key column, and how do you model whether the child row is allowed to exist without a parent?

## The rule and the reason In a one-to-many relationship, one parent row relates to zero or more child rows, and each child relates to at most one parent. The foreign key goes on the child — the many side — because that is the side whose cardinality toward the other is one. A column holds a single value, so it can encode "one", not "many". `order.customer_id` is therefore correct, and a `customer.order_id` column would be structurally incapable of representing a customer with two orders. This is the single most-used mapping rule in relational design, and it generalises: 1:1 is the same rule plus a uniqueness constraint on the foreign key; M:N cannot be expressed with a foreign key at all and needs a link table. ## What a foreign key actually guarantees A foreign key constraint enforces **referential integrity**: every non-NULL value in the child column must match an existing key value in the parent. It does *not* enforce that a parent has at least one child, and it does not by itself prevent NULLs. So a foreign key gives you "no dangling pointers", nothing more. ## Optional versus mandatory participation ER models speak of *participation*: total (mandatory) or partial (optional). On the child side this maps directly to nullability. - `customer_id BIGINT NOT NULL REFERENCES customer(id)` — total participation. An order without a customer cannot be inserted. - `assignee_id BIGINT NULL REFERENCES app_user(id)` — partial participation. An unassigned ticket is a legitimate state. Defaulting to nullable "for flexibility" is a common and expensive mistake. Every nullable foreign key forces outer joins, three-valued logic in filters, and defensive null handling in application code, for a state the business may never intend to allow. Make the column NOT NULL unless you can name a real scenario where the child stands alone. Mandatory participation on the **parent** side — "every customer must have at least one order" — cannot be expressed by a foreign key or by nullability at all. Minimum cardinality greater than zero on the one side requires application logic, a deferred check, or a trigger, and interviewers like to hear that you know the schema cannot express it. ## Referential actions When a parent row is deleted or its key updated, the engine applies the declared action: - `RESTRICT` / `NO ACTION` — refuse if children exist. The safe default for business data: you do not want deleting a customer to silently erase their order history. - `CASCADE` — delete the children too. Appropriate when the child is existentially dependent: order lines under an order, junction rows under either parent, attachments under a document. Dangerous when the child is valuable in its own right, because one statement can remove a great deal of data. - `SET NULL` — detach. Only valid on a nullable column, and only when "child with no parent" is a meaningful state, such as an employee whose manager left. - `SET DEFAULT` — rarely useful; the default value must itself exist in the parent. ## Indexing The parent's key is already indexed because it is the primary key. The child's foreign key column is *not* automatically indexed in several engines. Without that index: - "all orders for customer X" scans the whole orders table; - deleting or updating a parent row must scan the child table to check for references, and in some engines holds locks while doing so, turning a small delete into a long blocking operation. So: index every foreign key column unless you can show nothing ever navigates or deletes in that direction. If the child is frequently read in a particular order, a composite index like `(customer_id, created_at DESC)` serves both the filter and the sort. ## Practical shape A well-formed 1:N looks like: parent with a surrogate primary key; child with its own primary key, a NOT NULL foreign key to the parent, an index on that key, and an explicit referential action. Nothing about the relationship is duplicated on the parent — no `order_count` column unless you have consciously decided to denormalise and accepted the job of keeping it correct. ## Degenerate and adjacent cases If you add a unique constraint to the child's foreign key, the relationship becomes one-to-one: at most one child per parent. If you discover the child actually needs several parents, the foreign key must be replaced by a link table — that is a genuine model change, not a tweak. And if the parent and child are the same table (`employee.manager_id`), it is still the same rule: the foreign key sits on the many side, which happens to be the same table, and it must be nullable to allow a root row. ## Quick checks in review Is the foreign key on the many side? Is it NOT NULL unless optionality is intentional? Is there an index on it? Is the delete action stated rather than inherited by accident? Four questions that catch most 1:N defects.