How to ADD PRIMARY KEY in PostgreSQL

Galaxy Glossary

How do I add a primary key in PostgreSQL?

ALTER TABLE … ADD PRIMARY KEY creates a unique, not-null identifier for every row in a table.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why add a PRIMARY KEY?

Primary keys guarantee each row is unique and quickly locatable. They also become the target of foreign-key references, enabling reliable relationships between tables like Customers and Orders.

What’s the basic ALTER TABLE syntax?

Use ALTER TABLE table_name ADD CONSTRAINT pk_name PRIMARY KEY (column[, …]);.PostgreSQL creates a unique B-tree index behind the scenes.

How do I add a primary key to an existing table?

Ensure the column is NOT NULL and contains no duplicates, then run ALTER TABLE Customers ADD PRIMARY KEY (id);. The command fails if duplicate or NULL values exist.

Can I create a composite primary key?

Yes.Combine columns that jointly guarantee uniqueness, e.g., ALTER TABLE OrderItems ADD CONSTRAINT orderitems_pk PRIMARY KEY (order_id, product_id);.

How do I name the constraint?

Always supply a meaningful name: ADD CONSTRAINT customers_pk PRIMARY KEY (id). Named constraints simplify future ALTER TABLE … DROP CONSTRAINT commands.

What precautions should I take before running this in production?

Check for duplicates with SELECT id, COUNT(*) FROM Customers GROUP BY id HAVING COUNT(*) > 1;.Add indexes concurrently on large tables, then attach them with ALTER TABLE … ADD CONSTRAINT … USING INDEX …;.

Best practices for ADD PRIMARY KEY

Use surrogate integer keys, keep key width narrow, and add the constraint early in the table’s life cycle to avoid future data-cleaning work.

Why How to ADD PRIMARY KEY in PostgreSQL is important

How to ADD PRIMARY KEY in PostgreSQL Example Usage


-- Add a primary key to the Orders table
ALTER TABLE Orders
ADD CONSTRAINT orders_pk PRIMARY KEY (id);

-- Composite key for OrderItems
ALTER TABLE OrderItems
ADD CONSTRAINT orderitems_pk 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 (column [, ...]);

-- With CONCURRENTLY built index on large table
CREATE UNIQUE INDEX CONCURRENTLY customers_pk_idx ON Customers(id);
ALTER TABLE Customers ADD CONSTRAINT customers_pk PRIMARY KEY USING INDEX customers_pk_idx;

Common Mistakes

Adding the constraint before removing duplicate or NULL values causes ERROR: could not create unique index. Fix by cleaning data with SELECT … HAVING COUNT(*) > 1 and setting columns NOT NULL first.
Omitting a constraint name lets PostgreSQL auto-generate one, making future ALTER or DROP harder. Always supply a descriptive name like customers_pk.