Why am I getting the \"Incorrect Prefix Key\" error (#1089) when creating a MySQL table?

MySQL Error: Incorrect Prefix Key (#1089)

Problem:

When attempting to create a table using the following query:

<code class="sql">CREATE TABLE `movies`.`movie`
( `movie_id` INT(3) NULL AUTO_INCREMENT, `movie_name` VARCHAR(25) NULL,
  `movie_embedded_id` VARCHAR(50) NULL, `rating_no` INT(3) NULL,
  `movie_description` VARCHAR(50) NULL, PRIMARY KEY (`movie_id`(3))) ENGINE = InnoDB;</code>

the following error is encountered:

#1089 - Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys.

Answer:

The error occurs because the PRIMARY KEY definition specifies a sub part key, that is, only the first 3 bytes of movie_id. This is only supported for string data types. For primary keys on numeric data types, the full length must be included, without specifying a sub part:

<code class="sql">CREATE TABLE `movies`.`movie`
( `movie_id` INT(3) NULL AUTO_INCREMENT, `movie_name` VARCHAR(25) NULL,
  `movie_embedded_id` VARCHAR(50) NULL, `rating_no` INT(3) NULL,
  `movie_description` VARCHAR(50) NULL, PRIMARY KEY (`movie_id`)) ENGINE = InnoDB;</code>

Upon making this change, the error should no longer appear, and the table will be created successfully.

The above is the detailed content of Why am I getting the \"Incorrect Prefix Key\" error (#1089) when creating a MySQL table?. For more information, please follow other related articles on the PHP Chinese website!