🗄️ The Unique Constraint That Let Two Identical Rows Through Anyway
A unique constraint on a column is expected to guarantee no two rows ever share the same value – but SQL Server (following the ANSI SQL standard) treats NULL as never equal to anything, including another NULL, so a unique constraint on a nullable column allows an unlimited number of rows with NULL in that column, even though every one of them looks like an identical ‘duplicate’ at a glance.
🔎 The Problem
CREATE TABLE Employees (
Id INT PRIMARY KEY,
Email NVARCHAR(255) NULL,
CONSTRAINT UQ_Employees_Email UNIQUE (Email)
);
INSERT INTO Employees (Id, Email) VALUES (1, NULL);
INSERT INTO Employees (Id, Email) VALUES (2, NULL);
INSERT INTO Employees (Id, Email) VALUES (3, NULL);
-- All three inserts succeed without any constraint violation. The
-- unique constraint is working exactly as the SQL standard defines it -
-- NULL is never considered equal to another NULL, so it never triggers
-- a uniqueness conflict - but from a business standpoint, three
-- employees with no email address on file looks exactly like three
-- "duplicate" rows the constraint was supposed to prevent.
✅ Fix: Decide Explicitly What NULL Should Mean Here
- If the column should genuinely never allow more than one NULL (or shouldn’t allow NULL at all), making the column `NOT NULL` is the simplest fix – it forces every row to have an actual, comparable value, which restores the uniqueness guarantee people generally expect.
- If NULL needs to remain allowed but still capped (at most one row without an email, say), a filtered unique index (`CREATE UNIQUE INDEX … WHERE Email IS NOT NULL` handles the non-NULL case, combined with a separate check or a computed column trick for the NULL case) achieves the specific business rule the plain unique constraint alone can’t express.
- For a case needing ‘no two rows share a value, treating NULL as one specific shared value too,’ a computed column that maps NULL to a specific sentinel value, with the unique constraint applied to THAT computed column instead of the original, sidesteps the standard NULL-comparison behavior entirely.
⚠️ Why This Behavior Is Correct, Not a Bug
- This isn’t a SQL Server quirk – it’s the ANSI SQL standard’s definition of NULL as ‘unknown,’ and two unknowns are never confirmed to be equal to each other, which is philosophically consistent even though it produces a business result that frequently surprises people expecting ‘unique’ to mean ‘no two rows look the same.’
- This is worth checking specifically on any column that’s both nullable AND has a uniqueness requirement in the business logic – the two properties coexisting is a common, easy-to-overlook source of ‘duplicate’ data that the database itself was never actually going to catch.
A unique constraint doesn’t promise no duplicates – it promises no two comparable values are equal, and NULL was never on the list of things SQL considers comparable to itself.
