Foreign Key Constraint
SQL · Working with Tables
Theory
A FOREIGN KEY ties a column in a child table to the primary key (or a unique key) of a parent table. orders.customer_id REFERENCES customers(id) means every order must belong to a customer that exists. The database checks this on every insert and update of the child, and on every delete or key update in the parent; this guarantee is called referential integrity.
A NULL foreign key is allowed unless the column is also NOT NULL; it simply means "no parent yet". You choose what happens when a parent row is deleted: the default (NO ACTION / RESTRICT) blocks the delete, ON DELETE CASCADE deletes the child rows too, and ON DELETE SET NULL clears the reference. Indexing the foreign key column speeds up joins and parent deletes. In SQLite, foreign keys are enforced only after PRAGMA foreign_keys = ON.
Why we use it
Without foreign keys, orders can point to customers that never existed or were deleted, and joins silently lose data. The constraint keeps relationships between tables valid at all times.
When to use it
Use foreign keys between related tables in transactional databases. Pick CASCADE only when children have no meaning without the parent. Many analytics warehouses accept the syntax but do not enforce it, so check data with joins there.
How it works
- INSERT or UPDATE a child row (orders)
- Is customer_id NULL? Yes: Allowed (unless NOT NULL). No: Look up the parent.
- Does customers have a row with that id? Yes: Accept the row. No: Reject: no parent key.
- On parent delete: block, CASCADE or SET NULL
- Relationships stay valid
Syntax
FOREIGN KEY (column) REFERENCES other_table(column)Example
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);Key points
- Prevents orphaned rows referencing non-existent parents
- Can cascade deletes with ON DELETE CASCADE, or use SET NULL
- NULL foreign key values are allowed unless NOT NULL is added
- Index the foreign key column for faster joins and deletes
- SQLite enforces it only with PRAGMA foreign_keys = ON