Enforcing uniqueness with UNIQUE

A UNIQUE constraint requires the values covered by the constraint to be unique across rows.

For example:

means PostgreSQL will not allow two rows to contain the same non-NULL email value.

A simplified view looks like this:

The database itself now protects the uniqueness rule.

A primary key also requires unique values, but a primary key has an additional role: it identifies each row in the table.

For example:

is the primary key.

While:

is another value that must be unique.

A table can have only one primary key, but it can have multiple UNIQUE constraints.

For example, a users table might have:

Each constraint represents a different rule about the data.

Sometimes no single column needs to be unique, but a combination of values must be.

Imagine an application has a table containing products that customers have saved:

The same customer can save many products:

and the same product can be saved by many customers:

But this combination cannot appear twice:

The unique rule applies to the pair:

rather than either column individually.

By default, PostgreSQL treats NULL values as distinct when enforcing a UNIQUE constraint.

For example, imagine:

If phone allows NULL, multiple rows can normally contain:

because PostgreSQL does not treat those NULL values as equal duplicates for the purpose of the constraint.

When an application needs NULL values to be treated as duplicates as well, PostgreSQL also supports:

With that form, only one row can contain NULL for the constrained value.

For many application columns such as email, you will instead commonly combine:

and:

so the value must both exist and be unique.

When you create a UNIQUE constraint, PostgreSQL automatically creates a unique B-tree index to enforce it.

That is why our customers.email column already has an index even though we didn't explicitly run:

The constraint and index have related but different roles.

The constraint expresses the data rule:

The index gives PostgreSQL an efficient way to enforce that rule.

Imagine an application creates a user like this:

At first, this might seem enough to prevent duplicates.

But two requests could happen at almost the same time:

Both requests saw that the email was available before either insert completed.

If the database has no uniqueness constraint, both rows could be inserted.

This is a race condition.

With:

PostgreSQL guarantees that only one of the inserts can succeed.

The other insert is rejected because the uniqueness rule has been violated.

A simplified view looks like this:

This is an important reason to enforce important invariants inside the database rather than relying only on application code.

The application can still check whether an email already exists.

Doing so may allow it to give the user a friendly message such as:

But the database constraint remains the final guarantee.

The correct pattern is usually:

rather than choosing one or the other.

A regular index is primarily a performance structure.

A UNIQUE constraint expresses something about the correctness of the data.

For example:

does not mean:

It means:

PostgreSQL happens to create an index to enforce that rule efficiently.

The important point is:

Use a `UNIQUE` constraint when uniqueness is part of the data model, not merely because you want a unique index.