Primary Key
A unique identifier for a specific record in a database table.
Detailed Explanation
Every table in a relational database should have a primary key. It must be unique for every row and cannot be null. It is used to quickly look up a single record and is referenced by 'Foreign Keys' in other tables to create relationships. Common primary keys include auto-incrementing integers or UUIDs.
Quick Summary
A primary key uniquely identifies every row in a table. It's the address other tables use to reference this one, and the choice between integer and UUID has long-tail consequences for performance, security, and replication.
Key Takeaways
- Must be unique and non-null; the database enforces both.
- Auto-incrementing integer (`SERIAL`, `BIGSERIAL`, `IDENTITY`): compact, sequential, but predictable and tied to one DB node.
- UUID v4: globally unique, safe to generate client-side or across shards, but bigger and randomly distributed (bad for B-tree locality).
- UUID v7 / ULID: sortable, time-ordered UUIDs, best of both worlds and increasingly the default.
- Composite primary keys are valid but make joins and indexing more complex; prefer surrogate keys unless natural keys are very stable.
When to use it
- Any relational table, every one needs a primary key.
- Distributed systems where client-generated IDs avoid central coordination.
- Public-facing identifiers where exposing sequential integers would leak business metrics ("order #5" → low volume).
- Multi-master replication where two writers can't safely assign the same integer.
Common Mistakes
- Using business attributes (email, SSN) as the primary key; they change or repeat, breaking foreign keys.
- UUID v4 as the clustered primary key in MySQL/SQL Server, random inserts fragment the index badly.
- Forgetting to index foreign keys that reference the primary key.
- Exposing sequential integer PKs in URLs and APIs without authorization checks ("IDOR" vulnerabilities).
Primary Key, Frequently Asked
Integer or UUID for primary keys?
Default to UUID v7 / ULID for new systems, sortable like integers, globally unique, safe across shards. Use plain integers when DB scale is small, you control all writers, and you don't expose IDs publicly.
Can a table have no primary key?
Some databases allow it, but you almost never want it. Without a PK, you can't reliably update or delete specific rows, replication breaks, and ORMs misbehave. Always declare one explicitly.