What is wrong with my SQL here? #1089 – Incorrect prefix key – This article will take you through the common SQL errors that you might encounter while working with mysql, sql, . The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
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!
Solution :
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)
.
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.
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;
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
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.
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;
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
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;
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 ;
In PHPMyAdmin, Ignore / leave the size value empty on the pop-up window.
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;
This worked for me:
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`(id))
) ENGINE = MyISAM;
No need to put id(11) because, by default, it is equal to 11 so you leave
it as id and in phpmyadmin you leave it empty.
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.