PostgreSQL – Make an Existing Column a PRIMARY KEY

You can make an existing PostgreSQL table column a PRIMARY KEY by using the ALTER TABLE statement with an ADD CONSTRAINT clause.

Before PostgreSQL can add the primary key, every value in the selected column must be unique and non-null. PostgreSQL checks the existing rows when the constraint is created and rejects the statement if duplicate or null values are present.

PostgreSQL Syntax for Adding a PRIMARY KEY to an Existing Column

</>
Copy
ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

In this syntax:

  • table_name is the table that contains the existing column.
  • constraint_name is the name assigned to the primary key constraint.
  • column_name is the column that should become the primary key.

Make the Existing students.id Column a PRIMARY KEY

Consider the following table named students. No column in this table is marked PRIMARY KEY. Now, we will make the column id as PRIMARY KEY.

PostgreSQL DELETE query example

The SQL query to make column id of table students a PRIMARY KEY is:

</>
Copy
 ALTER TABLE students
	ADD CONSTRAINT students_pk PRIMARY KEY (id);

The statement creates a primary key constraint named students_pk on the existing id column. PostgreSQL also creates the unique index required to enforce the primary key.

PostgreSQL - Make an existing column as PRIMARY KEY

Verify the New PRIMARY KEY in PostgreSQL

When you describe the table with \d tablename command as shown below, there is a constraint added with id as PRIMARY KEY.

For the students table, run the following command in the psql terminal:

</>
Copy
\d students
PostgreSQL - Make an existing column as PRIMARY KEY

The table description should list students_pk under the indexes or constraints and identify id as the primary key.

Check the Column Before Adding the PostgreSQL PRIMARY KEY

The existing column must satisfy these conditions before it can become a primary key:

  • Every row must have a value in the column.
  • The column must not contain duplicate values.
  • The table must not already have another primary key.
  • The chosen column should identify each row consistently.

Use the following query to find duplicate values in the proposed primary key column:

</>
Copy
SELECT id, COUNT(*)
FROM students
GROUP BY id
HAVING COUNT(*) > 1;

If this query returns rows, the duplicate values must be corrected before adding the primary key.

Use this query to check for null values:

</>
Copy
SELECT *
FROM students
WHERE id IS NULL;

If null rows are returned, assign valid unique values to them before running the ALTER TABLE statement.

Why PostgreSQL Rejects an Existing Column as PRIMARY KEY

PostgreSQL may reject the primary key statement for one of the following reasons:

  • Duplicate values: Two or more rows contain the same value in the selected column.
  • Null values: At least one row contains NULL in the selected column.
  • Existing primary key: The table already has a primary key constraint.
  • Invalid constraint name: Another object in the schema may already use the chosen constraint or index name.
  • Dependent data problems: Existing application logic or foreign-key relationships may require review before changing the table structure.

Add a Composite PRIMARY KEY to Existing PostgreSQL Columns

If one column does not uniquely identify a row, PostgreSQL can create a composite primary key from two or more existing columns.

</>
Copy
ALTER TABLE enrollments
ADD CONSTRAINT enrollments_pk
PRIMARY KEY (student_id, course_id);

In this example, the combination of student_id and course_id must be unique. Both columns are treated as non-null after the primary key is added.

Remove the PRIMARY KEY Constraint from the PostgreSQL Table

To remove the primary key later, drop the constraint by its name:

</>
Copy
ALTER TABLE students
DROP CONSTRAINT students_pk;

Dropping the primary key removes the constraint and its supporting index. Review any foreign keys that depend on the primary key before running this statement.

PostgreSQL PRIMARY KEY Migration Checklist

  • Confirm the intended column contains no duplicate values.
  • Confirm the intended column contains no null values.
  • Check whether the table already has a primary key.
  • Choose a clear constraint name such as students_pk.
  • Review foreign-key dependencies before changing a production table.
  • Run the change during an appropriate maintenance period for a large or frequently updated table.
  • Verify the result with \d table_name or a schema inspection tool.

PostgreSQL Existing Column PRIMARY KEY FAQs

Does PostgreSQL automatically set the column to NOT NULL?

Yes. A primary key requires non-null values. When the constraint is added successfully, PostgreSQL enforces the non-null requirement for the primary key column.

Can a PostgreSQL table have more than one primary key?

No. A table can have only one primary key constraint. However, that primary key can contain multiple columns as a composite primary key.

Does adding a PRIMARY KEY create an index in PostgreSQL?

Yes. PostgreSQL creates a unique B-tree index by default to enforce the primary key constraint.

Can an existing UNIQUE column become a PRIMARY KEY?

Yes, provided the column contains no null values and the table does not already have a primary key. PostgreSQL still creates and manages the primary key constraint according to the statement used.

PostgreSQL PRIMARY KEY on an Existing Column Summary

In this PostgreSQL Tutorial, we used ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY to make an existing column a primary key. Before applying the constraint, verify that the column contains unique, non-null values and that the table does not already have another primary key.