PostgreSQL UNIQUE Constraint: Introduction, Syntax & Examples

The UNIQUE constraint, as the name suggests, enforces the table to contain the unique data. In this tutorial, we will be learning about the unique constraints in PostgreSQL with some examples. So, let’s get started!

Also Read: PostgreSQL SELECT Statement

PostgreSQL UNIQUE Constraint Introduction

In the application, you often need the table column to have unique data only, for example, phone number, email or username. In this case, we use the UNIQUE constraint.

Simply, the UNIQUE constraint maintains the uniqueness in the table. If you try to insert or update the data in the column having the UNIQUE constraint, PostgreSQL will throw an error.

You can create a UNIQUE constraint on a single column as well as a group of columns.

The UNIQUE constraint is similar to the primary key except that the UNIQUE constraint accepts the null values. Moreover, you can have as many unique constraints as you want on a single table.

PostgreSQL UNIQUE Constraint Syntax

You can create the unique constraint in PostgreSQL in multiple ways. Check the syntaxes below to understand when to use which syntax.

-- syntax 1 - creates default / custom named unique constraint on single column
CREATE TABLE tableName(
colName datatype [ CONSTRAINT constraintName ] UNIQUE
);

-- syntax 2 - creates default / custom named unique constraint on multiple columns
CREATE TABLE tableName(
colName datatype,
[ CONSTRAINT constraintName ] UNIQUE( { colName | colNameList } )
);

-- syntax 3 - creates unique constraint on existing table
ALTER TABLE tableName
ADD [CONSTRAINT constraintName]UNIQUE( { colName | colList } );
Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, each syntax creates a unique constraint but is used for different circumstances. We will see examples of each syntax further in this tutorial.

The UNIQUE constraint is ignored for the null values, and because two null values are not equal, we can have multiple null values in the table. However, this behaviour can be changed by using the UNIQUE NULLS NOT DISTINCT clause.

PostgreSQL UNIQUE Constraint Examples

Now let’s see examples of each syntax one by one.

Create a UNIQUE Constraint on a Single-Column

We can create the single-column unique constraint using the UNIQUE keyword inline.

DROP TABLE IF EXISTS students;
CREATE TABLE students(
Id serial,
email VARCHAR(100) UNIQUE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, PostgreSQL automatically generates the name for the unique constraint. You can also assign any name to the constraint as shown below.

Create Custom Named UNIQUE Constraint

DROP TABLE IF EXISTS students;
CREATE TABLE students(
Id serial,
email VARCHAR(100) CONSTRAINT uniqueEmail UNIQUE
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see in the highlighted area, PostgreSQL has created the unique constraint of having the name assigned by us.

Create Multi-Column UNIQUE Constraint

We can create a unique constraint on the group of columns. Check the example below.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees(
Id serial,
empId INT,
deptId INT,
UNIQUE(empId, deptId)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the combination of the empId and deptId is used as a unique key.

Create a UNIQUE Constraint on Existing Table

If you forgot to create the unique constraint while creating the table, no worries, you can still create the unique constraint on your existing table. Check the example below.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees(
Id serial,
empId INT,
deptId INT
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, there is no constraint present in the table. So, let’s create one.

ALTER TABLE employees
ADD UNIQUE(empId,deptId);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see, the unique constraint has been added to the table.

Drop the Unique Constraint from the Table

Let’s say you have created the unique constraint on the table and you later think it is unnecessary. In this case, you can delete the unique constraint using the ALTER command.

To delete the constraint, specify the constraint name after the DROP CONSTRAINT clause in the below query.

ALTER TABLE employees
DROP CONSTRAINT IF EXISTS employees_empid_deptid_key;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As you can see here, the unique constraint that we had created in the previous example has been deleted.

Read More: PostgreSQL DROP TABLE

Conclusion

In this tutorial, we have learned how to create the unique constraint on the table using multiple ways along with how to delete an existing unique constraint using ALTER command. Note that, the unique index is created automatically under the hood as soon as you create the unique constraint. The unique index helps the query run faster when finding a particular row. You can read more about the unique index to improve your knowledge.

References

• StackOverflow thread explanation on how unique index increases performance
• PostgreSQL official documentation on unique constraints