The primary key is used to uniquely identify a record or we can define the primary key as the the column that is used to uniquely identify a row. A database table must have a single primary key. It is a better approach to have a primary key per table while creating a table. Usually, primary keys are declared when creating a table. However, we can also change afterward if the need arises.

This write-up will extensively elaborate on the method to change the primary key so let’s get started.

How to Change Primary Key in Postgres?

We can change the already declared primary key for a database table in Postgres. But let’s start from the very beginning by creating a table and declaring the primary key there.

Creating a Table and Declaring the Primary key

Let's create a table named “Students_scholarships” and declare the student_id as the primary key which uniquely identifies each student in the table. The query for this will be:

CREATE TABLE Students_scholarships (
Student_id SERIAL PRIMARY KEY,
Student_name VARCHAR(100) NOT NULL,
Scholarship_amount DOUBLE PRECISION NOT NULL
);

Here we have declared the primary key as “student_id” so Postgres will store it as the primary key for the table “Students_scholarships”. If we do not specify the primary key Postgres will consider “table-name_pkey” by default.

Now if we want to change the already declared primary key we will follow the following steps:

Step 1: Remove the Primary Key Attribute

The first step is to remove the primary key attribute from the previous/former primary key. This can be done by using the following syntax:

ALTER TABLE   <tab_name> DROP CONSTRAINT   <tab_name>_pkey;

For our particular use case, the query will become:

ALTER TABLE Students_scholarships DROP CONSTRAINT Students_scholarships_pkey;

By running this query, the table will successfully be altered for the removal of the primary key.

Also by running the select query, we can see that the “student_id” is no longer a primary key. For reference, you can look at this.

The next step is to change the name of the primary key and the candidate properly.

Step 2: Set a New Primary Key

After removing the previous primary key from a table, we will now assign a new primary key to the table. The basic syntax of the query looks like this:

ALTER TABLE <tab_name> ADD PRIMARY KEY (new_primary_key);

In the above syntax:

● We will alter the table by specifying the table name.

● We will alter the table for a new primary key by specifying the name of the new primary key after the ADD PRIMARY KEY keyword.

Let’s execute this in our use case. Our query will look like this:

ALTER TABLE Students_scholarships ADD PRIMARY KEY (Student_name);

The query says that the table “students_scholarships” is altered and we have added, “student_name” as the primary key. By executing this query, we will successfully alter our table for the change.

Now let’s select the table to see if the primary key change has been reflected on the table or not. Run the select query like this:

SELECT * FROM Students_scholarships;

The table is returned by the query which looks like this:

We can clearly see that the primary key for the table has been changed. So this is how we can change a primary key for an existing table.

There is a one-step query doing the same thing. The basic syntax for this query can be written as:

ALTER TABLE <tab_name>
 DROP CONSTRAINT <tab_name>_pkey CASCADE,
 ADD PRIMARY KEY(new_primary_key);

In this above query, the table is altered to drop the constraint as a primary key and add a new primary key in the table in a single query. The query for our use case will be:

ALTER TABLE Students_scholarships
 DROP CONSTRAINT Students_scholarships_pkey CASCADE,
 ADD PRIMARY KEY(Student_name);

The query will be successfully altered as:

To select the table we will write the select query:

We can use any of the queries to do the same thing as they both are doing the same function. This is all about the way to change the existing primary key in Postgres.

Conclusion

To change a Primary key in PostgreSQL, first, you need to remove the existing primary key from the table. After that, a new primary key can be added to the desired column using the “ALTER TABLE ADD PRIMARY KEY” statement. The main purpose of primary keys is that they are used to uniquely identify each record/row. But sometimes we need to change the existing primary key for the Postgres table. This can be done by altering the table to first drop the existing primary key and then by adding a new primary key of your choice. In this article, we learned about the method to change the primary key in an effective way using an example.