Why Am I Getting MySQL Error Code #1089 \"Incorrect Prefix Key\"?

Understanding MySQL Error Code #1089

The MySQL error code #1089, "Incorrect prefix key," indicates an issue with a primary key or unique index. It specifically occurs when the key part used is not a string or when the length of the key part used exceeds the actual key part's length.

In the provided SQL query:

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;

The issue stems from the usage of PRIMARY KEY (movie_id(3)). This line creates a primary key on the first three bytes of the movie_id column, which is specified by the (3) segment.

However, attempting to create a prefix on a numeric data type (INT in this case) is not supported by MySQL. This is because prefix keys are only applicable to string-based data types.

To resolve the error, you should use the following query:

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;

By removing the (3) segment, the primary key is created on the entire movie_id column as opposed to just its prefix. This resolves the issue and allows MySQL to create the primary key correctly.

The above is the detailed content of Why Am I Getting MySQL Error Code #1089 \"Incorrect Prefix Key\"?. For more information, please follow other related articles on the PHP Chinese website!