SQL · Chapter 33 of 42

PRIMARY KEY

The PRIMARY KEY uniquely identifies each row. It is UNIQUE and NOT NULL. Every table should have one.

Common choices: an auto-increment integer (`SERIAL`/`AUTO_INCREMENT`) or a UUID.

Natural vs surrogate

Natural: an existing unique value (email). Surrogate: a synthetic ID (a BIGINT counter). Surrogate is usually easier to change and index.

Example 1 (sql)

CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

Output

Auto-increment surrogate + natural unique key

Best of both worlds.

Example 2 (sql)

CREATE TABLE user_roles (
  user_id BIGINT,
  role VARCHAR(50),
  PRIMARY KEY (user_id, role)
);

Output

Composite PK on two columns

Great for many-to-many join tables.

💡 Note: Databases automatically create an index on the PRIMARY KEY, giving fast row lookups.