Postgres Unique Constraint

Introduction to the UNIQUE constraint

In SQL, constraints allow you to maintain data integrity and keep all your records uniform and consistent by throwing errors whenever a constraint is “violated”. The Postgres UNIQUE constraint is especially valuable because it prevents any two records from having the same data in a particular column. It’s no surprise, then, that a table’s ID (identity) column is a particularly good candidate for this constraint. In this article, we’ll take a closer look at the Postgres UNIQUE constraint and check out some examples of its use.

Prerequisites to using PostgreSQL

Before we start looking at examples of the Postgres UNIQUE constraint, we need to make sure that a couple of key prerequisites are in place. Both PostgreSQL and its psql command-line interface need to be installed. If you’re not sure which version of PostgreSQL you’re running, you can use the psql -V command to have it return its version number.

Accessing the PostgreSQL using the ‘psql’ command-line interface

We use the psql command to access a PostgreSQL database on a localhost server:

Create a PostgreSQL database to test the ‘UNIQUE’ constraint

We’ll need to create a table with a column that utilizes the UNIQUE constraint, but first we’ll need to set up a Postgres database.

We’ll use the command below to create a test database in PostgreSQL:

This command will return a response saying CREATE DATABASE. Once your database has been successfully created, exit psql using the \q command, then type the following command to go back into psql and access the database you just created:

You can also just use the \c command followed by the database name to connect to the database.

Create a PostgreSQL table using the ‘UNIQUE’ constraint syntax

A UNIQUE constraint acts as a sort of rule that restricts what values can be used in a given column. This prevents records with duplicate values from being inserted into a table.

To create our table, we’ll use a command with the following syntax in the psql command-line interface:

Here’s the exact command we’ll use to create a PostgreSQL table with a UNIQUE constraint:

We can display the table information by using the \d command followed by the table name:

NOTE: The some_user_email_key index exists because of the UNIQUE constraint on the email column. Its presence means that you can’t input the same value for that column in different records.

Always remember that UNIQUE and PRIMARY KEY constraint work in the same way. The main difference between them is that you can have multiple UNIQUE constraints within a table but only one PRIMARY KEY.

Violating the unique constraint

Let’s picture a scenario where we have two users named “Jane” and “John Doe”. We’ll attempt to insert a record for each of them using the same email address. Shown below is the INSERT INTO statement that we’ll use:

Because of the UNIQUE CONSTRAINT placed on the email column, this SQL command will throw an exception and return the following response:

If you execute the SELECT * FROM some_user; SQL command in psql, it should return 0 rows since the above exception prevented any records from being inserted.

Conclusion to the UNIQUE constraint

When you’re using PostgreSQL to store and manage your data, it’s important to put safeguards in place to maintain data integrity. The Postgres UNIQUE constraint provides a simple and effective way to prevent duplicate values from being entered in a given column within a table. In this tutorial, we explained how to create a table using the Postgres UNIQUE constraint, and we showed an example of how the constraint works to enforce uniqueness during the insertion of records. With these instructions and examples, you’ll be able to implement this constraint on your own PostgreSQL tables and maintain better data integrity.