Quick Answer: In SQL, unique constraints permit multiple NULL values because NULL represents an unknown state rather than an empty value, meaning two NULLs are never equal ( NULL = NULL evaluates to UNKNOWN ). While this prevents unintended collisions on optional single columns, it silently compromises composite keys unless you explicitly configure your index with NULLS NOT DISTINCT .

Imagine running a migration to enforce strict uniqueness on a database column, only to look at your production data a week later and find twenty rows that appear identical. Your initial instinct might be to suspect a transaction isolation bug or a race condition in your application layer.

In reality, your database is following the SQL standard to the letter.

Why does SQL permit duplicate NULL values in a UNIQUE column?

SQL relies on three-valued logic (True, False, and Unknown), which means a NULL value represents missing or unknown data rather than an empty string or zero.…