MYSQL: How to make composite unique constraint case insensitive

Question

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?

Anch0rman · Accepted Answer · 2018-06-12 20:31:06Z

2

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;

answered Jun 12, 2018 at 20:31

Anch0rman

854 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2018-06-12 20:15:28Z

1

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.

answered Jun 12, 2018 at 20:15

Mureinik

316k54 gold badges400 silver badges405 bronze badges

2 Comments

Sathya Over a year ago

Getting exception - Error Code: 1273. Unknown collation: 'utf8mb4'

Bill Karwin Over a year ago

utf8mb4 is a character set name, not a collation name.

Collectives™ on Stack Overflow

MYSQL: How to make composite unique constraint case insensitive

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related