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!
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.
Here the full solution step by step
here is the solution
(id (11))
this on bottom of the code(id (11))
into (id)
boom now you will be fine
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.
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;
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.
In PHPMyAdmin, Ignore / leave the size value empty on the pop-up window.
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;
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;
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
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.
This
PRIMARY KEY (
id
(11))
is generated automatically by phpmyadmin, change to
PRIMARY KEY (
id
)
.
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.
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;
Source: Stackoverflow.com