MySQL Unique Key Index, Max Key Length Limitation

MySQL Unique indexes has a max key length limitation of 1000 bytes. I was attempting to build a unique index based on 2 columns but was getting this error message:

Specified key was too long; max key length is 1000 bytes

Let’s see how this can be fixed.

First, let’s check the table structure. I notice that the 2 columns which I was trying to build my unique index upon was using the utf8_unicode_ci collation.

SKU – varchar(255) – utf8_unicode_ci
MANUFACTURER – varchar(255) – utf8_unicode_ci

Since utf8 is 3 bytes per character, this means that the unique key i’m trying to create could potentially be 1530 bytes [(255 characters + 255 characters)*3 bytes/character), and hence why MySQL was giving the error as mentioned above.

The easiest way to solve this problem is to use the latin1 (ex. latin1_general_ci) collation instead of utf8 (ex. utf8_unicode_ci) collation. latin1 uses 1 byte per character and our unique key will thus stay in the 1000 bytes limit

[(255 characters + 255 characters) * 1 bytes/character] = 510 bytes

That’s it, hope someone will find this useful.

*BTW, I’m wondering if this unique key max length limitation only happens in MySQL or does it applies to other RDBMS such as Oracle and SQL Server. If you know, please leave a comment, thanks.