NULL Columns Silently Bypass Unique Constraints

A UNIQUE constraint over a nullable column does not stop duplicates. When the value is NULL, the constraint is satisfied, and the database inserts the row without complaint. This is one of the most common silent data-integrity failures in production schemas, and it is almost always discovered late.

The mechanism is simple. In SQL, NULL means "unknown," not "empty." A unique constraint enforces that no two rows share the same value. Two unknowns are not equal to each other, so the constraint has nothing to compare and lets both rows in.

Why does a UNIQUE constraint allow multiple NULLs?

SQL treats NULL as a non-value. The comparison NULL = NULL evaluates to UNKNOWN, not TRUE. A unique index therefore cannot prove that two NULL entries are duplicates, so it permits them.

This is defined behavior, not a bug. The SQL standard leaves the treatment of NULL in unique constraints to the implementation, and most engines follow the same rule: multiple NULLs are allowed.

• PostgreSQL: multiple NULLs are allowed in a UNIQUE column by default.
• MySQL / InnoDB: multiple NULLs are allowed in a UNIQUE index.
• SQLite: multiple NULLs are allowed in a UNIQUE constraint.
• SQL Server: a UNIQUE constraint permits only one NULL unless a filtered index is used.

The divergence matters. A schema that behaves one way in a local SQLite test can behave differently in production PostgreSQL, and a migration between engines can silently change deduplication semantics.

What does the failure look like in practice?

Consider an ingestion table that deduplicates on an external identifier. The identifier is nullable because some upstream sources do not provide one.

CREATE TABLE events (
  id          BIGSERIAL PRIMARY KEY,
  source_id   TEXT UNIQUE,
  payload     JSONB NOT NULL,
  ingested_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The intent is clear: one row per source_id. The behavior is not. Every row where source_id IS NULL passes the constraint, so a retry loop or a partial upstream response can insert the same logical event repeatedly.

No error is raised. No warning is logged. The insert succeeds, the application moves on, and the duplicate count grows quietly until a downstream aggregation or a billing report surfaces it.

Which tables are most exposed to this pattern?

Any table where the deduplication key is optional is a candidate. Common cases include:

• Event streams where a correlation ID is sometimes absent.
• Sync tables that mirror an external system with partial identifiers.
• Staging tables loaded before a normalization step.
• Join tables where one side of the relationship is optional.

The pattern is especially dangerous in idempotent ingestion. Teams typically design a retry-safe insert around a unique key, assume the constraint provides exactly-once semantics, and then discover that the guarantee only holds for rows where the key is present.

How do you enforce uniqueness when NULLs are allowed?

There are three practical approaches. The right one depends on whether NULL is a legitimate state or a data-quality defect.

Option 1: Make the column NOT NULL. If every row should have an identifier, the schema should say so. This is the cleanest fix and it forces the upstream contract to be explicit.

ALTER TABLE events
  ALTER COLUMN source_id SET NOT NULL;

Option 2: Use a partial unique index. When NULL is a valid state but duplicates among non-null values must be blocked, a partial index expresses the intent precisely.

CREATE UNIQUE INDEX events_source_id_key
  ON events (source_id)
  WHERE source_id IS NOT NULL;

This is the correct tool when the column is genuinely optional. It does not prevent multiple NULL rows, but it makes that behavior explicit rather than accidental.

Option 3: Normalize the sentinel. Replace NULL with a deterministic placeholder before insert, then enforce uniqueness on the normalized column. This is common in pipelines that cannot change the upstream schema.

UPDATE events
SET source_id = 'unknown:' || id
WHERE source_id IS NULL;

The trade-off is that the placeholder becomes part of the data model and must be documented. It also makes the column non-nullable in practice, which is usually the desired outcome.

Before shipping any of these, check the existing constraint. Query the catalog for unique indexes on nullable columns and confirm which ones are load-bearing for deduplication.

Why does this survive code review?

The constraint reads correctly. UNIQUE on a column named source_id looks like it enforces one row per source. The failure is invisible in tests that insert a single row, and it is invisible in tests that insert two rows with distinct values.

It only appears when two rows share a NULL. That case is rarely in the test suite because it requires a deliberate fixture. The result is a constraint that passes review, passes CI, and fails in production under retry pressure.

A second factor is ORM behavior. Many ORMs generate a unique constraint from a model field without surfacing nullability semantics. The developer sees unique=True and assumes the database enforces it for all values.

What should you check first?

Start with the tables that carry deduplication logic. For each one, ask whether the unique key can be NULL, and whether the application relies on the constraint for idempotency.

• List unique indexes and their column nullability.
• Identify ingestion paths that can produce a NULL key.
• Decide whether NULL is valid or a defect.
• Apply NOT NULL, a partial index, or normalization accordingly.

If the answer is "the key should never be null," enforce it in the schema. If it can be null, make the partial index explicit so the next reader understands the guarantee.

Run one query against your catalog today: find every unique index whose column is nullable, and check whether any of them guards a retry loop. Which of your deduplication constraints are you trusting without having tested the NULL case?