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

Question

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    I m getting the  1089 - Incorrect prefix key error and can t figure out what I m doing wrong  Help  please

User · Answer

It works for me   CREATE TABLE  users        user id  INT 10  NOT NULL AUTO INCREMENT       username  VARCHAR 255  NOT NULL       password  VARCHAR 255  NOT NULL      PRIMARY KEY   user id     ENGINE   MyISAM

User · Answer

In my case  i faced the problem while creating table from phpmyadmin  For id column i choose the primary option from index dropdown and filled the size 10   If you re using phpmyadmin  to solve this problem change the index dropdown option again  after reselecting the primary option again it ll ask you the size  leave it blank and you re done

User · Answer

In PHPMyAdmin  Ignore   leave the size value empty on the pop-up window

User · Answer

Problem is the same for me in phpMyAdmin  I just created a table without any const   Later I modified the ID to a Primary key  Then I changed the ID to Auto-inc   That solved the issue    ALTER TABLE  users  CHANGE  ID   ID  INT 11  NOT NULL AUTO INCREMENT

User · Answer

When you give id as a primary key then a pop up is come and those aske you to how many size of this primary key  So you just leave blank because by default int value is set 11  Click then ok on those pop up without any enter a number  in this type of error never will you face in future  Thank you

User · Answer

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

User · Answer

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

User · Answer

I also had this same problem   Solution work for me    CREATE TABLE IF NOT EXISTS  users       sr no  int 11  NOT NULL AUTO INCREMENT     username  VARCHAR 50  NOT NULL       password  VARCHAR 50  NOT NULL       dir  VARCHAR 100  NOT NULL    PRIMARY KEY   sr no     ENGINE InnoDB  DEFAULT CHARSET latin1 AUTO INCREMENT 3     I paste this code in SQL and run  it works fine

User · Answer

This      PRIMARY KEY  id  11     is generated automatically by phpmyadmin  change to      PRIMARY KEY  id

User · Answer

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

User · Answer

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

User · Answer

according to the latest version of MySQL  phpMyAdmin   add a correct INDEX while choosing primary key  for example  id int  INDEX 0  if id is your primary key and at the first index   Or     For your problem try this one  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

User · Answer

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

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

Examples related to mysql

Examples related to sql