What is wrong with my SQL here? #1089 - Incorrect prefix key

Question

In your PRIMARY KEY definition you've used (id(11)), which defines a prefix key - i.e. the first 11 characters only should be used to create an index. Prefix keys are only valid for CHAR, VARCHAR, BINARY and VARBINARY types and your id field is an int, hence the error.

Use PRIMARY KEY (id) instead and you should be fine.

MySQL reference here and read from paragraph 4.

If you are using a GUI and you are still getting the same problem. Just leave the size value empty, the primary key defaults the value to 11, you should be fine with this. Worked with Bitnami phpmyadmin.

This

PRIMARY KEY (id (11))

is generated automatically by phpmyadmin, change to

PRIMARY KEY (id)

.

CREATE TABLE `table`.`users` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(50) NOT NULL,
    `password` VARCHAR(50) NOT NULL,
    `dir` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`(11))
) ENGINE = MyISAM;

Change To

CREATE TABLE `table`.`users` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `username` VARCHAR(50) NOT NULL,
        `password` VARCHAR(50) NOT NULL,
        `dir` VARCHAR(100) NOT NULL,
        PRIMARY KEY (`id`)
    ) ENGINE = MyISAM;

There is a simple way of doing it. This may not be the expert answer and it may not work for everyone but it did for me.

Uncheck all primary and unique check boxes, jut create a plain simple table.

When phpmyadmin (or other) shows you the table structure, make the column primary by the given button.

Then click on change and edit the settings of that or other colums like 'unique' etc.

Here the full solution step by step

• First of all you have to make the table by inserting all the data. id should AI ticked.
• then press go and #1089 error will be pop-up

here is the solution

• theres a button near go called preview SQL
• click that button and copy the sql code
• then click on SQL tab on top of the window
• Clear the text filed and paste that copied code there.
• you will be see (id (11)) this on bottom of the code
• replace (id (11)) into (id)
• and click go

boom now you will be fine