TopolearnThe key you pick is referenced everywhere, so changing it later touches every related table.
A candidate key is any minimal set of columns that uniquely identifies a row. You pick one as the primary key; the others should still be enforced with UNIQUE constraints.
- A natural key comes from the domain: an ISO country code, an ISBN, an email. It is meaningful but can change (people change emails) or turn out not to be unique.
- A surrogate key is generated by the database: an identity integer or a UUID. It never changes and is cheap to reference, so most application tables use one.
- A composite key spans several columns; junction tables are the classic case:
(student_id, course_id).
The important rule: a surrogate key doesn't remove the need to enforce the natural one. If emails must be unique, declare UNIQUE (email), otherwise two rows for the same person will appear the first time a form is double-submitted.
Integer vs UUID: bigint identities are small and index-friendly; UUIDs can be generated anywhere (clients, other services) and don't reveal row counts. Random UUIDv4 values scatter inserts across a B-tree index; time-ordered UUIDv7 keeps inserts mostly sequential and is now the usual choice when you want UUIDs.
Keep keys immutable. If a value can change, don't make it the primary key.
Example
CREATE TABLE country (
code char(2) PRIMARY KEY, -- natural key: stable ISO code
name text NOT NULL
);
CREATE TABLE app_user (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, -- surrogate
email text NOT NULL,
country char(2) NOT NULL REFERENCES country(code),
CONSTRAINT app_user_email_uk UNIQUE (email) -- natural key still enforced
);
INSERT INTO country VALUES ('AM', 'Armenia');
INSERT INTO app_user (email, country) VALUES ('ana@example.com', 'AM');
INSERT INTO app_user (email, country) VALUES ('ana@example.com', 'AM');
-- ERROR: duplicate key value violates unique constraint "app_user_email_uk"Remember
- Primary key = the chosen candidate key; enforce the others with UNIQUE
- Surrogate keys are stable; natural keys carry meaning but may change
- Prefer bigint identities or time-ordered UUIDs over random UUIDs for hot inserts
Watch out for
- Adding a surrogate id and forgetting the UNIQUE constraint on the real-world key.