Making unique key case insensitive

Question

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

score 3 · Accepted Answer · answered Apr 01 '13 at 05:22

3

The simpliest is to add BINARY on the DDL statement,

`NAME` varchar(128) BINARY NOT NULL

SQLFiddle Demo

answered Apr 01 '13 at 05:22

John Woo

258,903
69
498
492

@J W : i changed collation for NAME aatribute to utf8_bin. Im using SQLYOG. Same issue is there. – Rosh Apr 01 '13 at 05:57
how about adding `BINARY`? what do you mean by same issue? – John Woo Apr 01 '13 at 06:08
2

How can this solve the problem? It's even not using `UNIQUE` constraint – Ifan Iqbal Jan 22 '14 at 00:48

score 1 · Answer 2 · answered Apr 01 '13 at 05:34

1

If you need case sensitive:

ALTER TABLE `DATA_USER_ROLE` CHANGE `NAME` `NAME` VARCHAR( 128 )
CHARACTER SET latin1 COLLATE latin1_bin NULL DEFAULT NULL

If you need case insensitive:

ALTER TABLE `DATA_USER_ROLE` CHANGE `NAME` `NAME` VARCHAR( 128 )
CHARACTER SET latin1 COLLATE latin1_general_ci NULL DEFAULT NULL

answered Apr 01 '13 at 05:34

Mayukh Roy

1,815
3
19
31

In any case, this answer would be far more helpful with a bit more explanation. – Michael Scheper Jul 28 '15 at 20:46

ergohack · Answer 3 · 2021-11-08T15:58:14.590

A case insensitive COLLATE can be added on the whole table, i.e. every text column in the table, e.g.

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=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

In this case, the _ci at the end of the COLLATE stands for case insensitive.

This follows Monolune's recommendation for CHARSET and COLLATE from https://www.monolune.com/mysql-utf8-charsets-and-collations-explained/ and
https://www.monolune.com/what-is-the-utf8mb4_0900_ai_ci-collation/

These are the defauts since MySQL 8.0.1 .

score 0 · Answer 4 · answered Sep 28 '16 at 08:48

0

You can do it in such a way..

create table testTable (col_name varchar(100) character set utf8 
collate utf8_bin not null ) Engine=innodb default charset=utf8

answered Sep 28 '16 at 08:48

Tarun Singhal

977
8
11

Alex Joe · Answer 5 · 2015-10-16T09:47:52.197

-1

Just use utf8_general_ci (case insensitive) charset by default for your db or only for one row, if you needed

edited Oct 16 '15 at 09:47

answered Oct 16 '15 at 08:09

Alex Joe

369
3
8

Making unique key case insensitive

5 Answers5

Related