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:
CREATE DATABASE testdb;
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:
psql some_username -h 127.0.0.1 -d testdb
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
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:
CREATE TABLE TABLE_NAME(COLUMN_NAME + DATA_TYPE + UNIQUE);
Here’s the exact command we’ll use to create a PostgreSQL table with a UNIQUE constraint:
CREATE TABLE some_user (
id SERIAL PRIMARY KEY,
email VARCHAR(50) UNIQUE,
We can display the table information by using the
\d command followed by the table name:
COLUMN | TYPE | Collation | NULLABLE | DEFAULT
id | INTEGER | | NOT NULL | NEXTVAL('some_user_id_seq'::regclass)
name | text | | |
email | CHARACTER VARYING(50) | | |
password | CHARACTER VARYING(255) | | |
"some_user_pkey" PRIMARY KEY, btree (id)
"some_user_email_key" UNIQUE CONSTRAINT, btree (email)
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
PRIMARY KEYconstraint work in the same way. The main difference between them is that you can have multiple
UNIQUEconstraints within a table but only one
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:
some_user (id, name, email, password)
(1, 'John Doe', 'firstname.lastname@example.org', 'SeCrEtPaSs'),
(2, 'Jane Doe', 'email@example.com', 'securePASS');
Because of the
UNIQUE CONSTRAINT placed on the
ERROR: duplicate KEY VALUE violates UNIQUE CONSTRAINT "some_user_email_key"
DETAIL: KEY (email)=(firstname.lastname@example.org) already EXISTS.
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.Get Started