Bug #936 INDEX

Description:
Why case sensitive doesnt work in unique index ?

For example if I define one :

CREATE UNIQUE INDEX TEST ON ASSAY(CODE(10))

and insert one code="HEM" and after try insert code="hem", the sql return one error <<duplicate value on index>>.

 

How to repeat:

Look ahead

Because the text fields by default are not case sensitive. You have to use
the VARCHAR(xx) BINARY if you want to have a case sensitive column:

mysql> create table casetest ( field1 varchar(10) binary) ;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table casetest add unique (field1);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into casetest values ('HEM');
Query OK, 1 row affected (0.01 sec)

mysql> insert into casetest values ('hem');
Query OK, 1 row affected (0.00 sec)

mysql> select * from casetest;
+--------+
| field1 |
+--------+
| HEM    |
| hem    |
+--------+
2 rows in set (0.00 sec)