When using a uniqie index on a text field in mysql, the column collation setting is very important. The collation settings of a column does not only affect sorting and comparsion, but also unique indexes. So you can not insert “a” and “A” into a table that has a unique index on a column that has a case-insensitive collation. The mysql manual about collations: “A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.”

Here is an example:
The column text in table text1 has a case-sensitive collation (_cs suffix), the column in text2 has a case-insensitive collation (_ci suffix).

CREATE TABLE text1 (
  `text` varchar(50) character set latin1 collate latin1_general_cs NOT NULL default '',
  UNIQUE KEY uniq_text (text)
) ENGINE=MyISAM
;
INSERT INTO text1 (text) VALUES ('a');
Query OK, 1 row affected (0.00 sec)

INSERT INTO text1 (text) VALUES ('A');
Query OK, 1 row affected (0.00 sec)

CREATE TABLE text2 (
  `text` varchar(50) character set latin1 collate latin1_general_ci NOT NULL default '',
  UNIQUE KEY uniq_text (text)
) ENGINE=MyISAM
;
INSERT INTO text2 (text) VALUES ('a');
Query OK, 1 row affected (0.00 sec)

INSERT INTO text2 (text) VALUES ('A');
ERROR 1062 (23000): Duplicate entry 'A' for key 1

Constraints are also affected by collation:
The queries on table text1 give different results (a and A), the two queries on table text2 result in the same row twice (a).

SELECT * FROM text1 WHERE text = 'a';
+------+
| text |
+------+
| a    |
+------+
1 row in set (0.01 sec)

SELECT * FROM text1 WHERE text = 'A';
+------+
| text |
+------+
| A    |
+------+
1 row in set (0.00 sec)

SELECT * FROM text2 WHERE text = 'a';
+------+
| text |
+------+
| a    |
+------+
1 row in set (0.00 sec)

SELECT * FROM text2 WHERE text = 'A';
+------+
| text |
+------+
| a    |
+------+
1 row in set (0.00 sec)

You can display all available collations using

SHOW COLLATION;

or

SHOW COLLATION LIKE 'latin%';

Popularity: 30% [?]