SQL, data modeling, data quality, EDA, visualization · insight-1046
Do not rely on a UNIQUE constraint to stop duplicates when a key column can be NULL
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- cross-industry
Summary: situation, action, reason, boundary
Situation
A table has UNIQUE(customer_id, promo_code), promo_code is nullable, and the nightly load keeps inserting "the same" customer row with no promo.
Action
Make key columns NOT NULL with an explicit sentinel, or declare the constraint with NULLS NOT DISTINCT where supported, or add a unique expression index on COALESCE values. Add a dbt-style uniqueness test on the combination of columns for engines that cannot enforce it.
Reason
The SQL standard leaves NULL treatment in unique constraints implementation-defined, and PostgreSQL treats NULLs as distinct by default.
Boundary
Natural keys with optional parts, upserts keyed on nullable columns and warehouse tables without enforced constraints.
Symptom or risk
By default NULLs are not equal for uniqueness, so any number of rows with NULL in a constrained column pass the constraint and duplicates accumulate unnoticed.
Exceptions
Engines that already treat NULLs as equal, which differ from each other.
Trade-offs
Sentinels need to be documented and excluded from numeric aggregates.
Pitfalls
Writing ON CONFLICT against a key that includes a nullable column and expecting conflicts to fire.
Lessons and concepts
Claims and their sources
In PostgreSQL two NULLs are not considered equal by a unique constraint by default, NULLS NOT DISTINCT changes that, and the SQL standard leaves the default implementation-defined.
The dbt-utils package provides a unique_combination_of_columns test for sets of columns that must be unique together.