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 );
![img](/media/images/image_EQi61Pi.width-1200.png)
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;
![img](/media/images/image_h6Na5tW.width-1200.png)
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 );
![img](/media/images/image_rRsqoeY.width-1200.png)
Executing the SELECT command will show you the structure of the staff_bio table:
SELECT * FROM staff_bio;
![img](/media/images/image_NqHAlaJ.width-1200.png)
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”.
![img](/media/images/image_r7iKODy.width-1200.png)
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;
![img](/media/images/image_sLG4e9o.width-1200.png)
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
![img](/media/images/image_uTzZJXy.width-1200.png)
Let’s verify the constraint deletion via the below command:
SELECT * FROM staff_bio;
![img](/media/images/image_NxEtORe.width-1200.png)
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.