I still though think some arguments given in some of the replies, while probably correct, are besides the point.
I use a unique index that may contain null values. On an insert or update I can now not rely on the exception thrown but actually have to write a select statement to check if the same row exists, which I believe defies ONE purpose of having unique indices. Whether Null is associated with "unknown value", "divided by zero"... or however one wants to interpret it is not the issue here, in my view NULL in the same column have the same value or at least should be treated the same. (If I want to differentiate the state, I would use a code instead of NULL as a NULL does not give any indication of its meaning, thus we could safely assume they are treated as equal).
Maybe there could be an option in the creation of the index to indicate on how to use NULL values.
How do other DBMS handle this?
A
Tom Lane wrote:
"Dann Corbit" <DCorbit@xxxxxxxxx> writes:
Or (perhaps better yet, violating trichotomy) ...
If <Some_column> has a null numeric value, then ALL of the following are
FALSE for that case:
Some_column < 0
Some_column > 0
Some_column = 0 Some_column <> 0 // This is the one that many find surprising
Some_column <= 0
Some_column >= 0
It's worse than that: the above do *not* yield FALSE, they yield NULL. Which does act like FALSE in a simple WHERE clause, but there are other cases (like CHECK clauses) where it doesn't. "x NOT IN (SELECT ...)" is a case that newbies routinely get bitten by.
Even at that, I think that being able to insert more than one null value
into a unique index should be considered as a bug (or diagnosed as an
error).
Direct your complaints to the ISO SQL standards committee.
regards, tom lane
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
