Re: Unique Index

Alex <alex(at)meerkatsoft(dot)com> writes:

> I actually just wanted to know if there is a way around this problem. Obviously
> it is implemented that way for whatever reason.

The way around is to make all the columns NOT NULL. For most applications
unique indexes don't make much sense on nullable columns.

> (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).

I think you have that backwards. You use NULL in the case where you want all
cases to compare as unknown values. If you want them to compare as known
values then you should use a special value.

In other words, NULL has special properties. You should use it if those
special properties are what you want. If you want the system to enforce a
unique constraint on the special value then you probably don't want to be
using NULL for that special state.

Specifically if you find yourself saying "we could safely assume they are
treated equal" then NULL is almost certainly not what you want to represent
that. NULL never compares equal to anything.

--
greg