Bug #936 INDEX

• Submitted: 28 Jul 2003 12:46
• Modified: 29 Jul 2003 5:35
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.0.13
• OS: Linux (Linux/Windows)
• 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)