In SQL Server, technically, NULL = NULL is false. By that logic, no NULL is equal to any NULL and all NULLs are distinct. Shouldn't this imply that all NULLs are unique, and a unique index should allow any number of NULLs?
Comments are not for extended discussion; this conversation has been moved to chat.– Paul White ♦Aug 29, 2017 at 9:48
Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with
NULLs, and can coerce different behavior at will). That decision dictated that, in this case,
NULL = NULL.
It wasn't a very smart decision. What they should have done is have the default behavior adhere to the ANSI standard, and if they really wanted this peculiar behavior, allow it through a DDL option like
WITH CONSIDER_NULLS_EQUAL or
Of course, hindsight is 20/20.
And we have a workaround, now, anyway, even if it isn't the cleanest or most intuitive.
You can get the proper ANSI behavior in SQL Server 2008 and above by creating a unique, filtered index.
CREATE UNIQUE INDEX foo ON dbo.bar(key) WHERE key IS NOT NULL;
This allows more than one
NULL value because those rows are completely left out of the duplicate checking. As an added bonus, this would end up being a smaller index than one that consisted of the entire table if multiple
NULLs were allowed (especially when it isn't the only column in the index, it has
INCLUDE columns, etc). However, you may want to be aware of some of the other limitations of filtered indexes:
Correct. The implementation of a unique constraint or index in sql server allows one and only one NULL. Also correct that this technically doesn't fit with the definition of NULL but it's one of those things they did to make it more useful even though it isn't "technically" correct. Note a PRIMARY KEY (also a unique index) does not allow NULLs (of course).
1This (SQL-Server's) technicality also doesn't fit with the SQL standard. There is a 7-year old Connect item about this issue. Oct 17, 2014 at 18:39
@ypercube True. That's why I said it was just the implementation and doesn't really fit in with the definition of NULL. I hadn't thought about the filtered unique index (although I've used it for other things.) Oct 17, 2014 at 19:27
This may not be technically accurate, but philosophically it helps me sleep at night...
Like several other have said or alluded to, if you think of NULL as unknown, then you cannot determine whether one NULL value is in fact equal to another NULL value. Thinking of it this way, the expression NULL == NULL should evaluate to NULL, meaning unknown.
A Unique constraint would need a definitive value for the comparison of the column values. In other words, when comparing a single column value against any other column value using the equality operator, it must evaluate to false to be valid. Unknown isn't really false even though it is often treated as falsy. Two NULL values could be equal, or not... it simply can't be definitively determined.
It helps to think of a unique constraint as restricting values that can be determined to be distinct from one another. What I mean by this is if you run a SELECT that looks something like this:
SELECT * from dbo.table1 WHERE ColumnWithUniqueContraint="some value"
Most people would expect one result, given that there is a unique constraint. If you allowed multiple NULL values in ColumnWithUniqueConstraint, then it would be impossible to select a single distinct row from the table using NULL as the compared value.
Given that, I believe that regardless of whether or not it's implemented accurately with respect the definition of NULL, it's definitely a lot more practical in most situations than allowing multiple NULL values.
Your Select will do give 1 result, when there is a Unique constraint (in any implementation, not only SQL-Server). What is your point? Oct 18, 2014 at 10:38
First - stop using the phrase "Null value", it will just lead you astray. Instead, use the phrase "null marker" - a marker in a column indicating that the actual value in this column is either missing or inapplicable (but note that the marker does not say which of those options is actually the case¹).
Now, imagine the following (where the database does not have complete knowledge of the modeled situation).
Situation Database ID Code ID Code -- ----- -- ----- 1 A 1 A 2 B 2 (null) 3 C 3 C 4 B 4 (null)
The integrity rule we are modelling is "the Code must be unique". The real-world situation violates this, so the database shouldn't allow both items 2 and 4 to be in the table at the same time.
The safest, and least-flexible, approach would be to disallow null markers in the Code field, so there is no possibility of inconsistent data. The most flexible approach would be to allow multiple null markers and worry about uniqueness when values are entered.
The Sybase programmers went with the somewhat-safe, not-very-flexible approach of only allowing one null marker in the table - something commentators have been complaining about ever since. Microsoft have continued this behaviour, I guess for backwards compatibility.
¹ I am sure I read somewhere that Codd considered implementing two null markers - one for unknown, one for inapplicable - but rejected it, but I can't find the reference. Am I remembering correctly?
P.S. My favourite quote about null: Louis Davidson, "Professional SQL Server 2000 Database Design", Wrox Press, 2001, page 52. "Boiled down to a single sentence: NULL is evil."
1Allowing a single
nulldoesn't achieve this goal either. Because the missing value might turn out to be the same as the value in one of the other rows. Oct 20, 2014 at 5:13
1What @MartinSmith said. What if you have a check constraint
CHECK (Value IN ('A','B','C','D'))? Then both SQL-Server's implementation and the SQL standard allow the table to have 5 rows (one row for each value plus 1 with NULL.) Then, arguably, while the database is consistent with its constraints, it's not consistent with the designer's intent for the table to have max of 4 rows. There is no value that the NULL can be changed into that will not violate a constraint, unless one or more rows are deleted. Oct 20, 2014 at 8:49
1The fact that the standard would allow 6 even 106 rows instead of 5 does not change that they both fail the some way in this scenario. Oct 20, 2014 at 8:50
@Martin Smith, it might, but then again, it might not - the database server can't tell so it doesn't risk it and takes the safe route. That's what the Sybase (I presume) programmers decided, causing annoyance ever since (at least as far back as Inside SQL Server 6.5, the oldest book on my bookshelf, where Ron Soukup makes much the same comment that Aaron Bertrand does in his answer). I guess it could be worse - they could have mandated no null markers. :-) Oct 20, 2014 at 9:49
3@GreenstoneWalker - It doesn't take the "safe" route. It assumes that the missing value won't conflict.
CREATE TABLE #T(A INT NULL UNIQUE);INSERT INTO #T VALUES (1),(NULL);UPDATE #T SET A = 1 WHERE A IS NULL;will raise an error. According to your theory of the design motivations it ought to have prevented the insertion of
NULLin the first case - because the incomplete knowledge means there is no guarantee that the value is different. Oct 20, 2014 at 12:05
One of the major purposes of a
UNIQUE constraint is to prevent duplicate records. If one needs to have a table in which there can be multiple records where a value is "unknown", but no two records are allowed to have the same "known" value, then the unknown values should be assigned artificial unique identifiers before they are added to the table.
There are a few rare cases in which a column which has a
UNIQUE constraint and contains a single null value; for example, if a table contains a mapping between column values and localized text descriptions, a row for
NULL would make it possible to define the description that should appear when that column in some other table is
NULL. The behavior of
NULL allows for that usage case.
Otherwise, I see no basis for a database with a
UNIQUE constraint on any column to allow the existence of many identical records, but I see no way to prevent that while allowing multiple records whose key values are not distinguishable. Declaring that
NULL isn't equal to itself won't make
NULL values distinguishable from each other.
3Artificial unique identifiers are a joke, sorry. How are you going to do that for a VIN? If you don't know what it is, why make something up? Just to take up extra disk space? Seems like nonsense to work around some other problem (like not wanting to write the application in such a way that it gracefully handles NULLs). If you absolutely need to know why something is NULL (exists but unknown vs. know it doesn't exist vs. don't know or care if it exists, for example), then add some kind of status column. Tokens just lead to awkward trickle-down code to deal with them. Oct 18, 2014 at 21:47
A lot depends upon the purpose of the uniqueness constraint. If a field will be used as an identifier, it shouldn't be null. In cases (as with VINs) where business rules would suggest that when an item appears twice, one of them must be wrong, but some items may be "don't know", a uniqueness constraint doesn't feel like the proper approach. If one has a vehicle with a known VIN, and it conflicts with another in the database, one may know that at least one of the VINs is wrong, but it would be better to have the database report the believed value for both records than guess that one is right.– supercatOct 18, 2014 at 22:02
@AaronBertrand: There are some cases where a possibly-null unique-if-not-null field would need to be a surrogate key couldn't be established in advance of populating the field (e.g. "spouse ID"), but in situations like that a "unique" constraint would be insufficient; it would be necessary that if X.Spouse is non-null, X.Spouse.Spouse=X. Incidentally, something like "spouse" might also be handled by saying that the record for an unmarried person shouldn't have "NULL" as a spouse, but rather its own ID, in which case the X.spouse.spouse=X rule could apply to everyone.– supercatOct 18, 2014 at 22:12