Engines automatically index the referenced key on the parent side of a foreign key but usually leave the referencing columns on the child side unindexed. What goes wrong in production because of that, and how do you decide which of those columns to index?

## Why the asymmetry exists A foreign key's referenced columns must be a primary key or unique key, so the parent always has a supporting index and the child-side existence probe is fast by construction. Nothing in the standard requires an index on the *referencing* columns, and most engines do not create one. MySQL/InnoDB is the notable exception — it requires and will auto-create an index on the child columns — while Postgres, Oracle and SQL Server leave it entirely to you. ## What breaks **Parent deletes and key updates become table scans.** The check "do children exist for this key?" is executed per affected parent row, per referencing table. With no index it is a full scan. Deleting 10,000 parents from a table referenced by two 100-million-row children is 20,000 full scans; the statement appears hung. The same applies to `ON DELETE CASCADE`, which must *find* and then delete the children. **Locking blowups.** Where the engine locks rows it examines while enforcing a referential action, an unindexed scan widens the lock footprint far beyond the logically affected rows. Two transactions deleting different parents can then contend on the same child pages and deadlock. Deadlock graphs that show two parent DELETEs fighting over an unrelated child table are the classic fingerprint. **Everyday query plans suffer too.** `SELECT * FROM order_items WHERE order_id = ?` and nested-loop joins driven from the parent both want exactly that index. So the index usually pays for itself twice. ## Diagnosing it Start from the catalog: enumerate foreign keys and check whether any index has the referencing columns as its *leading* columns. A composite index on `(status, order_id)` does not serve the reverse search for `order_id`; leading-column order matters. Then confirm with an execution plan for a single-parent DELETE — a sequential/full scan of the child under the delete node is the smoking gun. Watching for long lock waits attributed to a child table during parent maintenance is the operational signal. ## Choosing which ones to index An index on every referencing column is a defensible default, but it is not free: each one adds write amplification on child INSERT/UPDATE/DELETE, more space, and more pages to keep cached. Decide per relationship: - **Index it** when parent rows are ever deleted or their keys change; when a referential action (CASCADE, SET NULL, SET DEFAULT) is declared; when the application queries children by parent; when the child table is large. - **Consider skipping** when the parent is append-only reference data that is never deleted and whose key never changes (currencies, country codes), when the child table is small enough that a scan is trivial, or when the referencing column is so low-cardinality that the index would rarely be chosen anyway. - **Watch the leading column.** If a composite index already starts with the referencing columns, you are covered; if it merely contains them, you are not. Also note the difference between the *existence* search and the *action* search: even a plain `NO ACTION`/`RESTRICT` foreign key needs the search, so "we don't use CASCADE" is not a reason to skip the index. ## Alternatives and mitigations Where adding the index is genuinely undesirable, the workable alternatives are structural: delete children explicitly in the application before the parent (using whatever access path exists), partition the child by the parent key so parent removal becomes a partition drop, or soft-delete parents so the referential search never fires. Dropping the foreign key to make the delete fast is a trade of correctness for latency and should be an explicit, argued decision rather than an accident.