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:
| Unique Constraint | Primary Key |
|---|---|
| Some of the fields related to the unique constraint can have the null values if the combination of values are unique. | None of the fields which are related to the primary key can include a null value. |
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:
CREATE TABLE table_name(
col1 datatype,
col2 datatype UNIQUE,
...
); OR
We can use the second illustration to create Unique Constraint for multiple columns into the table:
CREATE TABLE table_name(
col1 col_definition,
col2 col_definition,
...
[CONSTRAINT constraint_name]
UNIQUE(column_name(s))
);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:
| 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 parameter is used to specify the name of the Unique Constraint. |
| Column_name(s) | These are the columns that are going to be a Unique Constraint. |
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.
CREATE TABLE Customers (
Customer_id SERIAL PRIMARY KEY,
Customer_name VARCHAR (25),
Address VARCHAR(25),
email_id VARCHAR (50),
UNIQUE (email_id)
);OR
We can use the following command to create the Customers table:
CREATE TABLE Customers (
Customer_id SERIAL PRIMARY KEY,
Customer_name VARCHAR (25),
Address VARCHAR(25),
email_id VARCHAR (50) UNIQUE
);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.
INSERT INTO Customers(Customer_name, Address, email_id)
VALUES ('Chris Johnson', 'Los Angeles', '[email protected]'),
(' Daniel Rodriguez', 'San Diego', '[email protected]'),
('Isabella Smith', 'Dallas', '[email protected]'),
('Penelope Brown', 'Seattle', '[email protected]'); 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:
NSERT INTO Customers(Customer_name, Address, email_id)
VALUES ( 'Savannah Smith', 'Boston', '[email protected]');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.
CREATE TABLE Customer_info (
Cust_ID SERIAL,
Cust_Username VARCHAR(45),
Cust_name VARCHAR (50) NOT NULL,
Cust_email VARCHAR (50),
Cust_address VARCHAR (30),
CONSTRAINT uc_Cust_username_Cust_email
Unique(Cust_username, Cust_email)
);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:
ALTER TABLE table_name
ADD CONSTRAINT [ constraint_name ]
UNIQUE(column_list); 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 unique constraint name. |
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.
CREATE TABLE Student (
Student_ID INT NOT NULL,
First_name VARCHAR(45),
Last_name VARCHAR(50),
Student_Age INT,
Student_email VARCHAR(25) ,
Student_roll_no INT
); 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:
CREATE UNIQUE INDEX CONCURRENTLY Student_Student_email
ON Student(Student_email); 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:
ALTER TABLE Student
ADD CONSTRAINT unique_Student_email
UNIQUE USING INDEX Student_Student_email; 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:
SELECT
datid, datname, usename, state
FROM pg_stat_activity; 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:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name; 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 unique constraint name, which we want to remove. |
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:
ALTER TABLE Customer_info
DROP CONSTRAINT uc_Cust_username_Cust_email; 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.
Leave a Reply