Last updated on

PostgreSQL - Unique Constraint vs Unique Index

Introduction

When designing a database, ensuring data integrity is crucial. One common requirement is to make sure that certain columns in your tables do not contain duplicate values. PostgreSQL offers two mechanisms to enforce this: the UNIQUE CONSTRAINT and the UNIQUE INDEX. While they might seem similar, understanding their differences can help you make better decisions for your database design.

In this post, we’ll investigate how each of these works, and when you might prefer one over the other.

Goal

The goal is straightforward: we want a column in a table to contain only unique values, meaning that any attempt to insert a duplicate value should result in an error.

Investigation

To begin, let’s create a simple users table to demonstrate the use of a UNIQUE CONSTRAINT.

CREATE TABLE users (
  id integer PRIMARY KEY,
  email text,
  UNIQUE (email)
);

Testing the UNIQUE CONSTRAINT

We’ll add a row and then attempt to insert a duplicate email.

INSERT INTO users VALUES (1, '[email protected]');

INSERT INTO users VALUES (2, '[email protected]');

As expected, the database throws an error, preventing the duplicate entry.

ERROR: duplicate key value violates unique constraint “users_email_key”

This shows that the UNIQUE CONSTRAINT successfully enforces uniqueness.

Creating the Unique Index

Next, let’s explore how a UNIQUE INDEX operates by creating another table.

CREATE TABLE users1 (
  id INTEGER PRIMARY KEY,
  email TEXT
);

CREATE UNIQUE INDEX email_idx ON users1 (email);

Testing the UNIQUE INDEX

We perform the same insert operations:

INSERT INTO users1 VALUES (1, '[email protected]');

INSERT INTO users1 VALUES (2, '[email protected]');

Again, the database prevents the duplicate entry, throwing an error similar to the one we saw earlier.

ERROR: duplicate key value violates unique constraint “email_idx”

This confirms that a UNIQUE INDEX can also enforce uniqueness.

Outcome

At this point, it’s clear that both UNIQUE CONSTRAINT and UNIQUE INDEX can achieve the same goal of preventing duplicate values. But the question remains: which one to use ?

Research

To gain more insight, let’s see what others in the community are saying about these concepts.

Community Insights

Reddit

On Reddit, user cldellow points out a subtle advantage: “Constraints can be deferred,” linking to the PostgreSQL documentation on the topic. This feature allows for more flexibility in certain operations.

Another user, bendem, adds, “Constraints are modeling concepts, indexes are optimizations,” highlighting a key distinction in their intended use.

StackOverflow

A StackOverflow thread echoes similar sentiments, with user David Spillett noting that “Under the hood, a unique constraint is implemented the same way as a unique index.” Although, the comment is about SQL Server (and not about Postgres), it’s still an useful information.

PostgreSQL Documentation

The PostgreSQL documentation clarifies that adding a UNIQUE CONSTRAINT will automatically create a unique B-tree index on the specified columns. The documentation also states that a UNIQUE INDEX is the mechanism that enforces the constraint.

“Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint.” — Documentation: 16: 5.4. Constraints1

“PostgreSQL automatically creates a unique index when a unique constraint or primary key is defined for a table. The index … is the mechanism that enforces the constraint.” — Documentation: 16: 11.6. Unique Indexes2

Conclusion

In summary, both UNIQUE CONSTRAINT and UNIQUE INDEX enforce uniqueness in PostgreSQL, but they serve slightly different purposes.

Here’s what we’ve learnt:

  1. Both UNIQUE CONSTRAINT & UNIQUE INDEX can be used to enforce uniqueness.
  2. A UNIQUE CONSTRAINT is a rule. Under the hood it is implemented or enforced using a UNIQUE INDEX.
  3. A UNIQUE INDEX is automatically created when we define a UNIQUE CONSTRAINT.
  4. If the goal is to enforce uniqueness, use a UNIQUE CONSTRAINT. This will give a hint to future maintainers about its purpose.
  5. If the goal is to search/sort the rows or you have a complex requirement to enforce uniqueness conditionally (e.g. Partial Indexes), use a UNIQUE INDEX.
  6. Do not create both of them on the same column(s).

Understanding these nuances helps in designing a robust and efficient database that meets your specific needs.

Footnotes

  1. https://www.postgresql.org/docs/16/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS ↩

  2. https://www.postgresql.org/docs/16/indexes-unique.html#INDEXES-UNIQUE ↩