PostgreSQL Unique Constraint

In this section, we are going to understand the working of the PostgreSQL UNIQUE constraint, which is used to make sure that all values in a column of a table are exclusive.

Examples of the PostgreSQL Unique constraint, how to create a unique constraint on multiple columns, adding a unique constraint using a unique index, and also see how to drop a unique constraint for the particular table.

What is PostgreSQL Unique key Constraint?

In PostgreSQL, the UNIQUE CONSTRAINT is used to maintain the individuality of the values that we store into a field or a column of the table. It is compatible with a group of column constraints or column constraints and a table constraint.

When we are using the unique constraint, an index on one or more columns generate automatically. If we add two different null values into a column in different rows, it does not interrupt the UNIQUE constraint's specification.

Sometimes, if we want to ensure that values stored in a column or multiple columns are unique across the entire table, then a column cannot store duplicate values.

For example

• The Phone number of clients should be a unique column in the Clients
• The Roll numbers and the Email address should be the unique columns in the Student

When we have used the UNIQUE constraint, every time we insert a new row, it checks if the value is already in the table, rejects the modification and raise an error if the value already exists. And similar steps will be followed while updating the current data.

Why we need to use the Unique constraint in PostgreSQL

• The Unique constraint can include the null value into the table.
• To avoid the two records from storing identical values into the column, the unique constraint is very useful.
• It also works with a foreign key to maintain the uniqueness of a table.
• In PostgreSQL, the unique constraint contains only the different values, which preserve the reliability and the integrity of the database for retrieving the information in an organized way.

Unique constraint VS a primary key

The major difference between unique constraint and primary key are as follows:

How to create a Unique Constraint in PostgreSQL

We can create a PostgreSQL Unique Constraint with the help of the below commands:

• CREATE TABLE command
• ALTER TABLE command

Now, we are discussing both the commands in detail.

Creating a Unique Constraint using a CREATE TABLE command

In PostgreSQL, we can generate a Unique Constraint with the CREATE TABLE command's help.

The syntax for creating a Unique Constraint

The below illustrations are used to display how we can create a Unique Constraint using the CREATE TABLE command:

The first syntax is used to create a Unique Constraint for one column into the table:

OR

We can use the second illustration to create Unique Constraint for multiple columns into the table:

Note: If we do not define a unique constraint name, PostgreSQL automatically creates a name for the particular column. Therefore, it is suggested to use the constraint name while generating a table.

Description of Parameters

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

Example of PostgreSQL Unique Constraint using Create command

To understand the PostgreSQL Unique Constraint's working, we will see the below example.

In the below example, we create a new table called Customers, which contains multiple columns, such as Customer_ID, Customer_name, Address, and email_ID.

And the email_id is the Unique column, which uniquely classifies the email id in the Customer table.

OR

We can use the following command to create the Customers table:

Output

After implementing the above command, we will get the below message window, which displays that the Customers table has been created successfully into the jtp database.

After creating the new table as Customers successfully, we will enter some values into it with the INSERT command's help.

Output

On executing the above command, we will get the following message window, which displays that the four values have been inserted successfully into the Customers table.

Now, we will insert another row with duplicate email_id into the Customers table, as shown in the below statement:

Output

After implementing the above insert command, PostgreSQL issued an error message, which is as follows:

ERROR: duplicate key value violates unique constraint "customers_email_id_key" DETAIL: Key (email_id)=([email protected]) already exists.

Generating a unique constraint on multiple columns

Let us see a sample example to understand the working of the PostgreSQL Unique constraints 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, which help us to generate one new table as Customer_info.

And the combination of values in column Cust_username and Cust_email will be unique across the whole table. The value of the column Cust_username and Cust_email needs not to be unique.

Output

After implementing the above command, we will get the following message window, which displays that the Customer_info table has been created successfully into the Jtp database.

Creating a Unique constraint using an ALTER TABLE command and adding a unique constraint using a unique index

We can generate a Unique constraint in PostgreSQL with the ALTER TABLE command's help, and we can also add the unique constraint using the PostgreSQL unique index.

The syntax for creating a unique constraint

The below illustrations are used to create a Unique constraint with the ALTER TABLE command in PostgreSQL:

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

Example of PostgreSQL Unique constraint using ALTER TABLE command and adding the unique constraint using a unique index

In the below example, we will understand the PostgreSQL Unique constraint working with the ALTER TABLE command and follow the below steps to add the unique constraint using a unique index.

Step1: Creating a new table

Firstly, we will create a new table called Student, which does not contain the Unique column in the table description.

Output

After implementing the above command, we will get the following message window, which displays that the Student table has been created successfully into the Jtp database.

Step2: Create a unique Index

After creating the Student table, we will create a new index based on the Student_email column.

To get more information about the PostgreSQL Unique index refers to the below link:

https://www.tutorialsinfo.com/postgresql-unique-index

Output

After executing the above command, we will get the below message window displaying that the particular index has been created successfully.

Step3: Add the unique constraint to the Specified table

After creating the Student_Student_email index successfully, we are going to add a unique constraint to the Student table using the ALTER TABLE command's help, as shown in the following statement:

Output

On executing the above command, we will get the below messages window: The Student table has been altered successfully.

In the above screenshot, we can see that the PostgreSQL raise a notice, which says that the ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "student_student_email" to "unique_student_email".

Note:

• The ALTER TABLEcommand obtains an exclusive lock on the table. If we have any incomplete transactions, it will wait for all transactions to complete before altering the table.
• To see the existing incomplete transactions, we should check the pg_stat_activity table with the help of the below command:

Output

After implementing the above command, we will get the below output, where we can find the State column with the value idle and active in the transaction. And the idle state defines that these are incomplete transactions.

How to Drop the PostgreSQL Unique Constraint

In PostgreSQL, we can remove the unique constraint with the help of the ALTER TABLE command.

The syntax for dropping a unique constraint

The below illustrations are used to remove a unique constraint with the ALTER TABLE command in PostgreSQL:

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

Example of drop the PostgreSQL unique constraint using an ALTER TABLE command

Let's us see a sample example of removing the unique constraint from the particular table.

For this, we are taking the Customer_info table to remove the unique constraint 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 unique constraint (uc_Cust_username_Cust_email) has been dropped from the Customer_info table.

Overview

In the PostgreSQL Unique Constraint section, we have learned the following topics:

• We have used the CREATE TABLE command to create a Unique Constraint for the particular table.
• We have understood the concept of altering the PostgreSQL unique constraint using the CREATE TABLE
• We added the unique constraint using the PostgreSQL unique index to the existing table.
• We have used the ALTER TABLE command to drop the unique constraint from the particular table.