Foreign Key
Someone inserts an order for customer_id 99. There is no customer 99. The order ships, the invoice goes to nobody, and a report of "orders by city" quietly drops a row. A foreign key would have refused that insert on the spot.
The rule
A foreign key only accepts values that exist in the table it points to.
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
FOREIGN KEY (product_id) REFERENCES products (product_id)
);orders.customer_id is the child column; customers.customer_id is the parent it references, and it must be a primary key or have a UNIQUE constraint. The database now guards the link from both ends:
- From the child side: inserting or updating an order with a
customer_idthat is not incustomersis rejected. - From the parent side: deleting (or changing the id of) a customer who still has orders would leave orphans, so by default that is rejected too.
A NULL in the child column is allowed unless you also say NOT NULL — it means "not linked to anyone yet".
You choose what happens when a parent row is deleted with an ON DELETE rule:
| Rule | When the customer is deleted, their orders… |
|---|---|
(default) / RESTRICT / NO ACTION |
block the delete |
CASCADE |
are deleted too |
SET NULL |
stay, with customer_id set to NULL |
FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ON DELETE CASCADE(RESTRICT and NO ACTION differ only in the timing of the check, which rarely matters at this level.)
The shortcut
Read a foreign key as a sentence: "every child value must be a parent value" — then ask the parent-side question: "if the parent goes, what should happen to the children?"
Name the link
order_items.order_idreferencesorders.order_id. Every item must belong to a real order.
Drills
Answer fast — trust the rule.
orders.customer_id references customers. There is no customer 99. What happens on INSERT INTO orders (order_id, customer_id, product_id, quantity) VALUES (120, 99, 1, 1);?
With ON DELETE CASCADE on orders.customer_id, you delete customer 1, who has two orders. What happens?
Which column can a foreign key reference?
Order items are meaningless without their order. Which ON DELETE rule suits order_items.order_id?
Remember
- A foreign key only accepts values that exist in the table it points to.
- Ask the parent question: if the parent goes, what happens to the children?
- Default blocks, CASCADE deletes, SET NULL unlinks.