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; ```

Accepted Answer

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.

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

Solution

Problem