CODEWITHSUNDEEP
mysqldatabase-designforeign-keysmysql-workbench
imperium2335
imperium2335

Reputation: 24122

MySQL Alter table strange key error

I am using MySQL Workbench to create a large database.

I keep getting:

ERROR: 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

When trying to execute the following query:

ALTER TABLE `view4`.`entities` DROP COLUMN `resellerType` ,
DROP COLUMN `industrTypeId` ,
DROP COLUMN `paymentTermsId` ,
ADD COLUMN `paymentTermsId` INT(11) NOT NULL  AFTER `vatRateId` ,
ADD COLUMN `industrTypeId` INT(11) NOT NULL  AFTER `paymentTermsId` ,
ADD COLUMN `resellerType` INT(11) NOT NULL  AFTER `industrTypeId` ,
DROP FOREIGN KEY `fk_entities_grades1`

I have tried changing the length of various INT columns but it has no effect. I am also getting this error on a few other tables.

What is workbench doing wrong?

I've reduced it a bit to:

ALTER TABLE `view4`.`entities` DROP COLUMN `resellerType` ,
DROP COLUMN `industrTypeId`  , 
ADD COLUMN `industrTypeId` INT(11) NOT NULL  AFTER `paymentTermsId` ,
ADD COLUMN `resellerType` INT(11) NOT NULL  AFTER `industrTypeId`

and it's still giving me the error.

Upvotes: 2

Views: 15641

Answers (1)

Devart
Devart

Reputation: 122002

It looks like you changed indexed field type from CHAR/VARCHAR to INT, used index was created with prefix length. The script recreates this field - DROP COLUMN + ADD COLUMN, and this causes an error.

Try to alter fields using this way - 

ALTER TABLE `view4`.`entities`
  CHANGE COLUMN `industrTypeId` `industrTypeId` INT(11) NOT NULL
    AFTER `paymentTermsId`,
  CHANGE COLUMN `resellerType` `resellerType` INT(11) NOT NULL
    AFTER `industrTypeId`;

Upvotes: 3

Related Questions