MySQL Unique Indexes Allow Multiple NULLs: Why It Happens and How to Enforce True Uniqueness

Empty Phone Numbers Insert Successfully Three Times

The user_profile table has a unique index on phone defined as VARCHAR(20) DEFAULT NULL. After enabling third-party login, a batch of users without phone numbers arrived, resulting in three rows with phone = NULL. No Duplicate entry error occurred. The MySQL manual states clearly in the CREATE INDEX section: a unique index allows multiple NULLs provided the column is nullable. The root cause is SQL's three-valued logic: NULL = NULL evaluates to UNKNOWN, so the equality comparison used for duplicate detection matches no rows and the check passes.

CREATE TABLE user_profile (
  id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  org_id  BIGINT UNSIGNED NOT NULL,
  phone   VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_phone (phone)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

INSERT INTO user_profile (org_id, phone) VALUES (1, NULL);
Query OK, 1 row affected

INSERT INTO user_profile (org_id, phone) VALUES (1, NULL);
Query OK, 1 row affected

INSERT INTO user_profile (org_id, phone) VALUES (1, NULL);
Query OK, 1 row affected

INSERT INTO user_profile (org_id, phone) VALUES (1, '13800000000');
Query OK, 1 row affected

INSERT INTO user_profile (org_id, phone) VALUES (1, '13800000000');
ERROR 1062 (23000): Duplicate entry '13800000000' for key 'user_profile.uk_phone'

Three NULLs all land; switching to a real number stops the second insert. Note the column declaration: DEFAULT NULL without NOT NULL. Changing the column to phone VARCHAR(20) NOT NULL produces a different error: ERROR 1048 (23000): Column 'phone' cannot be null. The two constraints govern separate domains — NOT NULL governs value existence, the unique index governs value duplication, and the null-value gap in between is unguarded by either.

Index Entries Exist but Duplicate Check Misses Them

InnoDB writes index entries for all three rows. The secondary index leaf nodes are sorted by phone; NULL is treated as the smallest value and placed at the far left. The three NULL records sit physically adjacent with no overwriting. Building the index does not apply a "NULL not indexed" optimization — SELECT * FROM user_profile WHERE phone IS NULL uses uk_phone and returns three rows, proving the entries exist. Duplicate checking is a separate step: before insert, InnoDB probes the index with the new value using an equality condition phone = ?. When the new value is NULL, the equality comparison always yields UNKNOWN, the probe finds no conflicting record, and each NULL passes through independently.

Cross-Database Comparison

Different engines choose different semantics for NULL in unique indexes, hinging on whether NULL is considered equal to NULL.

MySQL 8.4: Allows multiple NULLs.

PostgreSQL 15+: Allows multiple by default; with NULLS NOT DISTINCT allows only one.

SQL Server: Allows only one NULL.

Oracle 23: Allows multiple NULLs.

PostgreSQL exposes the choice, SQL Server has treated NULL as equal from the start, and MySQL through 8.4 still follows the manual's "allows multiple". This difference travels with migration scripts: a schema built in PostgreSQL and imported into MySQL relaxes the UNIQUE semantics silently; the script runs without error until production starts accumulating duplicate NULL rows.

ON DUPLICATE KEY UPDATE Degrades to Pure Insert

ON DUPLICATE KEY UPDATE

relies entirely on the unique index's conflict detection. When detection fails for NULL, the statement degrades to a plain INSERT.

INSERT INTO user_profile (org_id, phone)
VALUES (1, NULL)
ON DUPLICATE KEY UPDATE org_id = VALUES(org_id);

Running this three times adds three rows; affected rows returns 1 each time (meaning "inserted one row"). With '13800000000' the second run returns 2 (meaning "updated one row"). Many codebases use the affected-row count to distinguish create vs. update: upsert() returning true is treated as "record existed and was updated", logs show no anomaly, but manual data inspection reveals three profiles for the same user. REPLACE INTO follows the same detection — it searches by unique index, deletes the old row if found, then inserts. NULL never finds a match, so every execution is a clean insert, row count grows, auto-increment ID climbs, and downstream sharding by ID produces orphaned records. INSERT IGNORE behaves similarly: it depends on conflict detection to decide "ignore"; NULL rows create no conflict, so they are neither rejected nor ignored — they are quietly written. All three statements fail differently but share the same root: they ask the unique index "does this record exist?" and the index answers "cannot determine" for NULL.

GROUP BY Says Duplicate, Unique Index Says Not

Within the same database, the criterion for "is duplicate" is not uniform. The same record yields opposite answers depending on how you ask.

SELECT phone, COUNT(*) AS c
FROM user_profile
GROUP BY phone;

phone           c
NULL            3
13800000000     2

GROUP BY

groups the three NULLs into one bucket; DISTINCT also returns a single NULL. Aggregation and deduplication use hash or sort comparison where NULLs are equal to each other, bypassing three-valued logic. This explains a recurring misdiagnosis: inspection scripts using GROUP BY ... HAVING COUNT(*) > 1 correctly surface the three NULLs, yet the unique index never blocked them, leading someone to suspect the index was never created — SHOW INDEX confirms it exists. Conversely, an inspection that adds WHERE phone IS NOT NULL (a common pattern to avoid NULL noise) returns zero rows, and the operator concludes the data is clean. Both criteria are internally consistent; the mismatch lives entirely in NULL handling. Practical takeaway: GROUP BY results can detect duplicates, while the unique index state determines whether duplicates can be written. These two verifications must be run separately; only then can you tell if the data itself is dirty or the index simply failed to enforce.

Replace NULL with a Comparable Value

To make the unique index govern NULLs, the indexed column must not contain NULL. Several implementation approaches exist, each with different trade-offs.

1. Make the column NOT NULL with a default value phone VARCHAR(20) NOT NULL DEFAULT '' eliminates NULL; duplicate checking resumes normally. Cost: "not filled" and "filled with empty string" collapse into one category, requiring translation when exporting to data warehouses or downstream systems. More critically, the unique index on '' also permits only one occurrence, so a second batch of users without phone numbers would be blocked outright. This approach suits only scenarios where multiple NULLs should never exist, e.g., an order's cancellation reason.

2. Generated column (available since MySQL 5.7)

Add a column that maps NULL to a sentinel, then create a unique index on that column.

ALTER TABLE user_profile
  ADD COLUMN phone_key VARCHAR(24)
    GENERATED ALWAYS AS (IFNULL(phone, '__null__')) STORED,
  ADD UNIQUE KEY uk_org_phone_key (org_id, phone_key);

Within the same organization only one NULL phone is allowed; the second attempt raises ERROR 1062 with the key value shown as '1-__null__'. The generated column expression is visible in SHOW CREATE TABLE and in execution plans, making troubleshooting straightforward.

3. Functional index (since MySQL 8.0.13)

No extra column needed; the expression lives directly in the index definition.

ALTER TABLE user_profile
  ADD UNIQUE KEY uk_org_phone (org_id, (IFNULL(phone, '__null__')));

Saves a column but reduces readability. SHOW CREATE TABLE shows a parenthesized expression; error messages contain only the computed result, so a person reading the schema must parse the expression to understand the key composition. Functional indexes cannot be referenced by foreign keys, and cross-version rollback is more complicated than for ordinary indexes.

4. Application-level check-then-insert

Two threads simultaneously query, both see "not exist", both insert — this race window rarely appears in single-user operation but occurs constantly during bulk imports and message retries. It can serve as a supplementary guard but cannot withstand concurrency.

The first three options all require schema changes; choice depends on the team's tolerance for migrations. Preference leans toward generated columns: the expression is visible in the table definition, error messages, execution plans, and column listings all align, reducing investigation loops when issues arise.

Existing Data Must Be Cleaned First

Regardless of the chosen solution, existing duplicate rows must be resolved before altering the table. Adding NOT NULL will fail; a generated column's unique index will throw 1062 at DDL time.

SELECT org_id, phone, COUNT(*) AS c
FROM user_profile
WHERE phone IS NULL
GROUP BY org_id, phone
HAVING c > 1;

Cleanup rules are business-defined: keep the row with the smallest id, merge or soft-delete the rest. After cleaning, rerun the same query to confirm an empty result set. For large tables, DDL should use the online method; MySQL 8.0 adds unique indexes with INPLACE by default, which does not block reads/writes but consumes extra temporary space and execution time proportional to table size.

It is also worth scanning existing indexes to find unique keys on nullable columns. Run this query on every table with a unique key:

SELECT index_name, column_name, nullable
FROM information_schema.statistics
WHERE table_schema = 'app'
  AND table_name = 'user_profile'
  AND non_unique = 0;

Rows where nullable = YES are the ones to watch. These indexes behave perfectly while no NULLs exist; once upstream starts sending NULLs they silently stop enforcing uniqueness, yet ALTER TABLE ... ADD UNIQUE KEY on such a table succeeds — the index is created, but the constraint is not actually effective.