What does declaring a primary key on a table actually guarantee, and how does a relational engine enforce that guarantee?
answer
- NOT NULL + unique index = the whole mechanism
- One PK per table; other candidates → unique constraints
- Duplicate check happens in-transaction, race-free
- SELECT-then-INSERT is not equivalent
- Free seek path; cost paid on every write
basics
~20 sA primary key guarantees every row has a value in those columns (no NULLs) and no two rows share the same combination. The engine enforces it by marking the columns NOT NULL and maintaining a unique index it probes on every insert and key update.
solid answer
~50 sA primary key is the table's declared row identifier. It carries two guarantees: **no NULL** in any key column, and **uniqueness** of the whole key value across the table. A table has at most one primary key, though it may carry other unique constraints alongside it. Enforcement reuses machinery the engine already has: it marks the columns NOT NULL and maintains a unique index over them. Every insert, and every update that touches a key column, probes that index; a duplicate raises a constraint violation and the statement fails. That same index is what foreign keys pointing at this table use for cheap parent lookups. The practical consequence is that the guarantee holds no matter who writes — application code, a migration, an ad-hoc script, a bulk load. That is precisely why identity belongs in the schema rather than in application logic that only one code path runs.
go deeper
State the two guarantees — NOT NULL and unique — and that the engine backs them with a unique index. Being able to write the DDL is enough at this level.
Add the mechanism: the check happens inside the transaction via the index, which is why it is race-free where an application pre-check is not, and note the write-side cost.
Frame it as an invariant that must hold across every write path, discuss what happens when you add a PK to a table that already has duplicates, and mention collation defining equality.
Talk about identity as a schema-level contract others build on — foreign keys, replication, CDC, external references — and the cost of leaving a table without a declared identifier.
## What a primary key declares A primary key (PK) is the column, or ordered set of columns, that a table nominates as the official identifier of its rows. A table may have several *candidate keys* — column sets that happen to be unique — but exactly one is designated primary. The others can still be declared unique; they simply are not the handle that foreign keys and application code treat as "the" identity of the row. The declaration makes two promises simultaneously: 1. **Entity integrity (no NULLs).** Every key column is implicitly NOT NULL. NULL means "unknown or inapplicable", and an identifier that may be unknown identifies nothing. Declaring a nullable column as part of a PK either fails outright or silently promotes the column to NOT NULL, depending on the engine. 2. **Uniqueness of the whole key.** No two rows may carry the same key value. For a multi-column PK the *combination* must be unique — individual columns may repeat freely. Only one primary key per table is allowed. "I need two primary keys" almost always means one primary key plus additional unique constraints. ## How the engine enforces it There is no special "primary key subsystem". The engine implements the constraint with two existing features: - The columns are set NOT NULL, checked cheaply per row on write. - A **unique index** is created over the key columns (engines usually create it automatically; some reuse a suitable existing index). On every insert, and on every update that modifies a key column, the engine descends that index to the position where the new value would live and checks whether an equal value already exists. If it does, the statement fails with a uniqueness violation. Deletes and non-key updates still maintain the index entries so it remains an accurate mirror of the table. That check happens *inside* the transaction, so concurrent inserts of the same value serialize: one waits on the other's uncommitted index entry, then either fails (if the first commits) or proceeds (if the first rolls back). This is why "check with a SELECT, then INSERT" in application code is not equivalent — between the two statements another session can insert the same value. The unique index is the only thing that makes the guarantee race-free. ## What you get for free, and what it costs Because a PK always comes with a unique index, you also get a fast access path: lookups by the full key are index seeks rather than scans, and the index enforces ordering that range scans on the key can exploit. Foreign keys elsewhere depend on this index — resolving "does a parent with this key exist?" is one seek. The cost is write-side. Every insert, delete, and key-touching update maintains index entries and logs them, so the constraint is not free; on a very wide key it is measurably not free, because the key value is stored in every index entry. The correctness benefit almost always outweighs this, but it explains why bulk-load procedures sometimes drop and rebuild indexes. ## Why declare it rather than enforce it in code Application-level uniqueness checks fail in three predictable ways: races between concurrent requests, alternate write paths (a second service, an admin script, a data migration), and bugs in retry logic that insert twice. A database-enforced key survives all three, and it also serves as documentation — it tells the next engineer, and the query planner, what identifies a row. Planners use uniqueness to eliminate needless deduplication and to know a key lookup yields at most one row. ## Common corner cases - **No PK declared.** Some engines then invent a hidden internal row identifier. The table works, but replication tools, ORMs, and "delete exactly one duplicate row" operations become painful. Declare one. - **Duplicates already present.** Adding a PK to a live table fails until duplicates are resolved; the constraint is validated over existing rows first. - **Uniqueness is over the declared columns only.** `('ABC', 1)` and `('abc', 1)` may or may not collide depending on collation/case sensitivity — the index's comparison rules define equality, not your intuition.
- Why is a uniqueness check done in application code before the INSERT not equivalent to a primary key?Between the SELECT and the INSERT another session can insert the same value, so the check passes for both and you get duplicates. The unique index performs the check as part of the insert itself, inside the transaction, so concurrent inserters serialize and exactly one succeeds. Application checks also miss alternate write paths such as migrations, scripts, and second services.
- Can a primary key column be NULL if it is one column of a composite key?No. Every column of a primary key is implicitly NOT NULL, even in a composite key. Engines either reject the DDL or silently make the column NOT NULL. Nullable columns can only participate in unique constraints, where NULL handling is a separate discussion.
saying these in an interview costs you the question
- Saying a primary key is "just an auto-increment id column" — auto-increment is a value generator, not the constraint
- Claiming a table can have several primary keys instead of one PK plus unique constraints
- Believing NULL is allowed in a PK column as long as another key column is filled
- Assuming the uniqueness check can be replaced by an application-level SELECT before INSERT
- Thinking the PK's index is optional or separate from the constraint