skip to content

Unique Constraints & NULLs

19 roadmaps4 questions

How UNIQUE is enforced and its notorious NULL interplay: most engines admit many NULLs in a unique column, and engines disagree with each other. A classic trap question that separates rote learners from practitioners.

part ofRelational database conceptsoverview, primer and where to startread it →
on this pageshow

explore

questions

4

A nullable column carries a UNIQUE constraint. How many rows holding NULL in that column can the table contain, and why?

level: juniorimportance: must knowfreq 60%

answer

  1. NULL = NULL is unknown, not true
  2. many NULLs allowed (standard)
  3. SQL Server: exactly one NULL
  4. PRIMARY KEY = UNIQUE + NOT NULL
  5. 'one live row' via NULL flag enforces nothing

basics

~20 s

In standard SQL and most engines, unlimited — NULL means 'unknown', two NULLs never compare equal, so no duplicate is ever detected. Microsoft SQL Server is the well-known exception: it permits only one NULL. A PRIMARY KEY forbids NULL entirely.

solid answer

~50 s

Under the SQL standard a unique constraint is violated only when two rows have **equal, non-null** values in the constrained columns. NULL means "unknown", and `NULL = NULL` evaluates to unknown rather than true, so two NULL rows are never duplicates. PostgreSQL, Oracle, MySQL and SQLite all follow this: **any number of NULLs is allowed**. Microsoft SQL Server is the classic exception — its unique constraints and unique indexes treat NULLs as equal for this purpose, so at most **one** NULL row fits. Oracle adds its own twist: an empty string is stored as NULL, so `''` behaves like a missing value there. The practical consequence is that `UNIQUE` on a nullable column does **not** enforce "at most one row without a value". If the rule you want is "every row has a value and values are distinct", you need `NOT NULL` alongside `UNIQUE` — which is exactly what a primary key is: unique plus not-null.

code

sql · 9 lines
sql
CREATE TABLE person (
  id       bigint PRIMARY KEY,
  passport text UNIQUE
);

INSERT INTO person VALUES (1, 'X123');   -- ok
INSERT INTO person VALUES (2, NULL);     -- ok
INSERT INTO person VALUES (3, NULL);     -- ok (standard); rejected by SQL Server
INSERT INTO person VALUES (4, 'X123');   -- rejected everywhere

go deeper

for a junior

State the rule and the reason: NULL means unknown, NULLs never compare equal, so many NULL rows are allowed; add NOT NULL when a value is mandatory.

for a middle

Name SQL Server's single-NULL deviation and Oracle's empty-string-is-NULL quirk, and connect the rule to PRIMARY KEY = UNIQUE + NOT NULL.

for a senior

Spot the inert-constraint anti-pattern where a NULL flag is used to mean 'active', and be able to prescribe partial uniqueness or a sentinel instead.

for a principal

Treat it as a portability and modelling matter: do not let correctness depend on which side of the NULL-distinct split an engine falls on.

## The rule SQL uses three-valued logic. A comparison involving NULL yields **unknown**, not true or false: `NULL = NULL` is unknown, and so is `NULL = 5`. NULL is not a value; it is a marker for "no value recorded". A unique constraint is defined in those terms. The standard says the constraint is satisfied unless two rows have the same values in the constrained columns, where sameness is the usual comparison — and a comparison that yields unknown does not establish sameness. Therefore two rows that are both NULL in the constrained column are not duplicates, and neither is a NULL row versus a valued row. Read intuitively: if you do not know a person's passport number, and you do not know a second person's passport number either, the database has no basis to claim the two people share a passport. Uniqueness is enforced among the facts you actually recorded. ## What each engine does - **PostgreSQL, Oracle, MySQL/MariaDB, SQLite, DB2**: unlimited NULLs in a uniquely-constrained nullable column. This is standard behaviour. - **Microsoft SQL Server**: treats NULLs as equal for uniqueness purposes, so a unique constraint or unique index permits at most **one** NULL row and rejects the second with a duplicate-key error. This is a documented deviation and a frequent source of surprise in cross-engine work. - **Oracle extra quirk**: the empty string `''` is stored as NULL. Code that writes `''` to mean "blank" gets NULL semantics — meaning many such rows coexist under a unique constraint even though the application thinks it stored a real value. Because of this split, an application that relies on either behaviour is engine-coupled. Relying on "many NULLs allowed" breaks when ported to SQL Server; relying on "only one NULL" breaks everywhere else. ## Why this bites in practice A typical schema has an optional external identifier: `external_ref` is filled in for rows that came from an integration and NULL for locally created rows. `UNIQUE (external_ref)` correctly prevents importing the same external record twice — and correctly permits thousands of local rows with no reference. That is usually what you want. The bug appears when the rule was actually "at most one row in this state", and the state was encoded as a NULL. For instance, `UNIQUE (user_id, revoked_at)` intended as "one live token per user": live tokens have `revoked_at` NULL, so they never collide, and the constraint enforces nothing at all for the case it was written for. The constraint looks protective in the DDL and is inert at runtime — a genuinely dangerous shape because reviewers see the word UNIQUE and move on. The reverse mistake is assuming the constraint guarantees completeness: `UNIQUE (email)` on a nullable column does not mean every row has an email. Only `NOT NULL` does that. ## Relationship to primary keys A `PRIMARY KEY` is defined as unique **plus** not-null on every one of its columns. That is why the NULL question never arises for primary keys, and why a unique constraint is not simply "a primary key you did not pick": it is weaker in exactly this way. When an interviewer asks for the difference between a primary key and a unique constraint, NULL handling and the "one primary key per table" rule are the two substantive points. ## Getting the behaviour you want 1. **"Every row must have a distinct value"** — `UNIQUE` + `NOT NULL`. Simplest and portable; do this whenever the value is genuinely mandatory. 2. **"Values are distinct when present, absent freely"** — plain `UNIQUE` on a nullable column, which is the standard behaviour and needs nothing extra (but confirm your engine does not collapse NULLs). 3. **"At most one row may be missing the value"** — you must make the NULLs collide, either with a clause that declares NULLs non-distinct or by substituting a sentinel value; this is the case that requires deliberate work. When writing schemas that must run on several engines, do not leave the behaviour implicit. State the intent in a comment, and prefer designs that do not depend on which side of the split your engine is on — for example, making the column `NOT NULL` with an explicit sentinel, or moving optional-and-unique attributes into their own table where the column can be `NOT NULL`. ## Quick self-check Given `UNIQUE (nickname)` on a nullable column and rows `('ada')`, `(NULL)`, `(NULL)`: standard engines accept all three; SQL Server rejects the third. Adding a fourth row `('ada')` is rejected everywhere — the non-null duplicate is the only thing uniqueness ever catches.

  • So what is the difference between a PRIMARY KEY and a UNIQUE constraint?
    A primary key is unique plus NOT NULL on every column, and a table has at most one of them; unique constraints can be declared many times per table and may cover nullable columns, where the NULL rows do not collide under standard semantics. Engines also tend to cluster or otherwise privilege the primary key physically. Functionally, the primary key is the identifier you have chosen; unique constraints record the other candidate keys.
  • A colleague writes UNIQUE (user_id, revoked_at) to allow only one un-revoked token per user. What is wrong?
    Un-revoked rows carry NULL in revoked_at, and NULLs do not compare equal, so two live tokens for the same user never collide — the constraint is inert for exactly the case it was written for. The correct tools are a partial unique index over rows where revoked_at IS NULL, or a non-null sentinel encoding. The dangerous part is that the DDL reads as protective while enforcing nothing.

Two blank forms with the passport field left empty are not evidence that two people share a passport — the database only catches duplicates among the numbers actually written down.

saying these in an interview costs you the question

  • Saying a unique constraint allows exactly one NULL as if that were universal
  • Believing UNIQUE on a nullable column implies every row has a value
  • Writing UNIQUE (x, nullable_flag) to enforce 'at most one active row'
  • Claiming NULL = NULL is true, or that NULL equals an empty string in every engine
  • Treating a unique constraint and a primary key as interchangeable

context

resolving links…

open as a page

A table declares a two-column unique constraint over (tenant_id, external_ref). Two rows are inserted, both with tenant_id 7 and external_ref NULL. Does the constraint reject the second row?

level: middleimportance: should knowfreq 40%

basics

~20 s

No, under standard semantics. A composite unique key only rejects a row when every constrained column compares equal and none of them is NULL. A single NULL anywhere in the key makes the comparison unknown, so both rows are accepted. SQL Server rejects the second.

open as a page

A rule requires that at most one row per customer may have an empty (NULL) discount code — that is, uniqueness must treat two NULLs as equal. What options does a relational database give you, and what does each cost?

level: seniorimportance: should knowfreq 28%

basics

~20 s

Three routes: declare the key with NULLS NOT DISTINCT where the engine supports it (SQL:2023, PostgreSQL 15+); replace NULL with a non-null sentinel and use an ordinary unique constraint; or add a partial unique index over the rows where the column IS NULL. The sentinel is the portable choice.

open as a page

You are modelling an optional identifier — most rows will not have one, but when present it must be unique — and the schema has to behave identically on PostgreSQL, MySQL and Microsoft SQL Server. How do you approach the design?

level: principalimportance: nice to knowfreq 22%

basics

~20 s

Do not let correctness depend on engine NULL rules, which disagree. Either move the optional identifier into its own table where the column is NOT NULL and uniquely constrained, or keep it in place with an engine-specific object (filtered index on SQL Server, plain unique constraint elsewhere) chosen deliberately.

open as a page