Constraints Row identity PRIMARY KEY · SERIAL · composite

PRIMARY KEY Constraint

The row's permanent identifier — NOT NULL plus UNIQUE plus an automatic B-tree index. Single-column or composite, with surrogate vs natural-key trade-offs and a clear comparison to UNIQUE.

What a PRIMARY KEY does

The primary key is a table's permanent identifier for each row — the column (or combination of columns) you'd reach for to pick out one specific record. Mechanically, it's just NOT NULL + UNIQUE rolled into one declaration, with a few extras:

PostgreSQL automatically creates a B-tree index on the primary key.
Each table has at most one primary key.
The columns can never be NULL.
Foreign keys from other tables typically reference the primary key.

It's a strong recommendation — borderline a rule — that every table have one.

Syntax

-- column-level (single column)
CREATE TABLE table_name (
    id data_type PRIMARY KEY,
    ...
);

-- table-level (single or composite)
CREATE TABLE table_name (
    col_a data_type,
    col_b data_type,
    CONSTRAINT pk_name PRIMARY KEY (col_a, col_b)
);

Example 1 — single-column primary key

SQL

CREATE TABLE employees (
    employee_id  SERIAL CONSTRAINT emps_pk PRIMARY KEY,
    first_name   VARCHAR(20),
    last_name    VARCHAR(20),
    email        VARCHAR(100) NOT NULL
);

-- Auto-generated id
INSERT INTO employees (first_name, last_name, email)
VALUES ('Alice', 'Smith', 'alice@example.com');

-- NULL id rejected
INSERT INTO employees VALUES (NULL, 'Bob', 'Jones', 'bob@example.com');
-- ERROR: null value in column "employee_id" violates not-null constraint

-- Duplicate id rejected
INSERT INTO employees (employee_id, first_name, last_name, email)
VALUES (1, 'Carol', 'Lee', 'carol@example.com');
-- ERROR: duplicate key value violates unique constraint "emps_pk"

SERIAL is PostgreSQL's shorthand for "auto-incrementing integer with a sequence" — perfect for surrogate primary keys. (Modern PostgreSQL also offers GENERATED ... AS IDENTITY, the SQL-standard alternative.)

Example 2 — composite primary key

When no single column uniquely identifies a row, combine several into a composite primary key. Common in junction tables that link two other tables.

SQL

CREATE TABLE order_items (
    order_id    INT,
    product_id  INT,
    quantity    INT NOT NULL,
    CONSTRAINT order_items_pk PRIMARY KEY (order_id, product_id)
);

Each (order_id, product_id) pair is unique. The same order can include many products, and the same product can appear in many orders — but never twice in the same order.

Adding a PRIMARY KEY later

SQL

-- Add to an existing table
ALTER TABLE customers
    ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

-- Drop it
ALTER TABLE customers
    DROP CONSTRAINT customers_pk;

Adding a primary key requires that the column(s) already be NOT NULL and contain no duplicates. Both checks happen at constraint-creation time.

PRIMARY KEY vs UNIQUE

Aspect	PRIMARY KEY	UNIQUE
Purpose	Permanent row identifier	Just "no duplicates"
NULL allowed?	No	Yes (each NULL distinct by default)
How many per table?	One	Many
Auto-creates an index?	Yes (B-tree)	Yes (B-tree)
Foreign keys point at it?	Usually	Possible but unusual
Conventional usage	Surrogate IDs	Natural keys (email, slug)

📌 Index type clarification. Some databases distinguish "clustered" vs "non-clustered" indexes (for example, SQL Server treats the primary key as clustered by default). PostgreSQL doesn't — every index is a separate B-tree, and the primary key just happens to be one of them. Heap order isn't tied to the primary key.

Choosing primary keys well

Surrogate keys (SERIAL, BIGSERIAL, UUID) are simple, stable, and always available. Most applications default to these.
Natural keys (email, iso_country_code) feel meaningful but can change — and changing a primary key cascades through every foreign key referencing it.
For high write rates and distributed systems, UUIDs avoid hot-spotting on a single sequence.
Use BIGSERIAL for tables that may exceed 2 billion rows.

Recap

A primary key uniquely identifies each row — NOT NULL + UNIQUE combined.
One per table; PostgreSQL auto-creates a B-tree index.
Single-column or composite (great for junction tables).
Use SERIAL / GENERATED AS IDENTITY for auto-incrementing surrogate keys.
Different from UNIQUE: PRIMARY KEY forbids NULLs and there's only one per table.