From: Martijn Tonies
Date: December 11 2006 2:48pm
Subject: Re: UNIQUE KEY vs NULLs
List-Archive: http://lists.mysql.com/mysql/203864
Message-Id: <037b01c71d33$74682b70$9902a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit


> I have an InnoDB table similar to this:
>
> CREATE TABLE Target
> (IMSI VARCHAR(15) ASCII,
>  IMEI VARCHAR(15) ASCII,
>  UNIQUE KEY (IMSI, IMEI));
>
> After playing a bit with it, I managed to add duplicate records, if one of
> the fields was a NULL:
>
> +-----------------+-----------------+
> | IMSI            | IMEI            |
> +-----------------+-----------------+
> | NULL            | 35195600126418  |
> | NULL            | 35195600126418  |
> +-----------------+-----------------+
>
>
> Is this a bug, or a feature? :-)

A feature. NULL isn't equal to NULL.

> If it is a feature, than how can I assure uniqueness for a table in a
sense
> that won't allow such duplicates?

If you don't want to have NULL, use a "primary key" instead
of a "unique key".


Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com