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:
1 | psql |
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:
1 | 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:
1 | 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
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:
1 | 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:
1 2 3 4 5 6 | CREATE TABLE some_user ( id SERIAL PRIMARY KEY, name TEXT, email VARCHAR(50) UNIQUE, password VARCHAR(255) ); |
We can display the table information by using the \d
command followed by the table name:
1 2 3 4 5 6 7 8 9 10 | TABLE "public.some_user" 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) | | | Indexes: "some_user_pkey" PRIMARY KEY, btree (id) "some_user_email_key" UNIQUE CONSTRAINT, btree (email) |
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
andPRIMARY KEY
constraint work in the same way. The main difference between them is that you can have multipleUNIQUE
constraints within a table but only onePRIMARY 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:
1 2 3 4 5 | INSERT INTO some_user (id, name, email, password) VALUES (1, 'John Doe', 'jdoe@email.com', 'SeCrEtPaSs'), (2, 'Jane Doe', 'jdoe@email.com', 'securePASS'); |
Because of the UNIQUE CONSTRAINT
placed on the email
column, this SQL command will throw an exception and return the following response:
1 2 | ERROR: duplicate KEY VALUE violates UNIQUE CONSTRAINT "some_user_email_key" DETAIL: KEY (email)=(jdoe@email.com) 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