Primary Keys, Foreign Keys & Constraints
How a schema enforces its own correctness — and how foreign keys link the tables normalization just split apart.
Overview
Normalization splits data across related tables. Keys and constraints are what make that split actually safe — a Primary KeyA column (or set of columns) that's unique and non-null for every row, used to identify that row unambiguously from anywhere else in the database.Learn more gives every row a unique identity, a Foreign KeyA column whose value must match an existing primary key value in another table — the database enforces this on every write, preventing orphaned references.Learn more makes one table's reference to another table's row something the database itself checks, and constraints like NOT NULL, UNIQUE, and CHECK let you encode basic correctness rules directly into the schema instead of trusting every piece of application code to enforce them correctly, forever.
Why It Exists
Once `customers` and `orders` are separate tables, something has to guarantee that `orders.customer_id` always points at a real, existing customer. Without that guarantee, nothing stops an order from referencing a customer that was deleted, or that never existed — an orphaned row that silently corrupts every report and every join that assumes the relationship is valid. Constraints exist so the database engine itself refuses to let that happen, instead of that guarantee living only in application code that has to get it right in every single code path, forever, with zero exceptions.
Real World Example
Say `orders.customer_id` is a foreign key referencing `customers.id`. Without that constraint declared, a bug in application code — or a bad manual `DELETE` — can remove a customer while their orders still reference that now-nonexistent id, and the database won't complain; you just have silently broken data. With the foreign key constraint in place, the database refuses the operation outright, or applies whatever rule you defined for it — `ON DELETE RESTRICT` blocks the delete while orders still reference that customer, `ON DELETE CASCADE` deletes those orders too, and `ON DELETE SET NULL` clears the reference instead of deleting anything. The point isn't which rule you pick — it's that you picked one, deliberately, and the database enforces it every time, not just when the application code remembers to check.
Example Data
customers — the parent table
| id | name | |
|---|---|---|
| 1 | Sarah Johnson | sarah.johnson@example.com |
| 2 | Michael Carter | michael.carter@example.com |
orders — valid rows: every customer_id matches a real row in customers
| id | customer_id | product |
|---|---|---|
| 101 | 1 | Laptop |
| 102 | 1 | Mouse |
| 103 | 2 | Keyboard |
orders — without a foreign key constraint: an orphaned row nothing catches
| id | customer_id | product |
|---|---|---|
| 104 | 3 | Monitor |
How It Works
A primary key is a column (or set of columns) that's both unique and non-null for every row — it's how any other table refers back to this one unambiguously. Keys are usually surrogate keys: an auto-incrementing integer or a UUID with no real-world meaning, chosen specifically because it never needs to change. A foreign key is a column whose value must match an existing primary key value in another table — the engine checks this on every insert and update, and enforces whatever `ON DELETE`/`ON UPDATE` behavior you defined for the case where the referenced row goes away. Beyond keys, `NOT NULL` requires a column to always have a value, `UNIQUE` requires every value in a column to be distinct, and `CHECK` lets you enforce an arbitrary rule, like `price >= 0`, directly at the database layer.
Diagram
A foreign key is the enforced link between the tables normalization split apart
customers
id (PRIMARY KEY), name, email
orders
id, customer_id (FOREIGN KEY → customers.id)
Common Mistakes
Skipping foreign key constraints to make inserts 'simpler' or marginally faster
Why: Without an enforced foreign key, nothing stops orphaned rows — records referencing a parent that no longer exists — from silently accumulating and corrupting every downstream join.
Fix: Let the database enforce referential integrity. Application-code checks alone will eventually be skipped by some code path, a script, or a manual fix.
Choosing a natural, real-world value (like an email address) as a primary key
Why: A primary key that can legitimately change breaks every foreign key referencing it the moment it does — an email update shouldn't cascade into rewriting every related table.
Fix: Use a surrogate key with no real-world meaning — an auto-incrementing id or a UUID — specifically because it never needs to change.
Leaving a foreign key's ON DELETE behavior at the engine's default instead of choosing it deliberately
Why: The default varies by engine and situation, and an unconsidered default can silently block deletes you expected to succeed, or cascade-delete more than you intended.
Fix: Decide RESTRICT, CASCADE, or SET NULL explicitly for every foreign key, based on what should actually happen to the child rows.
Interview Questions
What's the difference between a primary key and a foreign key?
A primary key uniquely identifies a row within its own table. A foreign key is a column in one table that references a primary key in another table, linking the two.
Why is it usually better to use a surrogate key (like an auto-incrementing id) instead of a natural key?
A natural key is a real-world value, like an email or a national ID number, and real-world values can change. If a natural key is used as a primary key, changing it means updating every foreign key that references it across every related table. A surrogate key has no real-world meaning, so it never needs to change for that reason.
What's the trade-off between ON DELETE CASCADE and ON DELETE RESTRICT, and how do you decide which to use?
CASCADE automatically deletes dependent rows when the parent is deleted, which is convenient but can silently delete far more data than intended if the dependency chain is deep. RESTRICT blocks the delete entirely while dependents exist, forcing an explicit decision, which is safer but requires the calling code to handle that failure case. The deciding factor is whether the child rows have any meaning without the parent — order line items don't exist without their order (CASCADE is reasonable), but a customer's historical orders usually shouldn't vanish just because the customer record is deleted (RESTRICT, or SET NULL with an 'archived' flag, is safer).
Production Best Practices
Do
✓Declare foreign key constraints for every real relationship — let the database enforce referential integrity.
✓Use surrogate keys (auto-increment or UUID) for primary keys instead of real-world values that can change.
✓Choose ON DELETE behavior deliberately for every foreign key, based on what should happen to dependent rows.
Don't
✗Don't rely on application code alone to keep references valid — some code path will eventually skip the check.
✗Don't use an email, username, or other mutable real-world value as a primary key.
✗Don't leave ON DELETE behavior at whatever the engine defaults to without confirming it's actually what you want.
Comparison
| Purpose | Can be null? | |
|---|---|---|
| Primary Key | Uniquely identifies a row | No |
| Foreign Key | References another table's primary key | Usually yes, unless also NOT NULL |
| Unique Constraint | Ensures no duplicate values in a column | Yes, unless also NOT NULL |
| Check Constraint | Enforces an arbitrary rule (e.g. price >= 0) | N/A |