Primary Keys

A primary key is the column, or set of columns, that uniquely identifies each row in a table. Every other part of a relational schema leans on this guarantee — foreign keys reference a primary key, ORMs assume one exists, and replication and indexing machinery use it as the natural way to address a specific row. Declaring one is usually the very first thing you do after naming a table's columns.

What PRIMARY KEY actually enforces

PRIMARY KEY is really shorthand for two constraints applied together: NOT NULL and UNIQUE. A row can never have a null primary key value, and no two rows can share the same value. PostgreSQL also automatically builds a unique B-tree index on the primary key column(s), which is why primary-key lookups are fast without any extra work.

Declaring a primary key

SQL

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  email       TEXT UNIQUE NOT NULL
);

-- Equivalent to writing this out explicitly:
CREATE TABLE customers_explicit (
  customer_id INTEGER NOT NULL,
  name        TEXT NOT NULL,
  email       TEXT UNIQUE NOT NULL,
  CONSTRAINT customers_explicit_pkey PRIMARY KEY (customer_id)
);

Composite primary keys

Sometimes uniqueness only makes sense across a combination of columns rather than one alone — a classic case is a junction table for a many-to-many relationship, where the pair of foreign keys together identifies the row. A composite primary key is declared with a table-level CONSTRAINT listing more than one column.

A composite primary key on a junction table

SQL

CREATE TABLE product_tags (
  product_id INTEGER NOT NULL REFERENCES products (product_id),
  tag_id     INTEGER NOT NULL REFERENCES tags (tag_id),
  PRIMARY KEY (product_id, tag_id)
);

This says a given (product_id, tag_id) pair can appear at most once — a product cannot be tagged with the same tag twice — while still allowing product_id and tag_id to each repeat individually across many rows.

Natural keys vs. surrogate keys

A natural key is a column that already carries real-world meaning — an email address, a national ID number, a product's SKU. A surrogate key is a value invented purely to identify the row, with no meaning outside the database, generated with SERIAL or IDENTITY (covered in depth on the SERIAL & Identity Columns page) or with a UUID.

	Natural key	Surrogate key
Source of value	Real-world data (email, SKU, SSN)	Database-generated (identity column, UUID)
Meaning outside the DB	Yes	None
Stability over time	Can change (a person changes email)	Never needs to change
Risk	Business rules evolve; the "unique" fact may stop holding	None inherent to the value itself
Join/index performance	Depends on type (text keys are heavier)	Typically small, fixed-width, fast

The trouble with natural keys is that they can turn out to be less permanently unique than they first seemed — companies merge and reissue SKUs, people change emails, government ID formats change. Once a natural key is baked in as a primary key and referenced by foreign keys throughout a schema, changing it later is disruptive.

Default to a surrogate key

For most application schemas, a generated surrogate key — an identity column or a UUID — is the pragmatic default. It is guaranteed stable, cheap to index and join on, and immune to changes in business rules about what counts as unique. Reserve natural keys for cases where the real-world value is genuinely permanent and you specifically want the database to enforce its uniqueness (an ISBN, for instance), and even then it is common to add a UNIQUE constraint on the natural key while still using a surrogate as the primary key.

A table can have only one primary key

A table may declare at most one PRIMARY KEY constraint (which can span multiple columns), but any number of additional UNIQUE constraints. If a table needs to enforce uniqueness on more than one independent column or column set, express the extras as UNIQUE rather than trying to have two primary keys.

PRIMARY KEY = NOT NULL + UNIQUE, plus an automatic index, on the identifying column(s).
A composite primary key spans multiple columns, common on many-to-many junction tables.
Natural keys carry real-world meaning; surrogate keys are invented purely to identify the row.
Surrogate keys (identity columns or UUIDs) are the safer default for most schemas.