Postgres stores data in the form of a table. For the real-time data, there is a high chance that the data recorded in the table is redundant. So there must be an attribute that uniquely identifies the data in a database table. The primary key does that job. To uniquely identify the data we need to define a primary key in the table. This is usually done while creating a table and defining the columns. however, we can also add a primary key constraint afterward.
This tutorial will teach us to add a primary key constraint to a column in a Postgres table.
How to Add Primary Key Constraint to a Column in PostgreSQL?
There are several methods we can add a primary key to a column in Postgres. These methods are:
Let’s go over each of these methods one by one.
Method 1: Add the PRIMARY KEY When Creating Table
We usually define the primary key constraint when we create a database table. The base syntax used for this, is:
CREATE TABLE t_name(
col1 Datatype PRIMARY KEY,
Col2 Datatype,
...
coln Datatype
);
The above syntax is simple to understand. You will just have to write the PRIMARY KEY keyword with the column’s name to which you want to associate it. Let’s try getting the concept with the help of an example, in which we will add a primary key when a Postgres table is being created.
Example: Add the PRIMARY KEY When Creating a Table
Let’s create a table named “fee_invoice” with a primary key “st_id”. The query can be written as:
CREATE TABLE fee_invoice(
st_id serial PRIMARY KEY,
st_name INT NOT NULL,
total_fee VARCHAR NOT NULL);
After executing the query, we will see that the table will be created with the primary key.
To verify whether the primary key has been added to the table or not, we will execute the SELECT query:
SELECT * FROM fee_invoice;
The [PK] shows that the column “st_id” has been added as a primary key.
Method 2: Add the PRIMARY KEY to A Pre-Existing Table
We can also add a primary key to a pre-existing table. There are two ways/approaches to do this. These ways are:
- Using Postgres Queries
- Manually Adding the Primary key
Let’s see how we can add/create a PRIMARY KEY to a pre-existing table by using these two approaches.
Approach 1: Using Postgres Queries
We can add a primary key to a pre-existing Postgres table by making use of the ALTER and ADD CONSTRAINT statement in Postgres. Consider the table “fee_invoice”.
It can be clearly observed that the primary key is not defined for the table. So, the query to create a primary key “st_id” in the fee invoice table is:
ALTER TABLE fee_invoice
ADD CONSTRAINT st_id_pk
PRIMARY KEY (st_id);
We have specified the table to be altered with the ALTER TABLE statement. ADD CONSTRAINT adds the primary key constraint to the table and the primary key is added to the “st_id” column. This query will alter the table successfully for the addition of the primary key.
Now, if we want to verify whether the primary key has been added to the column or not, we will execute the SELECT query:
SELECT * FROM fee_invoice;
The output will be the fee_invoice table.
We can see that the primary key has definitely been created/added to the “st_id” column.
Approach 2: Manually Adding the Primary key
To manually add a primary key in the existing table follow the steps given below:
Step 1: Navigate the Table Properties
For an existing table with a column having no primary key, we can add a primary key by a manual method. Go to the table name in which you desire to have, or add the primary key to a column. Right-click on that table and select the “Properties” option from the drop-down menu that appears on the right-click.
A new window will open showing you the properties of the table.
Step 2: Enable Primary Key
Go to the “columns” tab and toggle on the” Primary Key?” button.
Step 3: Name the Primary Key Constraint
Next, go to the “Constraints” tab and add the primary key constraint there. This step is optional. If you do not do this, the constraint is added automatically by PostgreSQL as “table_name_pkey”.
Here, we are adding “st_id_pk” as a primary key constraint:
You can see the automatically generated SQL query in the “SQL” tab. Hit the “save” button in order to add the primary key constraint.
Step 4: Verify the Primary Key Constraint
Now we will verify the addition of the primary key constraint by executing the SELECT query.
We can see that the primary key constraint has been added manually to the Postgres table column.
These are the ways we can add a primary key to a Postgres column.
Conclusion
We can define/add the primary key to a column of a table while creating the table or to a pre-existing table. The ALTER and ADD CONSTRAINT commands are used to add a primary key to a pre-existing table. We can also add a primary key manually to a column in Postgres. In this tutorial, we have learned how to add and define a primary key constraint to a Postgres column by properly implementing different methods.
