List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:June 18 2002 3:54pm
Subject:Re: Nulls and unique indexes.
View as plain text  
That is correct.
Use NOT NULL in the definition to force unique keys.

jon.barker@stripped wrote:

>>Description:
>>
>  If a null column is used in a unique index then mysql fails to throw a
> key violation when multiple identical inserts (with nulls) are
> performed.
>
>>How-To-Repeat:
>>
>  create table test (a int null, b int null);
>  alter table test add unique index index_ab (a,b);
>  insert into test (a,b) values(1,null);
>  insert into test (a,b) values(1,null);
>  insert into test (a,b) values(1,null);
>  select * from test;
>  +------+------+
>  | a    | b    |
>  +------+------+
>  |    1 | NULL |
>  |    1 | NULL |
>  |    1 | NULL |
>  +------+------+
>  3 rows in set (0.00 sec)
>
>  insert into test (a,b) values(1,1);
>  insert into test (a,b) values(1,1);
>  ERROR 1062: Duplicate entry '1-1' for key 1
>
>>Fix:
>>
> No fix.
>
>>Submitter-Id:	<submitter ID>
>>Originator:	Jon Barker
>>Organization:
>>
> 
>
>>MySQL support: none
>>Synopsis:	NULLs don't work correctly in unique indexes.
>>Severity:	serious
>>Priority:	
>>Category:	mysql
>>Class:		
>>Release:	mysql-3.23.41 (Official MySQL RPM)
>>
>
>>Environment:
>>
>	
>


Thread
Nulls and unique indexes.jon.barker18 Jun
  • Re: Nulls and unique indexes.Gerald Clark18 Jun
    • Re: Nulls and unique indexes.Jon Barker18 Jun