I actually just wanted to know if there is a way around this problem. Obviously it is implemented that way for whatever reason.
I still though think some arguments given in some of the replies, while probably correct, are besides the point.
Sorry. I was hoping someone else would answer.
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.
I can think of two options.
One was mentioned already. If only one row can have a null value then it seems to me that you should make it a non null and null would have a special code.
If it really needs to be null. Then a rather messy solution would be to have a second column (I'll call it a null indicator) which can only be 1 or null and have a unique index on it.
colA ind ------ ----- 1 null 2 null 3 null null 1
How do other DBMS handle this?
Oracle is the same.
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
