Making unique key case insensitive
database-design, mysql, sql
Solution
The simpliest is to add `BINARY` on the DDL statement,
`NAME` varchar(128) BINARY NOT NULL
- SQLFiddle Demo
Problem
This is my schema for mysql table, im using mysql 5 ``` -- -- Table structure for table `DATA_USER_ROLE` -- DROP TABLE IF EXISTS `DATA_USER_ROLE`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `DATA_USER_ROLE` ( `ID` int(11) NOT NULL, `NAME` varchar(128) NOT NULL, `VAL_ID` int(11) NOT NULL, `CREATION_TIME` datetime NOT NULL, `ROLE_TYPE` int(11) NOT NULL, `STORAGE_TYPE` int(11) NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `BV_AC_ROLE_KEY_IDX` (`NAME`,`VAL_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ``` Needed UNIQUE KEY case in sensitive, It should allow to enter the value like('a',0) & ('A', 0) tried changing collation to latin_1 and latin_generic_ci