Primary Key

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 primary key column(s), and automatically creates a unique B-tree index to support fast lookups. A table can have at most one primary key.

Defining a primary key

The most common pattern is a single auto-incrementing column defined as a column constraint:

CREATE TABLE orders (
  order_id    SERIAL PRIMARY KEY,
  customer_id VARCHAR(255) NOT NULL,
  order_date  DATE NOT NULL
);

For a composite primary key spanning multiple columns, use a table constraint:

CREATE TABLE order_items (
  order_id         INT,
  item_no          SERIAL,
  item_description VARCHAR NOT NULL,
  quantity         INTEGER NOT NULL,
  price            DEC(10, 2),
  PRIMARY KEY (order_id, item_no)
);

To assign a custom constraint name, use the CONSTRAINT clause:

CONSTRAINT pk_order_items PRIMARY KEY (order_id, item_no)

Adding a primary key to an existing table

If a table was created without a primary key, add one with ALTER TABLE ... ADD PRIMARY KEY:

CREATE TABLE products (
  product_id  INT,
  name        VARCHAR(255) NOT NULL,
  description TEXT,
  price       DEC(10, 2) NOT NULL
);

ALTER TABLE products ADD PRIMARY KEY (product_id);

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

CREATE TABLE vendors (name VARCHAR(255));

INSERT INTO vendors (name)
VALUES ('Microsoft'), ('IBM'), ('Apple'), ('Samsung')
RETURNING *;

ALTER TABLE vendors ADD COLUMN vendor_id SERIAL PRIMARY KEY;

SELECT vendor_id, name FROM vendors;

-- Output:
--  vendor_id |   name
-- -----------+-----------
--          1 | Microsoft
--          2 | IBM
--          3 | Apple
--          4 | Samsung

Dropping a primary key

Remove a primary key constraint using ALTER TABLE ... DROP CONSTRAINT. Find the constraint name first with d table_name:

d vendors
-- Indexes:
--   "vendors_pkey" PRIMARY KEY, btree (vendor_id)

ALTER TABLE vendors DROP CONSTRAINT vendors_pkey;
-- ALTER TABLE

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

Production tips

• Use SERIAL or GENERATED ALWAYS AS IDENTITY for single-column primary keys to let PostgreSQL manage unique values automatically.
• Prefer surrogate integer keys (BIGSERIAL / BIGINT) over natural keys for large tables — natural keys can change, breaking referential integrity.
• Default PostgreSQL primary key constraint names follow the pattern table_pkey. Use the CONSTRAINT clause to assign meaningful names in migrations.
• Adding a primary key to an existing table requires an exclusive lock. On large tables, do this during a maintenance window.
• Foreign keys in other tables must reference a primary key or unique constraint — having a clear primary key strategy makes join queries and referential integrity simpler.