In PostgreSQL, we can enforce the uniqueness of values in one or more columns to ensure that no duplicates are stored across the table. We can achieve this by adding the UNIQUE constraint across multiple columns.
In this guide, you will learn how to implement the UNIQUE constraint on multiple table columns in PostgreSQL.
Requirements:
To follow along with this tutorial, ensure that you have the following:
- Installed PostgreSQL server on your system
- Basic SQL and PostgreSQL knowledge
PostgreSQL UNIQUE Constraint
The UNIQUE constraint in PostgreSQL ensures the uniqueness of the data at the column or record level. This means that you can’t have two rows in the table with the same values in the unique columns.
When we apply a UNIQUE constraint to a group of columns, PostgreSQL enforces the uniqueness of the combined values of these columns.
Connect to the Target Database
Let us start by connecting to the target database on which we wish to create a table with multiple unique constraints.
You can use any client of your choice including pgAdmin or PSQL.
UNIQUE Constraint on Multiple Columns
The first and most recommended method is defining the unique constraint during the table creation. This ensures that all the data that are added to the table adheres to the rules of the constraint which ensures the data consistency.
Let us create a sample table with multiple unique columns.
id serial PRIMARY KEY,
first_name VARCHAR (50),
last_name VARCHAR (50),
email VARCHAR (255),
UNIQUE (first_name, last_name)
);
In the given example query, we create an employee table with four columns: id, first_name, last_name, and email. The combination of the first_name and last_name must be unique across the entire employees’ table as defined by the UNIQUE constraint.
Add the UNIQUE Constraint to an Existing Table
We can also add a unique constraint on multiple columns of an existing table using the ALTER TABLE command.
For example, suppose we wish to add the UNIQUE constraint to the employees’ table. We can run the query as follows:
ADD CONSTRAINT unique_name UNIQUE (first_name, last_name);
The given command should add a unique constraint called “unique_name” to the employees’ table which ensures that the combination of the “first_name” and “last_name” columns is unique across all the table rows.
We can test this by adding the rows with duplicate records as shown in the following entries:
VALUES ('Ann', 'Doe', '[email protected]'),
('Jane', 'Doe', '[email protected]'),
('Ann', 'Doe', '[email protected]');
If we run the previous query, the third insert statement should return an error as follows:
ERROR: Key (first_name, last_name)=(Ann, Doe) already exists.duplicate key value violates unique constraint “employees_first_name_last_name_key”
ERROR: duplicate key value violates unique constraint “employees_first_name_last_name_key” SQL state: 23505
This is because the combination of Ann and Doe is already present in the table. Thus, PostgreSQL prevents this row from being inserted due to the violation of the unique constraint.
Remove the UNIQUE Constraints
If we no longer need the UNIQUE constraint on the specified table, we can use the ALTER TABLE and DROP CONSTRAINT clause as shown in the following syntax:
DROP CONSTRAINT constraint_name;
For example:
ALTER TABLE employees
DROP CONSTRAINT unique_name;
This should remove the unique_name constraint from the employees’ table.
Conclusion
You learned how you can configure the UNIQUE constraint on multiple table columns.