In this section, we are going to understand the working of the PostgreSQL Primary Key, the examples of the PostgreSQL primary key, and how to manage PostgreSQL primary key constraints over SQL commands.
And also, understand how to add an auto-incremented (SERIAL)primary key to a current table and drop a primary key.
What is PostgreSQL’s Primary key?
It is a field in a table that individually identifies each row or the record in a database table, and it contains a unique value.
A primary key does not hold any null value. And for this, we can also say that the primary key is collecting the unique and not-null constraint of a table. If the column has a primary key constraint, then it cannot be null or empty.
It is used to identify each record in a database table distinctively. We can contain other unique columns, but we have only one primary key in a database table including single or multiple fields.
It is the most crucial key while creating the database tables, and it could be a unique ID. It can be signified for one column or group of columns.
The working of the primary key is similar to a unique constraint. Still, the significant difference between primary key and unique constraint is that the one table can have only one primary key; however, the table can have one or more unique and not-null constraints.
When we insert a new row into the table, then the primary key column can also use the SERIAL(Auto-increment) feature to create the next number for the particular row automatically.
In other words, we can say that the PostgreSQL primary key is only field or grouping of fields, which specify the data uniquely. And none of the fields that are part of the primary key can have the NULL value.
Whenever a primary key is added to the table, the PostgreSQL creates a unique B-tree index on the group of columns or a column, which describes the primary key.
Rules for Primary key
If we are working on the PostgreSQL Primary key, we should follow the below rules:
- The primary key column cannot contain a null or empty value.
- The primary key column value must be unique.
- Each table can have only one primary key.
- If we are using the primary key, we should use INT or BIGINT data type as it is recommended.
How to create a primary key in PostgreSQL
In PostgreSQL, we can create a primary key with the help of the following commands:
- CREATE TABLE command
- ALTER TABLE command
Note: In PostgreSQL, The ALTER TABLE command is used to add or drop a primary key.
Now, we are discussing both the commands in detail.
Creating a Primary Key using a CREATE TABLE command
We can generate a primary key in PostgreSQL with the CREATE TABLE command’s help.
We can generally add the primary key to a table when we are specifying the table’s structure with the CREATE TABLE command.
The syntax for creating a primary key using a create table statement
The below illustrations are used to display how we can create a primary key using the CREATE TABLE command:
The first syntax is used to create only one primary key column into the table:
CREATE TABLE table_name
(
column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
column2 datatype [ NULL | NOT NULL ],
...
);OR
We can use the second illustration to create more than one primary key column into the table:
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
CONSTRAINT constraint_name
PRIMARY KEY (column_name(s))
);In the above syntax, we have used the following parameters, which we discussed in the below table:
Description of Parameters
| Parameter Name | Description |
|---|---|
| Table_name | It is the name of a table which we are going to create. |
| column1, column2 | These are columns that we created in the table. |
| constraint_name | The constraint_name is used to specify the name of the primary key. |
| Column_name(s) | These are the columns that are going to be a primary key. |
Example of PostgreSQL Primary Key using Create command
To understand the PostgreSQL Primary key’s working, we will see the below example, which describes how a primary key is used in PostgreSQL.
In the below example, we create a new table called Applicant, which contains the four columns, such as applicant_Id, applicant_username, applicant_password, and applicant_email.
And the applicant_id is the primary key, which uniquely classifies the applicant id in the Applicant table.
CREATE TABLE Applicant (
applicant_Id INT SERIAL PRIMARY KEY,
applicant_username VARCHAR(50),
applicant_password VARCHAR(30),
applicant_email VARCHAR(55));Output
After implementing the above command, we will get the below message window, which displays that the Applicant table has been created successfully into the Organization database.
After creating the new table as Applicant successfully, we will enter some values into it with the INSERT command’s help.
INSERT INTO Applicant(applicant_id, applicant_username, applicant_password, applicant_email)
VALUES (101,'Mike Ross', 'Mikeross@', '[email protected]'),
(102, 'John Smith', 'Smith#john', '[email protected]');Output
After implementing the above command, we will get the following message window, which displays that the two values have been inserted successfully into the Applicant table.
As we can see in the above screenshot, the first insert command will be executed because we have used the unique value for the primary key columns.
But if we want to insert one more value into the Applicant table using the same applicant_id value as 101, then PostgreSQL will issue an error.
INSERT INTO Applicant(applicant_id, applicant_username,
applicant_password, applicant_email)
VALUES (101,'Ava jane', '1234@56', '[email protected]');Output
After executing the above insert command, we will get the following error: The Duplicate key value violates unique constraint “applicant_pkey” as applicant_id =101 values already exist.
Defining the primary key on multiple columns
Let us see a sample example to understand the working of the PostgreSQL primary key on multiple columns.
Suppose we want to specify the primary key on multiple columns; In that case we can see the below Create command example where we generate one new table as Trainee whose primary key is a combination of trainee_ID and trainee_roll_no.
CREATE TABLE Trainee (
trainee_ID int,
trainee_roll_No int,
trainee_name varchar(45) NOT NULL,
trainee_Age int,
trainee_location varchar(25),
Primary Key(trainee_ID, trainee_roll_No)
);Output
After implementing the above command, we will get the following message window, which displays that the Trainee table has been created successfully into the Organization database.
Note: The PostgreSQL uses table-name_pkey as the default name for the primary key constraint by default.
In the above example, PostgreSQL creates the primary key constraint as trainee_pkey for the Trainee table.
We can use the CONSTRAINT clause if we want to define the primary key constraint’s name, as shown in the following command:
CONSTRAINT constraint_name PRIMARY KEY(column_1, column_2,...); Creating a Primary Key using an ALTER TABLE command
We can generate a primary key in PostgreSQL with the ALTER TABLE command’s help.
As it is very rare to specify a primary key for an existing table, for adding a primary key constraint, we can use the ALTER TABLE command.
The syntax for creating a primary key using a alter table statement
The below illustrations are used to create a primary key with the ALTER TABLE command in PostgreSQL:
ALTER TABLE table_name
ADD PRIMARY KEY(column_list);ALTER TABLE table_name
ADD CONSTRAINT [ constraint_name ]
PRIMARY KEY (index_col1, index_col2, ... index_col_n) In the above syntax, we have used the following parameters, which we discussed in the below table:
| Parameter Name | Description |
|---|---|
| Table_name | It is the name of a table, which we are going to modify. |
| Column_list | These are columns, which we created in the table. |
| constraint_name | It is used to define the primary key’s name. |
| index_col1, index_col2, … index_col_n | These are columns that are used to frame the primary key. |
Example of PostgreSQL Primary Key using ALTER TABLE command
To understand the PostgreSQL Primary key’s working with ALTER TABLE command, we will see the below example.
In the below example, we create a new table called Customers, which does not contain the primary key column in the table descriptions.
CREATE TABLE Customers (
Customer_ID int NOT NULL,
Customer_Name varchar(45),
Customer_Age int,
Customer_Address varchar(25)
);Output
After implementing the above command, we will get the following message window, which displays that the Customers table has been created successfully into the Organization table.
If we want to add a primary key constraint to the Customers table, we can use the below ALTER TABLE command:
ALTER TABLE Customers
ADD PRIMARY KEY (Customer_ID); Output
After executing the above command, we will get the below message window displaying that the Customers table has been altered successfully into a similar database.
After understanding the working of PostgreSQL primary key using CREATE TABLE and ALTER TABLE command, we will try to add an auto-incremented primary key to the current table.
How to add an auto-incremented primary key to the current table
Let’s see a sample example of adding an auto-incremented primary key to the current table.
Assume that we have created one table as Seller with the CREATE table command’s help, and the particular table does not contain any primary key:
CREATE TABLE Seller(
Name VARCHAR(255)); Output
After executing the above command, we will retrieve one message window, which displays that the Seller table has been created successfully into the Organization database.
After creating the Seller table successfully, we will enter some values into it with the INSERT command’s help.
INSERT INTO Seller (NAME)
VALUES
('Apple iPhone'),
('Google),
('Samsung');Output
After implementing the above command, we will get the following message window, which displays that the three values have been inserted successfully into the Seller table.
After creating and inserting the Seller table’s values, we will use the SELECT command to retrieve the data from the Seller table:
SELECT *FROM Seller; Output
After successfully implementing the above command, we will get the below result: The PostgreSQL returns the data present in the Seller table:
If we want to add one new column as Seller_id into the Seller table and, which is also a primary key column.
And we use the below command, where the Seller_id column is auto-incremented by one, as shown in the following example:
ALTER TABLE Seller
ADD COLUMN Seller_id SERIAL PRIMARY KEY; Output
After executing the above command, we will get the following message window, which displays that the Seller table has been altered successfully.
After altering the Seller table, we will use the select command again to verify the particular table’s modification.
SELECT Seller_id, Name
FROM Seller;Output
We will get the following output on implementing the above command, which displays modification occurred into the Seller table successfully.
How to remove the PostgreSQL primary key
In PostgreSQL, we can remove the primary key with the help of the ALTER TABLE command.
The syntax for dropping a primary key using a alter table command
The below illustrations are used to remove a primary key with the ALTER TABLE command in PostgreSQL:
ALTER TABLE table_name
DROP PRIMARY KEY; ALTER TABLE table_name
DROP CONSTRAINT primary_key_constraint; In the above syntax, we have used the following parameters, which we discussed in the below table:
| Parameter | Description |
|---|---|
| table_name | The table name parameter is used to specify the table’s name, which needs to be modified. |
| constraint_name | The constraint name parameter is used to define the primary key’s name, which we want to remove. |
Example of removing the PostgreSQL primary key using an ALTER TABLE command
Lets us see a sample example of removing the primary key from the defined table.
For this, we are taking the Applicant table to remove the primary key with the ALTER TABLE command’s help, as shown in the below statement:
ALTER TABLE Applicant
DROP CONSTRAINT Applicant_pkey;Output
After successfully implementing the above command, we will get the below message window displaying that the primary key has been removed from the Applicant table.
Overview
In the PostgreSQL Primary key section, we have learned the following topics:
- We have used the CREATE TABLE command to create a primary key for the particular table.
- We have understood the concept of altering the PostgreSQL primary key using the CREATE TABLE
- We added an auto-incremented primary key to the existing table.
- We have used the ALTER TABLE command to drop the Primary key from the particular table.
Leave a Reply