Bug #936 INDEX
Submitted: 28 Jul 2003 12:46 Modified: 29 Jul 2003 5:35
Reporter: [ name withheld ] Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.13 OS:Linux (Linux/Windows)
Assigned to: CPU Architecture:Any

[28 Jul 2003 12:46] [ name withheld ]
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
[29 Jul 2003 5:35] Indrek Siitan
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)