PRIMARY KEY

Uniquely identify each row with a PRIMARY KEY constraint.

Overview

A primary key uniquely identifies each row in a table. It is a combination of the NOT NULL and UNIQUE constraints, but with the additional semantic meaning that this column (or set of columns) is the canonical identifier for rows in the table. Every table should have a primary key it enables efficient lookups, JOIN operations, and is required for replication and some ORMs. For most tables, an auto-incrementing integer (SERIAL or BIGSERIAL) or a UUID makes the best primary key. Composite primary keys (spanning multiple columns) are appropriate for junction/association tables in many-to-many relationships.

Key Points

•PRIMARY KEY = UNIQUE + NOT NULL, with "this is the row identifier" semantics
•A table can have at most one primary key
•Inline: id SERIAL PRIMARY KEY for single-column PKs
•Table-level: PRIMARY KEY (col1, col2) required for composite PKs
•PostgreSQL automatically creates a unique B-tree index on the PK column(s)
•Use SERIAL or BIGSERIAL for auto-incrementing integer PKs

Examples

Single column PK with SERIAL

CREATE TABLE users (
  id    SERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

Composite primary key (junction table)

CREATE TABLE user_roles (
  user_id INTEGER NOT NULL REFERENCES users(id),
  role_id INTEGER NOT NULL REFERENCES roles(id),
  PRIMARY KEY (user_id, role_id)
);

PRIMARY KEY

Overview

Key Points

Examples

Exercises

Knowledge Check

What constraints does PRIMARY KEY implicitly enforce?