PostgreSQL Unique Constraints

Unique constraint is used to enforce data integrity in PostgreSQL tables. It ensures that no duplicate values are entered in specific columns that is not primary key column.

use a UNIQUE constraint instead of a PRIMARY KEY constraint if you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.

The unique constraint can be applied to one or more columns in a table. PostgreSQL raises an error when you try to insert or update a value which is already exists in a column.

A NULL value in a column means missing data, and the NULL value of one column does not equal a NULL value in another column. Hence unique constraint allows storing one NULL value in the column.

Unique constraints can be defined at the table level or at the column level, as shown below.

CREATE TABLE table-name (
   column1 data_type(length) UNIQUE,
   column2 data_type(length),
...
    UNIQUE(column2)
);

In the above syntax, the unique constraint is defined on column1 at column level and for column2 at table level.

The following create a unique constraint on the email column of the employee table.

CREATE TABLE IF NOT EXISTS employee 
(emp_id INT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
gender CHAR(1),
birthdate DATE,
email VARCHAR(100) UNIQUE,
salary INT);

The above CREATE TABLE statement will create the following employee table with the unique constraint:

Create Unique Constraint on Multiple Columns

When a unique constraint is defined on a group of columns, then the combination of those column values needs to be unique across the table. A Unique constraint can be defined on multiple columns by specifying it at table level.

CREATE TABLE table_name (
   column1 data_type(length),
   column2 data_type(length),
...
UNIQUE(column_1, column_2)
);

Let's add a unique constraint on combination of first_name and last_name.

CREATE TABLE IF NOT EXISTS employee 
(emp_id	INT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
gender CHAR(1),
birthdate	DATE,
email	VARCHAR(100),
salary		INT,
UNIQUE (first_name, last_name));

In the above example, the first_name column also has a NOT NULL constraint also. Hence, you can have multiple constraints defined on one column.

Adding UNIQUE Constraint on Existing Table:

Unique constraints can be added on one or more columns of the existing table, provided that column or set of columns have unique values across the table rows.

ALTER TABLE table_name
ADD CONSTRAINT constraint_name
UNIQUE(column1,column2,..);

The following will add unique constraint on the email column of the employee table.

ALTER TABLE employee
ADD CONSTRAINT unique_employee_email
UNIQUE(email);

Create Unique Constraint using pgAdmin

You can create unique constraints on new or existing tables using pgAdmin. The steps would remain the same if you add unique constraints on new tables or existing tables.

Expand your databases node in the left pane and then expand Schemas -> public -> Tables nodes.

Now, right-click on the table where you want to add the unique constraints and click on 'Properties' in the menu.

In the popup, go to the 'Constraints' tab and click on the Unique tab, as shown below.

Now, to add a unique constraints, click on the + button to add a row in a grid. Again click on the edit icon to add unique column definition, as shown below.

As you can see above, add unique constraint name in the Name textbox.

Now, go to the Definition tab and select one or more columns where you want to apply the unique constraint, as shown below.

Click on the Save button to add the unique constraint on the selected columns.

In the same way, you can add unique constraints to a new table using pgAdmin.