ERROR 1071 (42000): Specified Key was Too Long; Max Key Length is 767 Bytes

Symptoms

During the import to MySQL, when you create a key for a InnoDB table, the “ERROR 1071 (42000)” error arises. For example,

CREATE TABLE department 
  (
    id INT,
    name VARCHAR(1000)
   );

ALTER TABLE department
  ADD PRIMARY KEY (id, name);

ERROR 1071 (42000) at line 8: Specified key was too long; max key length is 767 bytes

Cause

The maximum key length for the MySQL database for InnoDB Engine is 767 bytes. The key length includes the sum lengths of all the columns included in the key.

Solutions

There are several solutions for this issue:

• Changing the size of some columns. For example, if VARCHAR(300) is used to store last names, you can reduce its size to 100 with minimal risk of data loss.

• Specifying the partial column length for the character columns in the key. For example, for VARCHAR(1000) column, you can specify only 100 characters to be used in the key.

ALTER TABLE department
  ADD PRIMARY KEY (id, name(100));

In this example only 100 bytes of the NAME column participate in the primary key.

• Using a different set of columns for the key.

If you have any other questions, please contact us: support@ispirer.com