Problem Statement
You need a column that must be unique when present, but may be NULL for many rows. How do you enforce this across common engines?
Explanation
A plain UNIQUE often allows multiple NULLs, which is the desired behavior. If your engine treats NULLs as equal, emulate with a filtered or partial unique index on non-NULL rows. That enforces uniqueness only when a value exists.
This pattern keeps flexibility for missing values while preventing duplicates when the field is provided.
Code Solution
SolutionRead Only
-- PostgreSQL CREATE UNIQUE INDEX uniq_users_email ON users(email) WHERE email IS NOT NULL;
Practice Sets
This question appears in the following practice sets:
