Doctrine ORM unique string is not case sensitive

Question

I got an Entity with field like that:

/**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255, unique=true)
     */
    private $name;

But if i try to fix a typo in name from "test" to "Test" i got an error that this name is already taken! Any idea why is that happening? Is an unique index case insenstive?

It's likely a problem with the database. Here's a question for mysql http://stackoverflow.com/questions/6888279/is-there-any-way-to-make-a-unique-index-case-insensitive-in-mysql-5-1-x — lz., Dec 01 '13 at 11:07
There is another question about this case i think: http://stackoverflow.com/questions/6448825/sql-unique-varchar-case-sensitivity-question — BENARD Patrick, Dec 01 '13 at 11:08

score 8 · Accepted Answer · edited May 08 '15 at 15:35

Your problem is because you have utf8_general_ci collation note the _ci which means case-insensitive, in turn that means that database treats "Test", "test", "TEst" etc. identically.

You will have to change column collation in the database manually to utf8_bin, or to utf8_xxx_cs (where xxx is language name) if you happen to have one for the needed language.

From Doctrine ORM FAQ:

4.1.1. How do I set the charset and collation for MySQL tables?

You can’t set these values inside the annotations, yml or xml mapping files. To make a database work with the default charset and collation you should configure MySQL to use it as default charset, or create the database with charset and collation details. This way they get inherited to all newly created database tables and columns.

I found a PR on github https://github.com/doctrine/dbal/pull/245 so at least someone working on it. — lz., Dec 01 '13 at 11:14

Doctrine ORM unique string is not case sensitive

1 Answers1