Primary Key in PostgreSQL

A primary key uniquely identifies every row in a table. PostgreSQL enforces this by combining a NOT NULL constraint and a UNIQUE constraint on the key column(s), and automatically creates a unique B-tree index to support fast lookups. A table can have at most one primary key, though that key may span multiple columns.

Syntax

Single-column primary key defined as a column constraint:

CREATE TABLE table_name (
  id SERIAL PRIMARY KEY,
  -- other columns
);

Composite primary key defined as a table constraint (required when more than one column is involved):

CREATE TABLE table_name (
  col1 data_type,
  col2 data_type,
  PRIMARY KEY (col1, col2)
);

To assign a custom constraint name use the CONSTRAINT clause:

CONSTRAINT pk_orders PRIMARY KEY (order_id)

Practical Example

Create an events table with a surrogate primary key and a line_items junction table with a composite key:

CREATE TABLE venues (
  venue_id   SERIAL PRIMARY KEY,
  venue_name VARCHAR(200) NOT NULL,
  city       VARCHAR(100) NOT NULL
);

CREATE TABLE events (
  event_id   SERIAL PRIMARY KEY,
  title      VARCHAR(200) NOT NULL,
  venue_id   INT NOT NULL REFERENCES venues (venue_id),
  event_date DATE NOT NULL
);

CREATE TABLE event_line_items (
  event_id   INT NOT NULL,
  item_no    INT NOT NULL,
  description VARCHAR(300) NOT NULL,
  quantity   INT NOT NULL DEFAULT 1,
  unit_price NUMERIC(10, 2) NOT NULL,
  PRIMARY KEY (event_id, item_no)
);

Inspect the constraint and auto-created index on events:

\d events

-- Indexes:
--   "events_pkey" PRIMARY KEY, btree (event_id)

Add a primary key to a table created without one:

CREATE TABLE venues_staging (
  venue_id   INT,
  venue_name VARCHAR(200) NOT NULL
);

ALTER TABLE venues_staging ADD PRIMARY KEY (venue_id);

To add an auto-incrementing primary key column to a table that already has data:

ALTER TABLE venues_staging
  ADD COLUMN new_id SERIAL PRIMARY KEY;

Dropping a primary key — find the constraint name first, then drop it:

\d venues_staging
-- Indexes:
--   "venues_staging_pkey" PRIMARY KEY, btree (venue_id)

ALTER TABLE venues_staging DROP CONSTRAINT venues_staging_pkey;

Dropping the constraint removes the index but does not drop the column.

Composite Primary Keys vs Surrogate Keys

For most tables, a surrogate BIGINT GENERATED ALWAYS AS IDENTITY is the safest choice. Use composite keys for pure junction/mapping tables (many-to-many relationships) where the combination of two foreign keys is the natural identity.

Testing with Vela

Primary key changes — especially adding a key to an existing table or converting a natural key to a surrogate — carry real migration risk. Vela’s database branching lets you clone your production database in seconds, run the ALTER TABLE migration on the branch, verify that all dependent foreign key constraints still resolve correctly, and run your integration tests before touching production. This eliminates the guesswork of “does my backfill query cover every row?” in live environments.

Production Tips

• Use BIGSERIAL or BIGINT GENERATED ALWAYS AS IDENTITY instead of SERIAL (INT) for primary keys on tables expected to grow large — 32-bit integers overflow at ~2 billion rows.
• Default PostgreSQL constraint names follow the pattern table_pkey. Use the CONSTRAINT clause to assign meaningful names in migration files.
• Adding a primary key to an existing table acquires an ACCESS EXCLUSIVE lock and validates all rows. Run this during a maintenance window for large tables.
• Dropping a primary key does not drop the column — it only removes the constraint and its backing index. Foreign keys in child tables that reference the primary key must be dropped first.
• Foreign keys in other tables must reference either a primary key or a unique constraint. A clear primary key strategy makes join queries and referential integrity simpler to reason about.
• Use \d table_name or query information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY' to introspect primary key definitions programmatically.