A primary key is the one column, or set of columns, that identifies a row beyond doubt. It is not decoration: every foreign key in your database points at one, the engine uses it to enforce uniqueness, and the query planner uses it to find rows fast. Choosing well is the highest-leverage decision in a schema, and there are only two honest options — a manufactured identifier or a natural key.
What makes a good primary key?
Four properties separate a good key from a bad one. It must be unique. It must never change. It must never be NULL. And it should be small, because it is copied into every foreign key that references it and into every index that includes it.
Here is the order_items table, with its key flagged by the metadata view:
PRAGMA table_info(order_items);
cid name type notnull dflt_value pk
--- ---------- ------- ------- ---------- --
0 item_id INTEGER 0 NULL 1
1 order_id INTEGER 1 NULL 0
2 product_id INTEGER 1 NULL 0
3 quantity INTEGER 1 NULL 0
4 unit_price REAL 1 NULL 0
The pk column shows 1 on item_id and 0 everywhere else. Note also that item_id is the only column that does not carry a notnull flag — and it is not null anyway, because a PRIMARY KEY on a single column is enforced as NOT NULL UNIQUE by every engine. That is not a detail to remember but a rule to internalise: declaring the primary key gives you nullability and uniqueness in one word.
When is a composite key the right answer?
When the row has no single column that identifies it. In a junction table between products and roasters, the pair (product_id, roaster_id) identifies the relationship, and neither column identifies it alone. That table declares a composite primary key:
CREATE TABLE product_roasters (
product_id INTEGER NOT NULL REFERENCES products(product_id),
roaster_id INTEGER NOT NULL REFERENCES roasters(roaster_id),
PRIMARY KEY (product_id, roaster_id)
);
PRAGMA table_info(product_roasters);
cid name type notnull dflt_value pk
--- ---------- ------- ------- ---------- --
0 product_id INTEGER 1 NULL 1
1 roaster_id INTEGER 1 NULL 2
Both columns carry the pk flag, with the second numbered 2 — the engine is telling you this is a two-column key. The rule to remember: in a composite key, every column is needed for uniqueness, and removing any one of them would allow a duplicate row. If you find a column in a composite key that could be dropped without allowing duplicates, it does not belong in the key.
Should the key be manufactured or natural?
A surrogate key is an identifier the database invents — an integer that counts up. A natural key is a value that already exists in the business and identifies the row on its own. The shop has a natural candidate: the SKU, which is printed on the product and used on the invoice.
SELECT sku, COUNT(*) AS rows_with_this_sku FROM products GROUP BY sku HAVING COUNT(*) > 1;
sku rows_with_this_sku
No SKU appears twice, which is exactly the property a natural key needs. But three things argue against using it as the primary key. SKUs are reused and reformatted by the business — 'TB-002' becomes 'TB2' or 'TB-002-EU' — and a key that changes is the worst possible key. SKUs are long strings, and every order_items row would carry that string instead of four bytes. And a natural key makes merging two product records nearly impossible, because the merged row would need two SKUs at once.
The compromise most teams settle on is a surrogate integer primary key plus a UNIQUE constraint on the natural key. That is exactly what the products table does, and the constraint left an index behind it:
PRAGMA index_list(products);
seq name unique origin partial
--- --------------------------- ------ ------ -------
0 sqlite_autoindex_products_1 1 u 0
The unique index behind the UNIQUE constraint. It matters because a foreign key pointing at a SKU would have to be a string too, and would have to follow the business when the SKU was reformatted. Point it at product_id instead and nothing downstream notices the reformat. And the constraint is enforced by the database, whatever the application does:
INSERT INTO products (product_id, sku, name, category, price) VALUES (99, 'TB-002', 'Duplicate Mug', 'drinkware', 9.95);
UNIQUE constraint failed: products.sku
The second mug with the same SKU is refused. The natural key still guarantees that a SKU identifies at most one product, and the surrogate key gives the rest of the schema something small, stable and fast to point at.
What can and cannot be changed later?
Adding a primary key to an existing table is possible in MySQL and PostgreSQL, and SQLite cannot do it at all — the only SQLite route is to create a new table with the key, copy the rows across, drop the old table and rename. Adding a UNIQUE constraint is similarly restricted in SQLite: the attempt is rejected with "Cannot add a UNIQUE column". This is why the primary key and the natural key constraint belong in the CREATE TABLE statement on day one, not in a later migration.
Quick reference
- A primary key must be unique, never NULL, stable for the life of the row, and small.
- PRIMARY KEY implies NOT NULL and UNIQUE — you do not need to write them twice.
- Prefer a surrogate integer key plus a UNIQUE constraint on the natural key.
- Composite keys are for rows identified by a combination; every column in them must be necessary.
- SQLite cannot add a primary key or a UNIQUE column to an existing table — declare them at creation.
Key drill: the shop wants to start quoting orders by a human-readable reference like 'SO-1043'. Decide whether that reference should be the primary key, a UNIQUE column, or neither — and write the sentence you would give the business to justify the choice.