Ошибка 1089 phpmyadmin - Не ошибается лишь тот, кто ничего не делает!

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.

Dharman♦

30.4k22 gold badges84 silver badges132 bronze badges

asked Mar 8, 2015 at 21:47

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.

answered Mar 8, 2015 at 21:58

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.

answered Sep 18, 2015 at 23:20

This

PRIMARY KEY (id (11))

is generated automatically by phpmyadmin, change to

PRIMARY KEY (id)

answered Apr 17, 2016 at 2:19

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.

answered Aug 5, 2015 at 12:40

Robot BoyRobot Boy

1,8361 gold badge17 silver badges17 bronze badges

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;

answered Mar 3, 2017 at 16:16

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

answered Oct 22, 2019 at 16:06

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.

answered Oct 23, 2018 at 17:05

sh6210sh6210

4,0761 gold badge36 silver badges27 bronze badges

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;

answered Sep 16, 2015 at 13:29

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 😊

answered Jun 30, 2017 at 3:37

hemant raohemant rao

2,0572 gold badges12 silver badges13 bronze badges

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;

Francisco

10.8k6 gold badges34 silver badges45 bronze badges

answered Apr 7, 2017 at 7:28

ArindamArindam

6678 silver badges15 bronze badges

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.

answered Aug 23, 2016 at 12:06

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

answered Oct 27, 2020 at 16:34

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;

Adowrath

70211 silver badges24 bronze badges

answered Sep 17, 2015 at 10:32

Omar FarukOmar Faruk

3912 silver badges8 bronze badges

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.

Nick

4,67918 gold badges31 silver badges47 bronze badges

answered May 5, 2021 at 16:51

Drop the table.user and just use user
The lenght of the id was alread specified in the
id INT(11) and does not need to be specified in the PRIMART KEY.

CREATE 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;

answered Nov 6, 2022 at 7:11

Источник

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?

Источник

Fault description

When you’re using phpMyAdmin to create a database table, usually we need to set a primary key, and then allowed to self-growth, but sometimes when you set up, you may find 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

Image display as shown:

Failure Analysis:

Reference material

Preview SQL statement:

PRIMARY KEY, where we found a really more of a 4
So how do you solve the problem then?
Do we really want to deal with the command line? of course not.

solution:

When setting from growth, we will see this screen:

Tips:
Notably, this size is not required value, but not very familiar phpMyAdmin, it is easy to see the first set to a value unconscious, once set will be above mistake.
so the ultimate solution is the size of the figure here as long as we leave it blank, click Execute, and then you can save the table successfully.

Источник

First, give the solution directly:

Click a_ I, do not enter the size, directly click to execute

Analysis

When you use phpMyAdmin to create a database table, we usually need to set a primary key and let it grow by itself. But sometimes when you finish setting, you may find such an 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

The picture is shown as follows:

Fault analysis:

References

Preview SQL statement:

We found that there is indeed an additional 4 in primary key, so how to solve the problem
do you really need the command line to handle it?Of course not

Solution:

When setting self growth, we will see this interface:

Tips:
it is worth noting that this size is not a required value, but it is not very familiar with phpMyAdmin. It is easy to subconsciously set a value for the first time. Once it is set, the above error will appear
so the final solution is the size in the diagram. Here, we just need to leave the blank and click execute to save the table successfully

Solution 1

You have to remove the length of the key:

PRIMARY KEY (`BBZUID`) ,

instead of

PRIMARY KEY (`BBZUID`(9)) ,

Solution 2

SQL code

instead of primary key('id'(10));

it would be primary key('id');

Solution 3

go to preview SQL
copy SQL syntax
change this part of SQL syntax:

PRIMARY KEY (BBZUID(9)) -> PRIMARY KEY (BBZUID)

Comments

While creating a table I’m getting error #1089- Incorrect Prefix Key.

My sql code :

CREATE TABLE `buildblockz`.`db_user_info` ( `BBZUID` INT(11) UNSIGNED   NOT NULL AUTO_INCREMENT COMMENT 'User''s Id' , `username` VARCHAR(255) NOT NULL COMMENT 'User''s username' , `password` VARCHAR(255) NOT NULL COMMENT 'User''s password' , `fname` VARCHAR(255) NOT NULL COMMENT 'User''s First Name' , `mname` VARCHAR(255) NULL COMMENT 'User''s Middle Name' , `lname` VARCHAR(255) NOT NULL COMMENT 'User''s Last Name' , `line1` VARCHAR(255) NOT NULL COMMENT 'User''s Address Line 1' , `line2` VARCHAR(255) NULL COMMENT 'User''s Address Line 2' , `city` VARCHAR(255) NOT NULL COMMENT 'User''s Address City' , `state` VARCHAR(255) NOT NULL COMMENT 'User''s Address State' , `pincode` INT(11) UNSIGNED NOT NULL COMMENT 'User''s Address Pincode' , `email` VARCHAR(255) NOT NULL COMMENT 'User''s Contact Email' , `mobile` INT(11) UNSIGNED NULL COMMENT 'User''s Contact Mobile' , `phone` INT(11) UNSIGNED NULL COMMENT 'User''s Contact Phone' , `sex` ENUM('M','F','NS') NOT NULL COMMENT 'User''s Sex' , `dateOfBirth` DATE NOT NULL COMMENT 'User''s Date of Birth' , `designation` VARCHAR(255) NULL COMMENT 'User''s Designation' , `dateOfEntry` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'User''s Date of Entry' , PRIMARY KEY (`BBZUID`(9)) , UNIQUE (`username`(30)) ) ENGINE = InnoDB COMMENT = 'User''s Database';

Thanx, can you give me the reason.
Because the column BBZUID is of type int. keys with langht is only provided by columns of type varchar and char.
Thanks a lot Jens.. I was stuck for quite a while

Recents

Источник

Fault description

Failure Analysis:

solution:

First, give the solution directly:

Analysis

Fault analysis:

Solution:

Similar Posts:

Solution 1

Solution 2

Solution 3

Comments

Recents