How to add primary key in PostgreSQL

Galaxy Glossary

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

ADD PRIMARY KEY assigns a unique, non-null identifier to each row of a PostgreSQL table.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How to add a primary key in PostgreSQL

Step-by-step instructions for creating or altering tables so that every row is uniquely identified.

What is the fastest way to add a primary key to an existing table?

Use ALTER TABLE ... ADD PRIMARY KEY. PostgreSQL builds a unique index in one transaction, locking the table only while the index is created.

Which syntax options does ALTER TABLE support?

Two forms exist: add an unnamed primary key directly, or add a named constraint with ADD CONSTRAINT ... PRIMARY KEY. Both accept column lists and the USING INDEX clause.

How to create a table with a primary key from the start?

Define the key inline (id SERIAL PRIMARY KEY) or as a table-level constraint (PRIMARY KEY (id)) when running CREATE TABLE.

How do I add a composite primary key?

Supply multiple comma-separated columns in the constraint: ALTER TABLE sales ADD PRIMARY KEY (order_id, product_id);

Can I reuse an existing unique index?

Yes. Pass its name to USING INDEX to avoid rebuilding: ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_email_key;

Best practices for adding primary keys

Verify columns are NOT NULL, ensure no duplicate values, add keys early in design, and prefer surrogate keys when natural keys are wide.

Complete reference syntax

-- Add an unnamed primary key ALTER TABLE table_name ADD PRIMARY KEY (column [, ...]); -- Add a named primary-key constraint ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column [, ...]) [USING INDEX existing_unique_index];

Practical code example

-- 1. Make sure column is NOT NULL ALTER TABLE public.customer ALTER COLUMN customer_id SET NOT NULL; -- 2. Add the primary key ALTER TABLE public.customer ADD CONSTRAINT customer_pkey PRIMARY KEY (customer_id);

Common mistakes and fixes

Adding a key when duplicates exist

PostgreSQL raises ERROR: could not create unique index. Run SELECT col, COUNT(*) FROM tbl GROUP BY col HAVING COUNT(*) > 1; to locate duplicates, de-duplicate, then retry.

Forgetting NOT NULL

Primary keys implicitly require NOT NULL. Set the column accordingly before adding the key: ALTER TABLE ... ALTER COLUMN col SET NOT NULL;

Why How to add primary key in PostgreSQL is important

How to add primary key in PostgreSQL Example Usage


ALTER TABLE employees
  ADD CONSTRAINT employees_pkey PRIMARY KEY (emp_no);

How to add primary key in PostgreSQL Syntax


ALTER TABLE table_name
    ADD [ CONSTRAINT constraint_name ]
    PRIMARY KEY (column_name [, ...])
    [ USING INDEX existing_unique_index ];

Common Mistakes

Trying to add a primary key while duplicate or NULL values exist. Fix by removing duplicates and setting the column to NOT NULL before running ALTER TABLE.
Assuming PostgreSQL makes the column NOT NULL automatically. You must explicitly set NOT NULL or the command fails. Use ALTER TABLE ... ALTER COLUMN col SET NOT NULL first.

Frequently Asked Questions (FAQs)

Is ALTER TABLE ADD PRIMARY KEY blocking?

PostgreSQL must acquire an exclusive lock while creating the unique index. On large tables this can be noticeable. Consider creating a unique index concurrently first, then attach it with USING INDEX to minimize downtime.