tinOfBeans tinOfBeans - 5 months ago 11
SQL Question

I keep getting this mysql error code #1089

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;


I keep getting this error:


#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.


but I've got no idea what it means, anyone have a clue?

Answer

With the part

PRIMARY KEY (`movie_id`(3))

you are telling mysql to create a sub part key* on the first 3 letters of movie id. This only works for string types.

You need to use

PRIMARY KEY (`movie_id`)

without providing a length.

*Is this sure the query resulting in the error? Never saw that on a primary key, its used for indexes.