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.