[Solved] Why does a UNIQUE index allow multiple NULL values but still enforce uniqueness on non-NULL values? — Ans: Because NULL means unknown, so NULLs are not considered equal | SQL

SQL - Indexes and Query Performance

Why does a UNIQUE index allow multiple NULL values but still enforce uniqueness on non-NULL values?

ABecause UNIQUE indexes ignore NULL values completely

BBecause NULL is treated as zero internally

CBecause NULL means unknown, so NULLs are not considered equal

DBecause NULL values are automatically converted to unique strings

Step-by-Step Solution

Solution:

Step 1: Understand the meaning of NULL in SQL
NULL represents unknown or missing data, so two NULLs are not equal to each other.
Step 2: Explain UNIQUE index behavior with NULLs
Since NULLs are not equal, UNIQUE index allows multiple NULLs but enforces uniqueness on actual known values.
Final Answer:
Because NULL means unknown, so NULLs are not considered equal -> Option C
Quick Check:
NULLs are unknown, so multiple allowed in UNIQUE [OK]

Quick Trick: NULL means unknown, so multiple NULLs allowed [OK]

Common Mistakes:

Master "Indexes and Query Performance" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

Why does a UNIQUE index allow multiple NULL values but still enforce uniqueness on non-NULL values?