In Postgres, Primary keys are used to uniquely identify a table’s record. Users can add/set a primary key at the time of table creation or to an existing table. In Postgres, tables can be created with a primary key constraint using the CREATE TABLE command or altered using the ALTER TABLE command. For dropping a primary key constraint, the DROP CONSTRAINT is used with the ALTER TABLE command.

This Postgres blog will cover the below-listed aspects of the Primary key constraint:

- Adding PRIMARY KEY While Table Creation
- Adding PRIMARY KEY Using ALTER TABLE Command
- Dropping PRIMARY KEY CONSTRAINT

So, let's begin!

Adding PRIMARY KEY While Table Creation

Let’s add a primary constraint during table creation. To do so, we will add a primary key constraint in the staff_information table using the CREATE TABLE command. We will create a sample table with the following columns: st_id, st_name, st_department, and st_age:

CREATE TABLE staff_information(
st_id INT CONSTRAINT st_id_pk PRIMARY KEY,
st_name TEXT,
st_department TEXT,
st_age SMALLINT
);

The “CREATE TABLE” message in the output window indicates that the “staff_information” table has been created. You can verify the table’s creation via the below command:

SELECT * FROM staff_information;

From the table’s structure, you can clearly observe that a primary key constraint has been added to the staff_information table.

Adding PRIMARY KEY Using ALTER TABLE Command

"ALTER TABLE" lets you add primary key constraints to existing Postgres tables. For better understanding, firstly, we will create a table without any constraint:

CREATE TABLE staff_bio(
st_id INT,
st_name TEXT,
st_department TEXT,
st_age SMALLINT
);

Executing the SELECT command will show you the structure of the staff_bio table:

SELECT * FROM staff_bio;

The output snippet proves that the “staff_bio” table has no primary key constraint. Let’s run the ALTER TABLE command to add a PRIMARY KEY constraint in the staff_bio table:

ALTER TABLE staff_bio
ADD CONSTRAINT st_id_pk 
PRIMARY KEY (st_id);

In the above snippet,

-ALTER TABLE is a command used to modify the staff_bio table.
-ADD CONSTRAINT adds a primary key constraint in Postgres, such as “st_id_pk”.

The “ALTER TABLE” message in the output window proves that the “staff_bio” table has been modified successfully. Let’s validate the table’s structure via the following command:

SELECT * FROM staff_bio;

The output shows that a primary key has been added to an existing table: st_id column.

Dropping PRIMARY KEY CONSTRAINT

To drop a primary key constraint, use the ALTER TABLE command with DROP CONSTRAINT as follows:

ALTER TABLE staff_bio
DROP CONSTRAINT st_id_pk

Let’s verify the constraint deletion via the below command:

SELECT * FROM staff_bio;

The output clarifies that the primary key constraint has been removed successfully.

This way, a primary key constraint can be added or deleted from a table in Postgres.

Conclusion

Tables can be created with a primary key constraint using the CREATE TABLE statement. You can add the primary key to an existing table using the Postgres ALTER TABLE command. For dropping a primary key constraint, the DROP CONSTRAINT is used with the ALTER TABLE command. This blog post has explained how to add or drop a primary key constraint in Postgres via practical examples.