Posgtres UNIQUE Constraint on Multiple Columns

The unique constraint is one of the most valuable constraints in PostgreSQL. It provides high data integrity and uniqueness across the table when used with other constraints such as NOT NULL.

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:

1. Installed PostgreSQL server on your system
2. 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.

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:

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:

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:

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.