Integrity constraint violation: 1062 Duplicate ent

Question

I have a table called tag with a unique constraint on the name column:

CREATE TABLE `tag` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_389B7835E237E06` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=13963 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The collation for this table is utf8_unicode_ci. When I try to insert the following 2 entries I get an "Integrity constraint violation" execption.

SQL log:

130607 14:35:53  1096 Connect   imtpdb@localhost on imtpdb
1096 Query     SET NAMES utf8
1096 Query     START TRANSACTION
1096 Query     INSERT INTO tag (name) VALUES ('até')
1096 Query     INSERT INTO tag (name) VALUES ('ate')
1096 Query     rollback
1096 Quit

The exact error message is:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'ate' for key 'UNIQ_389B7835E237E06'

my.cnf:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8


[mysqld]
default-character-set = utf8
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

I'm running mysql 5.1.54. I have been trying to figure this out for a while now to no avail. Does anyone know what I'm doing wrong? Ohh, just to state the obvious. The entry doesn't exist in the table already.

Answer 1

Collation utf8_unicode_ci is both case insensitive and accent insensitive, that's why it considers 'até' and 'ate' as duplicates. There is no unicode multilingual collation that is case insensitive and accent sensitive. Depending on your needs, you may try different workarounds. Some related questions:

• Is there a MySQL utf8 collation that will not conflate accented characters?
• MySQL Case Insensitive but Accent Sensitive UTF8 Unique Key
• How to conduct an Accent Sensitive search in MySql