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

Question

If so, what should 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; 

Answer 1

If you mean case sensitivity, then:

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

If you mean case insensitivity, then:

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

For the table level do (for case insensitive):

 ALTER TABLE `contestants` DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci 

Note that the table level only affects new columns.

For the do database level (case insensitive):

 ALTER DATABASE `database_name` CHARACTER SET latin1 COLLATE latin1_general_ci 

Note that the database level only affects new tables.

Answer 2

Yes, use case insensitive sorting in the corresponding columns.

MySQL Guide :: Typing and Sorting Column Characters

Answer 3

This worked for me in mysql 5.5

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