How to ADD PRIMARY KEY in PostgreSQL

How do I use ALTER TABLE ADD PRIMARY KEY in PostgreSQL?

ALTER TABLE … ADD PRIMARY KEY creates or replaces the unique row identifier constraint on an existing PostgreSQL table.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why add a PRIMARY KEY after creating a table?

Sometimes you import data first and enforce constraints later. ALTER TABLE … ADD PRIMARY KEY lets you add or change the unique row identifier without recreating the table.

What is the basic syntax?

Use ALTER TABLE table_name ADD PRIMARY KEY (column_list);. It builds a unique b-tree index and marks the column list as the table’s primary key.

How do I add a primary key to the "Orders" table?

Run ALTER TABLE "Orders" ADD PRIMARY KEY (id);. PostgreSQL rejects duplicate ids automatically.

Can I create a composite primary key?

Yes. Combine multiple columns: ALTER TABLE "OrderItems" ADD PRIMARY KEY (order_id, product_id);. The pair now uniquely identifies each row.

How do I use an existing unique index?

Save time on big tables: CREATE UNIQUE INDEX idx_orders_id ON "Orders"(id); ALTER TABLE "Orders" ADD PRIMARY KEY USING INDEX idx_orders_id;.

Is the operation blocking?

ALTER TABLE … ADD PRIMARY KEY is blocking in vanilla Postgres. For large tables, consider creating the unique index concurrently, then attach it.

What happens if duplicate rows exist?

The command aborts with ERROR: could not create unique index. Clean up duplicates first with DELETE or SELECT DISTINCT INTO.

Best practices

Always create primary keys on surrogate integer or UUID columns, keep them narrow, and name constraints clearly, e.g., orders_pkey.

Why How to ADD PRIMARY KEY in PostgreSQL is important

How to ADD PRIMARY KEY in PostgreSQL Example Usage


-- Add a composite primary key so each product appears only once per order
ALTER TABLE "OrderItems"
    ADD CONSTRAINT orderitems_pkey PRIMARY KEY (order_id, product_id);

How to ADD PRIMARY KEY in PostgreSQL Syntax


-- Basic
ALTER TABLE table_name
    ADD [CONSTRAINT constraint_name]
    PRIMARY KEY [USING INDEX existing_index]
    (column1 [, column2 ...]);

-- Ecommerce examples
ALTER TABLE "Customers" ADD CONSTRAINT customers_pkey PRIMARY KEY (id);
ALTER TABLE "Orders" ADD PRIMARY KEY USING INDEX idx_orders_id;
ALTER TABLE "OrderItems" ADD PRIMARY KEY (order_id, product_id);

Common Mistakes

Omitting a unique column set. Adding a primary key on a non-unique column causes the command to fail. Fix: deduplicate rows or choose unique columns.
Running ADD PRIMARY KEY on huge tables without planning. The command locks writes and can stall production. Fix: create a UNIQUE INDEX CONCURRENTLY first, then attach.