Why SQL UNIQUE Constraints Allow Duplicate NULLs

#sql #postgres #databasedesign #softwareengineering

Quick Answer: In SQL, unique constraints permit multiple NULL values because NULL represents an unknown state rather than an empty value, meaning two NULLs are never equal (NULL = NULL evaluates to UNKNOWN). While this prevents unintended collisions on optional single columns, it silently compromises composite keys unless you explicitly configure your index with NULLS NOT DISTINCT.

Imagine running a migration to enforce strict uniqueness on a database column, only to look at your production data a week later and find twenty rows that appear identical. Your initial instinct might be to suspect a transaction isolation bug or a race condition in your application layer.

In reality, your database is following the SQL standard to the letter.

Why does SQL permit duplicate NULL values in a UNIQUE column?

SQL relies on three-valued logic (True, False, and Unknown), which means a NULL value represents missing or unknown data rather than an empty string or zero. Because the database cannot confirm what an unknown value actually is, evaluating whether one NULL matches another yields UNKNOWN rather than TRUE, allowing the unique check to pass.

Think of a NULL like an unlabeled package sitting in a delivery warehouse. If two packages arrive without shipping labels, you cannot declare that they contain the exact same item. You simply do not know what is inside either box. Because the contents are uncertain, the system cannot definitively prove a collision, so it leaves them alone.

Here is how that plays out in a standard table definition:

CREATE TABLE members (
    id INT GENERATED ALWAYS AS IDENTITY,
    email TEXT UNIQUE
);

-- Both inserts will succeed without an error:
INSERT INTO members (email) VALUES (NULL);
INSERT INTO members (email) VALUES (NULL);

For a single column like an optional secondary email address, this behavior is usually desirable. You want to enforce uniqueness whenever an address is actually supplied, but you do not want to block multiple accounts from leaving the field blank.

When do duplicate NULLs break your data integrity?

Duplicate NULLs become hazardous when you introduce composite unique constraints across two or more columns, such as (org_id, team_id). If team_id is nullable to represent org-wide assets that do not belong to a specific team, the database allows endless identical (org_id, NULL) records because it cannot prove the NULLs represent the same entity.

Let's say you are building an access control system where a permission can be scoped to an entire organization or narrowed down to a specific team within that organization. If your schema uses a unique constraint across (org_id, team_id), you might expect an organization to only ever have one org-wide default record where team_id is NULL.

Instead, your database will happily accept dozens of records for the exact same org_id as long as team_id remains NULL. You end up with orphaned, ambiguous configuration rows that defy your business rules.

Value 1	Value 2	Standard SQL Equality (`=`)	Triggers Unique Violation?
`'alice@test.com'`	`'alice@test.com'`	`TRUE`	Yes
`'alice@test.com'`	`'bob@test.com'`	`FALSE`	No
`NULL`	`'alice@test.com'`	`UNKNOWN`	No
`NULL`	`NULL`	`UNKNOWN`	No

How do you enforce unique NULL values in modern SQL?

Starting in PostgreSQL 15, you can add the NULLS NOT DISTINCT clause directly to a unique constraint or unique index. This clause modifies the standard comparison behavior, instructing the query engine to treat all NULL values as identical for the purposes of that constraint.

If you are on an engine that supports this syntax, fixing composite constraints takes a single modifier:

CREATE TABLE org_permissions (
    org_id INT NOT NULL,
    team_id INT,
    CONSTRAINT unique_org_team UNIQUE NULLS NOT DISTINCT (org_id, team_id)
);

With NULLS NOT DISTINCT applied, the database permits only one row where team_id is NULL for any given org_id. A second attempt to insert (42, NULL) will immediately raise a unique violation error, protecting your multi-column logic without forcing you into artificial workarounds.

Frequently Asked Questions

Does standard SQL require NULLs to be distinct in unique constraints?

The SQL-92 standard left the treatment of NULLs in unique constraints somewhat ambiguous, but SQL:2008 introduced the optional UNIQUE NULLS DISTINCT and UNIQUE NULLS NOT DISTINCT clauses. By default, most engines (including Postgres, MySQL, and SQLite) default to treating NULLs as distinct.

How can you prevent duplicate NULLs in older PostgreSQL versions?

If you are running PostgreSQL 14 or earlier, you can prevent duplicate NULLs by creating a partial unique index. For example, create one unique index on (org_id, team_id) WHERE team_id IS NOT NULL and a second partial index on (org_id) WHERE team_id IS NULL.

Does Microsoft SQL Server treat NULLs the same way as PostgreSQL?

No. SQL Server historically diverges from the standard default behavior: a standard UNIQUE constraint in SQL Server allows only one NULL value across the entire column. To mimic PostgreSQL's default behavior in SQL Server, developers typically have to build a filtered index that excludes NULLs.

DEV Community