NULLs and Unique Constraints
SQL NULLs compare as unknown, so multiple NULL values can coexist under a unique constraint — a permissive quirk that differs by engine and silently breaks intended exclusivity rules.
Definition
Unique constraints treat NULL as distinct in the SQL standard: any number of NULL values may appear, because NULL equals nothing, including itself. Engines diverge on NULL handling in unique indexes, and partial unique indexes can enforce presence-conditional uniqueness. The quirk turns exclusivity requirements like one-primary-email into bugs.
Context and scope
Bites with nullable unique business identifiers, singleton configuration rows, and one-active-per-group rules expressed as unique with a nullable discriminator.
Common misconceptions
- Unique constraints reject duplicate NULLs — the standard treats NULLs as mutually distinct, so duplicates pass in most engines
- Coalesce in the constraint fixes it — functional fixes are partial indexes with IS NOT NULL filters or sentinel values with their own costs
- All databases behave the same — NULLs in unique indexes vary by engine and by NULL semantics flags
Anti-patterns
- Modeling single-active rows with a nullable active flag and unique constraint, then shipping multiple actives
- Using empty strings as NULL surrogates to dodge the quirk and corrupting data semantics for consumers
- Writing joins expecting NULL equality semantics and producing wrong rows silently
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/ddl-constraints.html
What it is
Unique means no two non-null values are equal. NULL is unknown rather than a value, so it never violates uniqueness under standard rules. A column intended to hold at most one meaningful value plus many absent ones therefore accepts unlimited absent ones — and unlimited duplicates of nothing, which is rarely what the schema meant.
How it works
Standard engines allow repeated NULLs in unique indexes. MySQL with multiple NULL-capable columns treats any NULL-containing row as distinct even when other columns duplicate. PostgreSQL offers partial unique indexes — unique where discriminator is not null — as the precise tool. Oracle historically treats duplicate NULLs in single columns as violations, diverging from the standard.
When to care
One primary email per user where others are NULL, one active subscription per account, singleton rows keyed by an optional type. Model with partial unique indexes, or restructure so the constraint operates on real values.
Pitfalls
Sentinel values like empty string or zero enforce uniqueness but leak fake data into queries and aggregates. ORM validations duplicate constraint semantics imperfectly and race under concurrency — the database constraint is the arbiter. Test the engine-specific behavior explicitly before relying on it.