#1071 - Specified key was too long; max key length is 767 bytes

767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and prior versions). It's 1,000 bytes long for MyISAM tables. In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes.

You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 254.

One option you have is to just place lower limit on your VARCHAR fields.

Another option (according to the response to this issue) is to get the subset of the column rather than the entire amount, i.e.:

ALTER TABLE `mytable` ADD UNIQUE ( column1(15), column2(200) );

Tweak as you need to get the key to apply, but I wonder if it would be worth it to review your data model regarding this entity to see if there's improvements that would allow you to implement the intended business rules without hitting the MySQL limitation.

If anyone is having issues with INNODB / Utf-8 trying to put an UNIQUE index on a VARCHAR(256) field, switch it to VARCHAR(255). It seems 255 is the limitation.

When you hit the limit. Set the following.

• INNODB utf8 VARCHAR(255)
• INNODB utf8mb4 VARCHAR(191)