A foreign key declares that values in one table must reference existing rows in another—enforcing referential integrity between customers and orders, posts and comments, etc.
Declaring FK
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
total REAL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);Practice: Run DDL in a fresh SQLite file or DB Fiddle schema pane. Drop test tables when experimenting.
Referential actions
CREATE TABLE order_items (
id INTEGER PRIMARY KEY,
order_id INTEGER NOT NULL,
product_id INTEGER NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);ON DELETE CASCADE removes child rows; RESTRICT blocks delete if children exist; SET NULL is another option.
JOIN preview
SELECT c.name, o.id AS order_id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;Foreign keys model relationships; JOINs query across them—covered next in the queries module.
Important interview questions and answers
- Q: FK without constraint?
A: Application code can enforce integrity, but race conditions make DB constraints safer. - Q: ON DELETE CASCADE risk?
A: Accidental parent delete removes all children—understand cascade graphs before enabling.
Self-check
- What error occurs inserting order with nonexistent customer_id (when FK enforced)?
- Name two ON DELETE actions.
Tip: Draw FK arrows on ER diagrams before writing JOIN queries—it prevents backwards joins.
Interview prep
- FK purpose?
Ensures referenced parent row exists—referential integrity.
- ON DELETE CASCADE?
Deleting parent removes dependent child rows automatically.