List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:December 11 2006 2:50pm
Subject:Re: UNIQUE KEY vs NULLs
View as plain text  
This is a feature - a NULL value is an undefined value, therefore two
NULL values are not the same.  Can be a little confusing but makes
sense when you think about it.

A UNIQUE index does ensure that non-NULL values are unique; you could
specify that your column not accept NULL values.

Dan


On 12/11/06, imre@stripped <imre@stripped> wrote:
> Hi,
>
> 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? :-)
>
> If it is a feature, than how can I assure uniqueness for a table in a sense
> that won't allow such duplicates?
>
> Thx
>
> ImRe
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>
>
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