List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:December 11 2006 2:48pm
Subject:Re: UNIQUE KEY vs NULLs
View as plain text  
> 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

Thread
UNIQUE KEY vs NULLsimre11 Dec
RE: UNIQUE KEY vs NULLsemierzwa11 Dec
Re: UNIQUE KEY vs NULLsDan Buettner11 Dec
  • RE: UNIQUE KEY vs NULLsimre12 Dec
Re: UNIQUE KEY vs NULLsMartijn Tonies11 Dec