Keys and Referential Integrity

Chapter 8 let the database reject a rating of 99, but a row can still be perfectly typed and completely untrue: an order naming customer 999, an order item pointing at an order that was deleted yesterday. Chapter 9 of the SQL for Beginners series is how SQL guarantees that rows in one table may only refer to rows that exist in another — through keys: a primary key that identifies each row, and a foreign key that points at somebody else’s.

Primary keys

A primary key is the column (or columns) that make a row identifiable: unique, never NULL, and stable for the life of the row. In our sample schema it is always id, declared as INTEGER PRIMARY KEY so SQLite also gives it automatic numbering. Two consequences follow that you will rely on constantly:

• Uniqueness is enforced, not hoped for — a second row with the same id is refused with UNIQUE constraint failed.
• It gives every row a stable address — orders, order items and customers may all be fetched by id alone, no matter how the row is spelled out.

Resist the temptation to promote a pretty column such as email to primary key. Emails change, are long, and are hard to type in a support ticket; a small integer plus a UNIQUE constraint on the email gets you the same guarantee with none of the regret. That distinction — an identifier the business chose versus one the database invented — is the difference between a natural key and a surrogate key, and every schema design argument you will ever have is a variation of it.

Composite keys

Sometimes no single column identifies a row. The real identity of a line in an order is the pair (order, product): the same book may appear once per order, but not twice. Declare both columns together and the database enforces the pair:

CREATE TABLE line_keys (
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);
INSERT INTO line_keys VALUES (1, 4);
INSERT INTO line_keys VALUES (1, 4);

Expected output:

Error: UNIQUE constraint failed: line_keys.order_id, line_keys.product_id

The second insert is refused because the combination already exists, not because either value is repeated — (1, 5) would sail through. Our order_items table keeps its own surrogate id for convenience while carrying the same idea as a plain pair of foreign keys; the composite form appears whenever a many-to-many relationship is stored directly, such as a product’s tags.

Foreign keys: pointing at another table

A foreign key is a column whose value must be a primary key value from another table (or NULL). The REFERENCES clause is where the promise is written, and this is the shape our schema uses — a listing to read rather than a statement to re-run, because chapters 1–6 already created these tables:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    order_date TEXT NOT NULL,
    status TEXT NOT NULL
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id),
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL,
    unit_price NUMERIC NOT NULL
);

Read the second table’s declaration as a sentence: every item belongs to an order that exists, and to a product that exists. The referenced column must be a primary key or carry a UNIQUE constraint, which is why the pair (customers.id) works and (customers.email) would also work. Column names need not match — customer_id references id — but a naming convention that ends the foreign key in _id makes joins read themselves in chapter 10. And if your own tables were created without the REFERENCES clause, no ALTER TABLE can bolt a reference onto an existing column — SQLite requires rebuilding the table, which is exactly the chore chapter 22 has a recipe for.

SQLite keeps foreign keys off

Here is the trap that surprises everyone coming from another database: SQLite parses REFERENCES clauses but does not enforce them until you ask, once per connection. Ask from the shell:

sqlite3 store.db "PRAGMA foreign_keys;"

Expected output:

0

Zero means off, and every new sqlite3 session starts that way. Turn it on at the top of the session and try to break the rule:

PRAGMA foreign_keys = ON;
INSERT INTO orders (customer_id, order_date, status) VALUES (99, '2026-03-05', 'paid');

Expected output:

Error: FOREIGN KEY constraint failed

DELETE FROM customers WHERE id = 1;

Expected output:

Error: FOREIGN KEY constraint failed

The insert is refused because customer 99 was never registered; the delete is refused because orders 1 and 2 still name customer 1. That second behaviour is referential integrity doing its job: Aarav cannot evaporate while his orders remain. MySQL and PostgreSQL need no such switch — they always enforce declared keys — so when you port a script, add the PRAGMA to your connection setup (application code gets a place to do exactly this in chapter 23), and never assume an error means your SQL is wrong.

ON DELETE: what happens to the children

When a parent row must go, the child rows need a policy. The ON DELETE action in the reference declares it:

Watch both halves of the difference in a scratch pair of tables. First with enforcement switched off — SQLite’s default, and one PRAGMA away in either direction:

PRAGMA foreign_keys = OFF;
CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE shelf (
    id INTEGER PRIMARY KEY,
    category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    label TEXT NOT NULL
);
INSERT INTO categories VALUES (1, 'Tech');
INSERT INTO shelf (category_id, label) VALUES (1, 'Clean Code');

DELETE FROM categories WHERE id = 1;
SELECT COUNT(*) AS shelf_rows FROM shelf;

Expected output:

shelf_rows
----------
1

The category is gone and the shelf row behind it points at nothing — an orphan. Enforcement was off, so CASCADE never fired. Tidy up, switch on, and repeat:

PRAGMA foreign_keys = ON;
DELETE FROM shelf;
INSERT INTO categories (id, name) VALUES (2, 'Kids');
INSERT INTO shelf (category_id, label) VALUES (2, 'Matilda');

DELETE FROM categories WHERE id = 2;
SELECT COUNT(*) AS shelf_rows FROM shelf;

Expected output:

shelf_rows
----------
0

This time the delete took the shelf row with it. Choose CASCADE where the child has no meaning alone (an order item without its order), and NO ACTION where a human must decide (a customer with real orders — you archive them instead). Note that cascades only work if the child rows are reached by the constraint: nothing here protects a table that forgot to declare one.

Checking integrity on purpose

Even a well-built database deserves audits. The query below hunts for orders whose customer is missing — a left join from orders to customers that keeps only the rows where the customer side came back empty, which is exactly the technique chapter 11 develops:

SELECT COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

Expected output:

orphan_orders
-------------
0

Zero is the only acceptable answer, and it means every order in the sample data points at a real customer. Run the same check for items without orders and items without products; when a database has been edited by hand or imported from elsewhere, these three counts are the first thing to check before you trust any report built on top of them.

Common mistakes

• Assuming SQLite is enforcing your foreign keys — PRAGMA foreign_keys defaults to off, per connection; without it every REFERENCES clause is decorative.
• Forgetting that deletes propagate in both directions of thought — removing a customer either fails or takes their orders with it, depending on the action you declared.
• Choosing a mutable natural key as the primary key — when an email changes, every reference to it changes too; prefer a surrogate id plus a UNIQUE constraint.
• Referencing a column that is not unique — the target of REFERENCES must be a primary key or unique column, or the table will not be created.
• Running cascades on tables that predate them — adding a constraint to existing orphaned rows does not repair them; clean up with an audit query first.
• Reusing a deleted id without noticing — see AUTOINCREMENT in chapter 8 if ids must only ever grow.

Practice checklist

1. Check PRAGMA foreign_keys; in your session, switch it on, and confirm the value reads 1.
2. Insert an order for customer 99 and read the error; then insert a valid one for customer 4 (Marc) and delete it again.
3. Try DELETE FROM customers WHERE id = 3; with enforcement on, and explain why it is refused.
4. Build the categories/shelf pair twice — with enforcement off and on — and reproduce both row counts above.
5. Write the orphan checks for order_items against orders and products, and confirm both answer zero.

What's next?

Your tables now refer to each other safely, which finally makes it worthwhile to read them together. Chapter 10 combines customers and orders row by row in The INNER JOIN.