MySQL Duplicate Key issues

So you're doing a bulk import into your MySQL database, knowing that your primary key values are unique. Yet, MySQL throws the Duplicate Key message your way.

If your primary key is character based, and you are like most, you assigned it a data type of CHAR or VARCHAR.

However, these types are not case sensitive, meaning that if they are used as a primary key, uniqueness will be defined in a case-insensitive manner.

Try flipping your data type over to BINARY or VARBINARY respectively. Switching to VARBINARY should not disrupt your data, but BINARY may have unwelcomed resuilts for you if your values don't fill the required space.

BINARY and VARBINARY key uniqueness is assessed in a case-insensitive manner.


  • follow us in feedly

Tagged , .

Updated: 2011-04-29

Phil LaNasa