PostgresqlHow-ToBeginner · 3 min read

How to Add Primary Key in PostgreSQL: Syntax and Examples

To add a PRIMARY KEY in PostgreSQL, use the ALTER TABLE statement with ADD PRIMARY KEY (column_name). This sets the specified column as the unique identifier for each row in the table.

📐

Syntax

The basic syntax to add a primary key to an existing table is:

ALTER TABLE table_name: Specifies the table to modify.
ADD PRIMARY KEY (column_name): Defines the column to be the primary key.

You can add a primary key on one or more columns by listing them inside the parentheses separated by commas.

sql

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

💻

Example

This example shows how to add a primary key to a table named employees on the id column.

sql

CREATE TABLE employees (
    id INT,
    name VARCHAR(100),
    department VARCHAR(50)
);

ALTER TABLE employees
ADD PRIMARY KEY (id);

Output

CREATE TABLE ALTER TABLE

⚠️

Common Pitfalls

Common mistakes when adding a primary key include:

Trying to add a primary key on a column that contains duplicate or NULL values, which will cause an error.
Not specifying the correct table or column name.
Forgetting that a table can only have one primary key constraint.

Always ensure the column values are unique and not NULL before adding a primary key.

sql

/* Wrong: duplicates or NULLs present */
-- ALTER TABLE employees ADD PRIMARY KEY (department); -- Error if duplicates exist

/* Right: unique column */
ALTER TABLE employees ADD PRIMARY KEY (id);

📊

Quick Reference

Command	Description
ALTER TABLE table_name ADD PRIMARY KEY (column_name);	Add primary key to existing table column
CREATE TABLE table_name (id INT PRIMARY KEY, ...);	Define primary key when creating table
ALTER TABLE table_name DROP CONSTRAINT constraint_name;	Remove existing primary key constraint
SELECT conname FROM pg_constraint WHERE contype = 'p';	List primary key constraints in database

✅

Key Takeaways

Use ALTER TABLE with ADD PRIMARY KEY to set a primary key on existing tables.

Primary key columns must have unique and non-null values.

A table can have only one primary key constraint.

Check for duplicates before adding a primary key to avoid errors.

You can define a primary key when creating a table or add it later.