What Does a Primary Key Guarantee?

A primary key guarantees three things. The values are unique — no two rows can have the same key. They are not NULL — every row has a key, because a row with no identity is not a row you can point at. And they are minimal — the key contains no column that could be removed without losing the ability to identify a row. In practice the third point is what makes a composite key a design smell: if one column of the key determines the rest on its own, that column should be the key and the others should be ordinary columns.

What it does not guarantee is equally important. A primary key says nothing about the order of the rows, nothing about the meaning of the key value, and nothing about whether the key will still be there next year. It is not an ordering guarantee, it is not a business rule, and it is not immutable. Updating a primary key is legal and sometimes necessary — but any table with a foreign key pointing at it will have to cascade that change, or the update will be rejected.

Two consequences follow from the uniqueness guarantee. The engine can use the key as the physical identity of the row — in SQLite an INTEGER PRIMARY KEY is an alias for the row's own identifier, and in MySQL InnoDB the primary key is the clustered index, meaning the whole row is physically stored in key order. That is why a random UUID primary key is a genuine performance problem in MySQL: every insert lands in a random page of the index, causing page splits and write amplification.

And because the key is unique, it is the natural target for a foreign key and the natural identifier to pass to another system. It is also the one thing you should never renumber casually, because every external reference, every cached id and every log line that mentions it becomes wrong.

The engine refuses the write and reports a constraint violation. In SQLite the message is "UNIQUE constraint failed: accounts.id"; in PostgreSQL it is "duplicate key value violates unique constraint"; in MySQL it is error 1062. The statement is rejected and the row is not written — but the surrounding transaction, if there is one, is left in a failed state in PostgreSQL and has to be rolled back.

CREATE TABLE accounts (
  id INTEGER PRIMARY KEY,
  email VARCHAR(200) NOT NULL UNIQUE
);

INSERT INTO accounts (id, email) VALUES (1, '[email protected]');

-- A second row with the same id would raise:
--   UNIQUE constraint failed: accounts.id
-- INSERT OR IGNORE turns that violation into a no-op instead of an error.
INSERT OR IGNORE INTO accounts (id, email) VALUES (1, '[email protected]');

SELECT id, email FROM accounts;

One row comes back: id 1 with [email protected]. The second insert was ignored because the key already existed, and Ada's row is untouched — notice that Grace's email was not written either, so the whole attempted row was discarded rather than partially applied.

The INSERT OR IGNORE form is useful for exactly this situation: making a load idempotent so that re-running it does not fail. But it is worth knowing precisely what it ignores. It suppresses not only the duplicate key but also NOT NULL violations, CHECK constraint failures and, in SQLite, foreign key violations if the clause is used as a conflict clause against a specific constraint. That means a row with genuinely invalid data can disappear silently, and you end up with a load that reported success and quietly dropped records. If you need to know, count the rows you sent and the rows you stored, and log the difference.

The alternative is to catch the error and handle it deliberately — either retrying with a different key or falling back to an update. That is what an application does when the duplicate is meaningful rather than merely a replay. The rule of thumb: use OR IGNORE for idempotent ingestion where a duplicate means the same thing happened twice, and catch the constraint error when a duplicate means something went wrong that a human should know about.

A composite key exists when no single column identifies a row. A booking is identified by a room and a night, not by either alone. A line item in an order is identified by an order and a line number. A many-to-many link between two tables is identified by the pair of foreign keys.

The rule is that the key must be minimal — no column in it may be redundant. If the key is (room_id, night, hotel_id) and hotel_id is functionally determined by room_id, then hotel_id does not belong in the key: the key is not minimal, and including it means you have to keep all three consistent for no benefit. The same rule that produces third normal form produces minimal composite keys.

When should you reach for one? Genuinely, when the relationship is the identity — the link table between two entities is the classic case, and there the composite key is usually two foreign keys plus a primary key made of both, which prevents the same pair being linked twice. It is also right for a junction table that carries its own attributes: a book_authors table with a role column needs (book_id, author_id) as its key so that the role belongs to the specific pairing.

When should you avoid one? When a surrogate key would be simpler. A composite key spreads across every foreign key that references the table, makes joins wider, and makes the primary key of a child table into three or four columns instead of one. A table with a single-column surrogate key plus a UNIQUE constraint on the business key gives you the same guarantee with a much simpler join graph.

One more consideration for an interview: composite keys and NULL do not mix. Every column in a primary key is implicitly NOT NULL, which is correct — a row with a missing component has no identity. But a composite UNIQUE constraint behaves differently, because SQL allows multiple NULLs in a unique column and treats them as distinct from each other. That asymmetry surprises people and is worth being able to explain.