PostgreSQL Primary key

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:

OR

We can use the second illustration to create more than one primary key column into the table:

In the above syntax, we have used the following parameters, which we discussed in the below table:

Description of Parameters

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.

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.

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.

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.

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:

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:

OR

In the above syntax, we have used the following parameters, which we discussed in the below table:

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.

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:

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.

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.

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:

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:

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.

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:

OR

In the above syntax, we have used the following parameters, which we discussed in the below table:

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:

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.