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.

Why follow me on Twitter?

  • I tweet about new technologies, services or libraries I find interesting
  • Yeah, sometimes I'll post a pet-peeve or rant about something trivial
  • If I discover something that made my web development life easier, I share it
  • I'll shout out any handy tip that I think might be useful to other devs

Tagged , .

Updated: 2011-04-29

Phil LaNasa follow us in feedly