Problem when creating MySQL tables with PhpMyadmin

Question

Problem when creating MySQL tables with PhpMyadmin

Navigation

#1 by (2 votes)
#2 by (2 votes)
#3 by (0 votes)

1

Good afternoon,

I have a problem creating tables with PhpMyadmin my sentence is this:

CREATE TABLE 'prensacarv1'.'pr_clients' (
    'clientId' INT(10) NOT NULL AUTO_INCREMENT,
    'firstName' VARCHAR(45) NOT NULL,
    'lastName' VARCHAR(45) NOT NULL,
    'netProfit' INT(3) NOT NULL,
    'address' TEXT NOT NULL,
    'locality' INT(10) NOT NULL,
    'postalCode' VARCHAR(45) NOT NULL,
    'province' INT(10) NOT NULL,
    'country' VARCHAR(255) NOT NULL,
    'coordinates' POINT NOT NULL,
    'latitude' VARCHAR(45) NOT NULL,
    'longitude' VARCHAR(45) NOT NULL,
    'email' VARCHAR(45) NOT NULL,
    'comment' TEXT NOT NULL,
    'userId' INT(10) NOT NULL,
    'created' DATETIME NOT NULL,
    'modified' DATETIME NOT NULL,
    'active' TINYINT(1) NOT NULL,
    PRIMARY KEY ('clientId' (10))
)  ENGINE=INNODB;

and pressing Save I get the following error message:

# 1089 - Incorrect prefix key; the used key part is not to string, the used length is longer than the key part, or the storage engine doesn support unique prefiex keys

Does anyone know what is happening?

mysqli phpmyadmin

asked by Korzan 04.04.2017 в 15:06

source

3 answers

2

With:

PRIMARY KEY (clientId(10))

you are indicating the length to the primary Key and you should not. You should only use:

PRIMARY KEY (clientId)

without indicating the length, that should work.

answered by 04.04.2017 в 15:12

0

The Primary Key has already defined the length above. Below you are just placing the primary key property, you should not define the length of this again.

'clientId' INT(10) NOT NULL AUTO_INCREMENT

There you defined the long 10 as INT type

PRIMARY KEY ('clientId' (10))

and there you should only define which of all the created columns you will leave as the primary key, not redefine.

Greetings and success.

answered by 04.04.2017 в 15:16

Users with MasterPage disappeared record of a mysql table

score 2 · Accepted Answer

It struck me that error 1089 thrown presents three possible reasons:

1089 - Incorrect prefix key;

(a) the used key is not a string,

(b) the used length is longer than the key part,

(c) or the storage engine does not support unique prefiex keys

In your case the error is due to the first reason: the used key is not a string , and it happens because you are treating a column of type INT as if it were of type VARCHAR . The error would be corrected, as has been said in other answers, by removing the size of the column indicated in the declaration of the PRIMARY KEY in this line:

Change:

PRIMARY KEY (clientId (10))

By:

PRIMARY KEY ('clientId')

Perooo.

It does not mean that indicating a length to PRIMARY KEY is always incorrect .

In your documentation MySQL indicates that the key prefixes are valid in columns of the following types: CHAR, VARCHAR, BINARY, VARBINARY, BLOB y TEXT .

Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY   column indexes.

Prefixes must be specified for BLOB and TEXT column indexes.

In fact, in MYSQL, if the column of your table that will make the functions of PRIMARY KEY is of type VARCHAR you can perfectly indicate that you want only a prefix of that column as PRIMARY KEY , that is, the n characters indicated in parentheses from the left.

So a sentence like this is totally correct:

CREATE TABLE 'prensacarv1'.'pr_clients' (
    'clientId' INT(10) NOT NULL,
    'firstName' VARCHAR(45) NOT NULL,
    'lastName' VARCHAR(45) NOT NULL,
    -- otras columnas ...,
    PRIMARY KEY ('firstName' (10))
)  ENGINE=INNODB;

in it we indicate that the PRIMARY KEY will be the first 10 characters of the name of the person.

On the other hand, if you do this:

CREATE TABLE 'prensacarv1'.'pr_clients' (
    'clientId' INT(10) NOT NULL,
    'firstName' VARCHAR(45) NOT NULL,
    'lastName' VARCHAR(45) NOT NULL,
    -- otras columnas ...,
    PRIMARY KEY ('firstName' (999))
)  ENGINE=INNODB;

You would be committing part (b) of the error message: the used length is longer than the key part , since you are telling it to take 999 characters when the length of the column is only 45.

For more information about the interesting error 1089 , see the next article .