A primary key is the column, or minimal set of columns, whose values uniquely identify every row in a relational database table. Among the database fundamentals, it is the core mechanism of entity integrity: no two rows may share a primary key value, and no part of it may be NULL. Each table can have at most one primary key, although it may have several other unique columns.
The primary key is more than a label. Other tables reference it through foreign keys, the database engine usually builds an index on it automatically, and in many storage engines it also determines the physical order of rows on disk. Choosing it well therefore affects correctness, performance and the ease of future schema changes.
How a primary key enforces uniqueness
A primary key is declared in the schema as a constraint, for example PRIMARY KEY (id) in a CREATE TABLE statement. The DBMS then rejects any INSERT or UPDATE that would create a duplicate value or a NULL in the key columns, so uniqueness is guaranteed by the engine rather than by application code.
To check uniqueness efficiently, the database maintains a unique index, usually a B-tree. The table below shows how common engines implement this index and what it means for storage.
| Engine | Primary key index | Practical consequence |
|---|---|---|
| MySQL and MariaDB (InnoDB) | Clustered index; rows stored in key order | Secondary indexes store the key, so long keys enlarge them |
| SQL Server | Clustered by default, can be nonclustered | Clustering choice is a separate design decision |
| PostgreSQL | Separate B-tree index over a heap table | Row order on disk is independent of the key |
| SQLite | Integer key aliases the internal rowid | An INTEGER PRIMARY KEY is the fastest lookup path |
Because a primary key is NOT NULL by definition, it differs from a plain UNIQUE constraint, which in most engines still allows NULL values. This rule ensures that every row can always be addressed.
Natural, surrogate and composite keys
Every table usually has one or more candidate keys, meaning minimal column sets that are unique. The designer picks one as the primary key, and the remaining candidates are often enforced with unique constraints. The choice falls into three broad types.
| Type | Example | Strength | Weakness |
|---|---|---|---|
| Natural key | ISO country code, ISBN | Meaningful; no extra column | Real-world values can change or repeat |
| Surrogate key | Auto-increment integer, UUID | Stable and compact | Carries no business meaning |
| Composite key | (order_id, line_number) | Models dependent rows exactly | Wider foreign keys and joins |
Surrogate keys dominate in practice because business identifiers such as email addresses, tax numbers or product codes change more often than designers expect. A sound pattern is a surrogate primary key plus a unique constraint on the natural key, which keeps references stable while still preventing duplicates.
Choosing the key data type
The data type of a primary key affects index size, insert speed and how identifiers can be generated. Narrow, immutable and monotonically increasing values generally perform best in clustered engines.
| Data type | Size | Generation | Main trade-off |
|---|---|---|---|
| INT | 4 bytes | Sequence or auto-increment | Limit of about 2.1 billion signed values |
| BIGINT | 8 bytes | Sequence or auto-increment | Practically unlimited; safe default |
| UUID version 4 | 16 bytes | Random, anywhere | Random inserts fragment clustered indexes |
| UUID version 7 | 16 bytes | Time-ordered, anywhere | Sequential inserts but wider than BIGINT |
| Natural text key | Variable | Supplied by the business | Larger indexes and possible changes |
Running out of an INT range is a real production failure, so BIGINT is the safer default for tables that may grow quickly. UUIDs suit distributed systems where clients or multiple nodes create rows without coordination, and time-ordered variants avoid most of the index fragmentation caused by random identifiers.
Primary key compared with related constraints
The primary key works together with other constraints, and confusing them is a common modeling error. Each answers a different question about the data.
| Concept | Purpose | NULL allowed | Number per table |
|---|---|---|---|
| Primary key | Identifies each row | No | At most one |
| Unique constraint | Prevents duplicate values | Usually yes | Any number |
| Foreign key | References a key in another table | Yes, unless declared NOT NULL | Any number |
| Candidate key | Any minimal unique column set | No, if chosen as primary | One or more |
A foreign key normally points to the primary key of the referenced table, although standard SQL also allows a reference to any unique key. When the referenced primary key changes, the database must update or block every dependent row, which is a strong argument for immutable key values.
Common mistakes with primary keys
Most primary key problems appear only after a system has grown, when changing the key is expensive. The following mistakes recur in practice.
- Omitting a primary key leaves duplicate rows possible and makes replication, updates and ORM mapping harder or impossible.
- Using mutable business data such as an email address forces cascading updates across every referencing table.
- Exposing sequential IDs publicly in URLs can reveal record counts and invite enumeration; a separate public identifier avoids this.
- Choosing a key that is too narrow, such as a 32-bit integer, leads to overflow errors in high-volume tables.
- Inserting random UUIDs into clustered indexes causes page splits and slower writes as the table grows.
The idea in brief
A primary key is a unique ID number for every row in a table. It works like a passport number. Two travelers can share a name and a birthday. They never share a passport number, so the number reliably tells them apart.
The database refuses a row without this ID. It also refuses an ID that is already taken. That simple rule keeps the data trustworthy. Other tables can then point to exactly the right record.
A practical scenario
An online bookstore first designs its customers table with the email address as the primary key. Orders, reviews and invoices all reference that email. When customers begin changing their addresses, each change must cascade through millions of related rows, and several updates fail because of locking during peak hours.
The team migrates to a BIGINT surrogate key generated by a sequence and keeps a unique constraint on the email column. Orders now store a compact 8-byte customer ID instead of a long text value, the related indexes shrink noticeably, and an email change becomes a single-row update that no longer touches any other table.