Is there any way to make a UNIQUE index case insensitive in Mysql 5.1.x ?

Question

If so - What must change in this table ?

CREATE TABLE  contestants 
( 
  idContestants  int(10) unsigned NOT NULL AUTO_INCREMENT,
  idEvent        int(10) unsigned NOT NULL,
  ContestantName  varchar(50) DEFAULT NULL,
  PRIMARY KEY (idContestants),
  UNIQUE KEY Index_UniqueName (idEvent,ContestantName),
)
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

What does 'case instinctive' mean? – Ariel Jul 31 '11 at 08:12 — Ariel, Jul 31 '11 at 08:12
Do you mean case insensitive? – Stefan H Jul 31 '11 at 08:12 — Stefan H, Jul 31 '11 at 08:12

Ariel · Accepted Answer · 2015-09-03T04:13:07.910

23

If you mean case sensitive then:

ALTER TABLE `contestants` CHANGE `ContestantName` `ContestantName` VARCHAR( 50 )
CHARACTER SET latin1 COLLATE latin1_bin NULL DEFAULT NULL

If you mean case insensitive then:

ALTER TABLE `contestants` CHANGE `ContestantName` `ContestantName` VARCHAR( 50 )
CHARACTER SET latin1 COLLATE latin1_general_ci NULL DEFAULT NULL

For table level do (for case insensitive):

ALTER TABLE `contestants` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci

Note that table level only affects new columns.

For database level do (for case insensitive):

ALTER DATABASE `database_name` CHARACTER SET latin1 COLLATE latin1_general_ci

Note that database level only affect new tables.

edited Sep 03 '15 at 04:13

answered Jul 31 '11 at 08:16

Ariel

25,995
5
59
69

1

It is important to note that this is a COLUMN level collation, initially I read this too quickly and thought it was a TABLE level collation which didn't work for me. – Scott Jungwirth Sep 02 '15 at 18:37
1

@ScottJungwirth I added info about changing it at table and database level. – Ariel Sep 03 '15 at 04:13

score 4 · Answer 2 · answered Jul 31 '11 at 08:13

4

Yes, use a case-insensitive collation on the columns involved.

MySQL Manual :: Column Character Set and Collation

answered Jul 31 '11 at 08:13

Dan Grossman

51,866
10
112
101

score 0 · Answer 3 · answered Jun 28 '13 at 15:55

0

This worked for me in Mysql 5.5

ALTER TABLE `contestants` MODIFY
`ContestantName` VARCHAR(50) 
CHARACTER SET latin1
COLLATE latin1_bin;

answered Jun 28 '13 at 15:55

Hemerson Varela

24,034
16
68
69

Is there any way to make a UNIQUE index case insensitive in Mysql 5.1.x ?

3 Answers3

Linked