How can I get MySQL to accept both resume and resumé on a primary key?

Question

If I add both then the uniqueness constraints fail, even though my field is in UTF-8. What can be done? I'm running 5.5.16

mysql> show create table foo_person;
+------------+------------------------------------------------+
| Table      | Create Table                                   |
+------------+------------------------------------------------+
| foo_person | CREATE TABLE `foo_person` (
  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+------------+------------------------------------------------+

mysql> INSERT INTO foo_person (`name`) VALUES ('resumé');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO foo_person (`name`) VALUES ('resume');
ERROR 1062 (23000): Duplicate entry 'resume' for key 'PRIMARY'

mysql> select * from foo_person;
+---------+
| name    |
+---------+
| resumé  |
+---------+
1 row in set (0.00 sec)

Answer 1

It seems that MySQL does not support Accent Sensitive collations. (See http://dev.mysql.com/doc/refman/5.6/en/charset-collation-implementations.html)

Therefore your only option is to go with a binary collation, e.g. utf8_bin. However, that would also make the column case sensitive, which is different from your current collation.

See also https://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql