Home / Questions / 496239
Accepted
Bernardo Harreguy
Asked: 2022-04-11 05:18:38 +0800 CST 2022-04-11 05:18:38 +0800 CST

Mariadb 10.6 can't add an index

  • 772

I am trying to create an index, it always works for me but since version 10.6 it does not, I am using the following command CREATE OR REPLACE INDEX And ​​it tells me that the entry is duplicated as follows ERROR:

ERROR DE SQL(102):Entrada dupliada '9' para la clave "tipodeopcion"

The error occurs when I try to add an index with the following command:

create OR REPLACE UNIQUE INDEX tipodeopcion on nueva.opciones (terminalid) USING BTREE;

Being the following table:

CREATE TABLE `opciones` (
    `dato` MEDIUMBLOB NULL DEFAULT NULL,
    `datobinario` TINYINT(1) NULL DEFAULT NULL,
    `diayhora` DATETIME NULL DEFAULT NULL,
    `numero` DECIMAL(11,2) NULL DEFAULT NULL,
    `opciones` INT(12) NOT NULL AUTO_INCREMENT,
    `texto` TEXT NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `tipodeopcion` VARCHAR(100) NOT NULL COLLATE 'utf8mb3_general_ci',
    `tipop` INT(1) NOT NULL DEFAULT '0',
    `terminalid` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`opciones`) USING BTREE,
    INDEX `tipodeopcion2` (`tipodeopcion`) USING BTREE,
    INDEX `terminalid` (`terminalid`) USING BTREE,
    INDEX `opciones` (`opciones`) USING BTREE
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=148
;

As you can see, what I want to add is an index that is unique for the optiontype and terminalid columns, called "optiontype", and it gives an error even with heidisql, when doing it by interface, why has the error happened to someone?

mariadb

1 Answers

  1. Best Answer

    You are trying to create an index of type UNIQUE, that is, that will not allow more than one record with the same value for the field (or fields) that make up the index.

    And it gives you an error, because the records that already exist in the table violate that restriction, that is, there are two or more records that have the same value for the field (or fields) that make up the index.

    In your specific case, you have two or more records with the value 9 for that index.

    Therefore, before adding the index, you must solve that problem, for example by cleaning repeated records according to that criteria.

    By the way, you create the index with the name 'optiontype', but you define it with the 'terminal' field. The name is the least important thing, what in your case you are not allowing to repeat is the terminalid, but since you have another option type field, you are confusing yourself with something.

    • 1