CODEWITHSUNDEEP
mysqlcase-sensitiveunique-constraintunique-key
Sathya
Sathya

Reputation: 233

MYSQL: How to make composite unique constraint case insensitive

Table Query:

CREATE TABLE `TESTING_TABLE` (
   `ID` char(36) COLLATE utf8_bin NOT NULL,
   `NAME` char(36) COLLATE utf8_bin NOT NULL,
   `DISPLAY_NAME` varchar(128) COLLATE utf8_bin NOT NULL,
   UNIQUE KEY `DISPLAY_NAME_UK` (`NAME`,`DISPLAY_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

First row insert:

INSERT INTO `TESTING_TABLE` (`ID`, `NAME`, `DISPLAY_NAME`) VALUES ('1', 'Foo', 'Bar');

Second row insert:

INSERT INTO `TESTING_TABLE` (`ID`, `NAME`, `DISPLAY_NAME`) VALUES ('2', 'Foo', 'bar');

As I have 'DISPLAY_NAME_UK' unique constraint, I expect the second row insert should throw a "Duplicate entry" exception. But it is allowing case sensitive values in DISPLAY_NAME column. How to make it case insensitive?

Upvotes: 0

Views: 694

Answers (2)

Anch0rman
Anch0rman

Reputation: 85

Replace utf8_bin with utf8_general_ci

CREATE TABLE `TESTING_TABLE` (
`ID` char(36) COLLATE utf8_bin NOT NULL,
`NAME` char(36) COLLATE utf8_bin NOT NULL,
`DISPLAY_NAME` varchar(128) COLLATE utf8_general_ci NOT NULL,
UNIQUE KEY `DISPLAY_NAME_UK` (`NAME`,`DISPLAY_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Upvotes: 2

Mureinik
Mureinik

Reputation: 311228

If you want "bar" and "Bar" to be treated as the same value, this isn't case sensitivity, it's case insensitivity.

The easiest approach would probably be to use a case-insensitive collation, like utf8mb4.

Upvotes: 1

Related Questions