Foreign Key

A foreign key is the database version of a bouncer: it only lets in child rows that belong to a real parent row.

Question: What is a foreign key in SQL?

Answer: A foreign key is a column, or group of columns, in one table that points to a primary key or unique key in another table. Its job is to protect referential integrity, which means the database does not allow a child row to refer to a missing parent row. It can also control what happens when the parent row is updated or deleted.

Interview-Ready Answer: I think of a foreign key as the rule that keeps two tables consistent. The child table stores a value that must match an existing parent key, unless the column allows NULL. In practice, I use foreign keys to prevent orphan rows and to define clear delete or update behavior, such as CASCADE or RESTRICT.

What it is

A foreign key is not just a label; it is an enforceable rule. The parent table usually holds the master record, like customers, and the child table stores related rows, like orders. The child row is only valid if its foreign key value points to a real parent row.

How it works under the hood

1. You declare a constraint such as FOREIGN KEY (customer_id) REFERENCES customers(customer_id).
2. When you INSERT or UPDATE the child row, the database checks whether the referenced parent key exists.
3. If no matching parent exists, the statement fails immediately and the transaction can be rolled back.
4. When you DELETE or UPDATE the parent row, the database looks for matching child rows and applies the chosen action, such as CASCADE, SET NULL, or blocking the change.
5. Most engines do this inside the same transaction, so you do not get a race where one session validates a row and another session deletes the parent before commit.

When and why to use it

• Use a foreign key whenever one table logically depends on another table.
• Use it to stop orphan rows, which are child rows with no valid parent.
• Use it when you want the database to enforce rules, instead of trusting every application path to remember them.
• Use ON DELETE CASCADE only when child rows truly should disappear with the parent, such as temporary session data.

Foreign key vs other constraints

A primary key says, this row is the one and only record in this table. A foreign key says, this row must point to a real record over there. A unique constraint says, do not repeat this value in the same table.

Performance and practical numbers

Foreign key checks are cheap when the referenced parent key is indexed, which it usually is because primary keys and unique keys are indexed. The extra cost shows up on writes, not reads: inserts, updates, and deletes have to validate the relationship. If the child foreign key column is not indexed, deleting a parent with millions of children can force a large scan of the child table; that can turn a fast change into seconds or even minutes on busy systems. A good rule is to index the child foreign key column when the parent row is frequently updated or deleted, especially in large tables.

Important edge cases

• NULL values: if the foreign key column is nullable, a NULL usually means no relationship yet, so it does not violate the constraint.
• Composite foreign keys: a foreign key can use more than one column when the parent key is also multi-column.
• Self-referencing tables: a table can point to itself, like an employee row with a manager_id.
• Default delete action: if you do not choose one, many systems use NO ACTION or a very similar rule, so the parent change is blocked if children exist.
• Deferrable checks: some databases can delay checking until commit, which helps with circular inserts, but that is more advanced and not universal.

Memory-level takeaway: a foreign key is the database saying, show me the parent first, then I will accept the child.

Real-world story

Imagine an e-commerce checkout service with customers and orders. Every order must belong to a real customer, so the orders.customer_id column is protected by a foreign key. That means the system can trust every order record, every invoice join, and every support lookup.

Now the bug story: a team once imported order history before loading customer records. If the foreign key was enabled, the import failed fast with a clear constraint error, which was annoying but safe. If the team disabled the constraint to get the load through, they created orphan orders. The next morning, customer support saw orders with blank customer names, finance reports undercounted revenue because joins dropped missing parents, and ETL jobs logged mismatch errors. One missing rule turned a clean data model into a messy cleanup project.

What goes wrong: symptoms usually include constraint violation errors during writes, missing rows in joins, weird NULLs in reports, and users asking why their order or profile cannot be found. The root cause is almost always broken referential integrity, either because the foreign key was missing, disabled, or misconfigured.

Follow-up & Tricky Questions:

• Can a foreign key reference a unique key instead of a primary key? Yes. The referenced column or column set must be unique, so a primary key is common, but a unique key also works.
• What does ON DELETE CASCADE do? It deletes child rows automatically when the parent row is deleted, which is powerful but dangerous if you did not intend to remove history.
• What is the difference between RESTRICT and SET NULL? RESTRICT blocks the parent delete or update, while SET NULL keeps the child row but clears the foreign key value, which requires the column to allow NULL.
• Do foreign keys improve query speed? Not directly. They improve data quality, and with good indexes they can help joins, but the main job is enforcing correctness.
• Can a foreign key use multiple columns? Yes. That is called a composite foreign key, and it is used when the parent key is made of more than one column.
• Does a foreign key automatically create an index on the child column? Usually no. Many engines index the parent key because it is a primary or unique key, but you often need to add an index on the child foreign key yourself for faster deletes and updates.
• Tricky: Can a child row exist before the parent row? Normally no, because the insert is checked immediately. Some databases support deferred constraints, which delay the check until commit, but that is an advanced feature.
• Tricky: Does a NULL foreign key violate the constraint? Usually no, because NULL means unknown or not applicable, not a specific parent row. The column must be nullable, though.
• Tricky: If a foreign key exists, can the parent still be deleted? Yes, depending on the action. With CASCADE it deletes children, with SET NULL it clears them, and with RESTRICT or NO ACTION it is blocked.

Common Mistakes:

• Thinking a foreign key is just documentation. Correction: it is an enforced rule that the database checks on every relevant write.
• Forgetting to index the child foreign key column. Correction: the parent key is usually indexed automatically, but the child side often needs its own index for performance.
• Using CASCADE too casually. Correction: cascade is great for true dependent data, but it can delete more rows than you expect if the relationship is misunderstood.
• Assuming NULL is a violation. Correction: a nullable foreign key can be NULL, which means no current relationship.

Memory Hook: think of the parent as the office that stamps passports, and the child as the traveler. No valid stamp, no entry.

Cheat Sheet:

• Foreign key = child-table rule that points to a parent key.
• It enforces referential integrity and prevents orphan rows.
• It can reference a primary key or unique key.
• Common actions: CASCADE, SET NULL, RESTRICT, NO ACTION.
• Index the child foreign key column on large tables.
• NULL usually means no relationship, not a broken one.

Practice Tasks:

• Create authors and books, then add a foreign key from books.author_id to authors.author_id.
• Try inserting a child row with no matching parent, and observe the failure.
• Change the foreign key action to SET NULL or CASCADE and see how deletes behave differently.