The primary key
One column (or a small set of them) that identifies a row and never changes. Not the name — names change and repeat. Not the email — people switch. A number or a UUID that means nothing outside the database is called a surrogate key, and it is the right default precisely because it has no other job to lose.
create table order_items (
order_id integer not null references orders(id),
door_id integer not null references doors(id),
qty integer not null,
primary key (order_id, door_id) -- one line per door per order
);The foreign key
Without it, a mistyped id is simply a row that joins to nothing: the order disappears from every report and nobody knows why. With it, the mistake is rejected at the moment it is made, by the last component that can still tell it is a mistake.
create table runs (
id integer primary key,
van_id integer not null references vans(id),
day text not null
);
insert into runs (van_id, day) values (999, '2025-03-01');
-- FOREIGN KEY constraint failedForeign keys are declared in SQLite but not enforced unless the connection runs pragma foreign_keys = on; — per connection, every time. PostgreSQL and modern MySQL enforce them always. Any real SQLite application turns this on at startup.
One to many, and many to many
A junction table is not a workaround; it is the shape of the relationship. It is also the natural home for the facts about the pairing — the quantity, the price agreed on the day, who approved it.
-- one customer, many orders: orders.customer_id
-- one order, many lines: order_items.order_id
-- many doors in many orders: a table in the middle, holding both ids
create table order_items (
order_id integer not null references orders(id),
door_id integer not null references doors(id),
qty integer not null,
primary key (order_id, door_id)
);What happens to the children
Choose per relationship, by asking what the business means. Deleting an order should take its lines with it — they have no meaning alone. Deleting a customer should not silently delete four years of orders; that is what restrict is protecting you from.
van_id integer not null references vans(id) on delete cascade,
fitter_id integer references fitters(id) on delete set null| on delete | When the parent row goes |
|---|---|
| no action / restrict | the delete is refused while children exist — the safe default |
| cascade | the children are deleted too: right for lines of an order, wrong for orders of a customer |
| set null | the child keeps existing with no parent — the job whose fitter left |
| set default | rarer: point them at a placeholder row |
Indexes come along for the ride, except where they do not
A primary key is indexed automatically everywhere. A foreign key column is not — PostgreSQL and SQLite do not index it for you, and MySQL/InnoDB does. That matters twice: joining on it is a table scan, and deleting a parent row has to scan the child table to check for children. Index your foreign keys; the Indexes lesson is next but one.