What is Primary Key?
Definition
A primary key is the column or set of columns that uniquely identifies each row in a relational table. Its values may not be NULL, may not repeat, and a table can have at most one primary key. The database automatically creates a unique index for it, and other tables refer to a row through this value using foreign keys, which is why primary keys should be chosen to stay stable.
Also known as: PK, primary key constraint
The contract a primary key keeps
A primary key is a row's identity. The database enforces three rules: values are unique, they cannot be NULL, and each table has at most one primary key, though it may span several columns. Good design adds a fourth rule the database cannot enforce: the key should never change. Other tables store it as a foreign key, it ends up in URLs and cache keys, and changing an identity later sets off a chain of updates.
Declaring a primary key automatically creates a unique index, a B-tree in PostgreSQL. In MySQL's InnoDB engine the primary key goes further still: the table data itself is physically stored in primary key order.
Natural or surrogate?
A natural key is a value that already exists in the data and is believed to be unique: an email address, an ISBN, a tax number. A surrogate key is generated purely to identify the row and carries no business meaning. Surrogates are usually the safer bet because natural keys change in ways nobody planned for: people update their email, an industry code changes format. Using personal data as a primary key also copies that data into every referencing table, log line and URL. The common, robust pattern is a surrogate primary key plus a separate UNIQUE constraint on the natural value:
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
full_name text NOT NULL
);
-- Composite primary key on a join table
CREATE TABLE product_tags (
product_id bigint NOT NULL REFERENCES products (id),
tag_id bigint NOT NULL REFERENCES tags (id),
PRIMARY KEY (product_id, tag_id)
);Auto-increment or UUID?
| Option | Strengths | Watch out for |
|---|---|---|
| Auto-increment integer | Small (8 bytes), ordered, human-readable | You must insert to learn the ID; reveals record counts in URLs; collides when merging separate databases |
| Random UUID (v4) | Generated anywhere, even on the client; not guessable | 16 bytes; random distribution fragments B-tree indexes |
| Time-ordered UUID (v7) | Keeps UUID benefits while inserting in order | Roughly exposes when the row was created |
If you go with integers, bigint instead of a 32-bit int is cheap insurance; converting a live table that is approaching the roughly 2.1 billion limit is painful. Sequential IDs in URLs are not a vulnerability on their own, but every request must check in the authorization layer that the record belongs to the caller, otherwise incrementing an ID by one exposes someone else's data.
Misconceptions that cause trouble
- “IDs have no gaps.” Rolled-back transactions and deleted rows consume sequence values, so 1, 2, 5, 9 is perfectly normal. Values that must be gapless, such as invoice numbers, should be generated separately from the primary key.
- “A table can do without one.” Without a primary key, duplicate rows become hard to tell apart, and ORMs and some replication tools expect a key that identifies each row.
- “The ID should carry meaning.” Keys that embed a country code or a date lose their meaning when that fact changes. Business meaning belongs in its own columns.
The choice of primary key is one of the most expensive database schema decisions to reverse, so it deserves real thought in the first tables you create.