[plug] [OT] MySQL Question....

[Bernd Felsche]

"Timothy White" <weirdit at gmail.com> writes:

>Recently while designing a database, I discovered that only DB2 has a
>"UNIQUE IF NOT NULL" for index's. MySQL does not. :(
>I have the need for a 'unique or null' index. Setting the index to
>Unique, treats NULL as a value, so I can only have 1 NULL value.

Sounds like a bug. Smells like a bug with the fix being pushed to
version 5 of the MySQL engine.  http://bugs.mysql.com/bug.php?id=15133

IIRC from my SQL indoctrination in 1989/1990, NULL is never *equal*
to anything. That implies that an index with a NULL in a column can
have several UNIQUE rows with all other data being identical.

In SQL-world, NULL means *unknown*. The equality of two unknowns is
unknown. The uniqueness of the rows is only "broken" if identical
values fill the previously-NULL.

An index should treat NULL as unknown and therefore assume that the
NULL in that column doesn't match any value; or NULL itself.

That doesn't btw mean that you can't explicitly test for NULL
(unknown).

>Anyone know how I might be able to do this? Or is it a case of having
>to redesign that part of the database??

Look at the DB design. One should avoid indexing on stuff that could
be unknown. Do NOT assign a "dummy" value. That would be regretable
in the long run.

You could switch to Postgres which appears to treat NULL correctly
http://www.postgresql.org/docs/7.3/interactive/indexes-unique.html
(URL provides advice on how to handle the NULL with an additional
index.)

Or wait for the MySQL fix.
-- 
/"\ Bernd Felsche - Innovative Reckoning, Perth, Western Australia
\ /  ASCII ribbon campaign | "Laws do not persuade just because
 X   against HTML mail     |  they threaten."
/ \  and postings          | Lucius Annaeus Seneca, c. 4BC - 65AD.