Re: Unique Index

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.

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(at)connx(dot)com> 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
>
>
>
>