Rosh Rosh - 2 months ago 20
SQL Question

Making unique key case insensitive

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

Answer

The simpliest is to add BINARY on the DDL statement,

`NAME` varchar(128) BINARY NOT NULL
Comments