Sometimes we have to store the unique records in a table, such as an email address, employee id, etc. To achieve this purpose, the “UNIQUE” constraint is used in PostgreSQL. The UNIQUE constraint allows us to store the unique rows/records in a table.

This post will explain the working of the UNIQUE constraint through practical examples. So, let’s begin.

How Does UNIQUE Constraint Work in PostgreSQL?

Each time when you insert a new record in a table, the UNIQUE constraint checks whether the value to be inserted already exists in the table or not. If the specified value already exists, then the UNIQUE constraint generates an error stating that the specified value already exists in the table. In PostgreSQL, a unique index is automatically created when we insert a UNIQUE constraint to a table’s column.

Example: How to Create and Store a UNIQUE Constraint in PostgreSQL?

Let’s create a table named car_details with four columns id, car_model, car_color, and reg_number:

CREATE TABLE car_details (
id INT PRIMARY KEY, 
car_model VARCHAR (30), 
car_color VARCHAR (30), 
reg_number VARCHAR (30) UNIQUE
);

In the above snippet, we utilized the UNIQUE constraint with the reg_number column so it will accept only unique values:

The car_details table has been created successfully. Let’s execute the SELECT command to see the table structure:

SELECT * FROM car_details;

Let’s execute the INSERT query to insert a couple of rows into the car_details table:

INSERT INTO car_details(id, car_model,car_color,reg_number)
VALUES(1, 'Alto-2022','black','xyz123'),
(2, 'SWIFT-2021','blue','abx321');

Let’s insert a duplicate registration number and see how the UNIQUE constraint deals with that record:

INSERT INTO car_details(id, car_model,car_color,reg_number)
VALUES(3, 'Baleno-2022','red','xyz123');

The output shows that the UNIQUE constraint restricts us from inserting a duplicate value into the reg_number column.

How Does UNIQUE Constraint Work on Multiple Columns in PostgreSQL?

Postgres enables us to implement the UNIQUE constraint on more than one column using the following syntax:

CREATE TABLE tab_name (
col_1 data_type,
col_2 data_type,
…,
col_n data_type,
UNIQUE (col_1, col_2));

The following syntax will work the same way as the above syntax:

CREATE TABLE tab_name(
col_1 data_type UNIQUE,
col_2 data_type UNIQUE,
…,
col_n data_type
);

Columns col_1 and col_2 will have a unique combination of values throughout the table.

Example: How to Use UNIQUE Constraint on Multiple Columns in Postgres?

Let’s create a table named bike_info that implements UNIQUE constraints on multiple columns:

CREATE TABLE bike_info (
bike_model VARCHAR (50),
bike_color TEXT,
reg_num VARCHAR (50) UNIQUE,
bike_id INT UNIQUE
);

In this way, you can create multiple columns with unique constraints. Let’s insert some records into the newly created table to understand the working of UNIQUE constraint:

INSERT INTO bike_info(bike_model,bike_color,reg_num, bike_id)
VALUES('BMW K 1200 S','black','xyz456', 12 ),
('Ducati 1098s','black','xyz321', 12);

The output shows that the bike_id in the second row violates the unique constraint.

Conclusion

The UNIQUE constraint allows us to store the unique records in a table. Each time when you insert a new record in a table, the UNIQUE constraint checks whether the value to be inserted already exists in the table or not. If the specified value already exists, then the UNIQUE constraint generates an error stating that the specified value already exists in the table. This post explained how to use the UNIQUE constraint in PostgreSQL.