MySQL no puede soltar el índice necesario en una restricción de clave foránea

Necesito modificar mi base de datos existente para agregar una columna. En consecuencia, también quiero actualizar el campo ÚNICO para abarcar esa nueva columna. Estoy tratando de eliminar el índice actual, pero sigo recibiendo el error MySQL Cannot drop index needed in a foreign key constraint 

CREATE TABLE mytable_a (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;

CREATE TABLE mytable_b (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;

CREATE TABLE mytable_c (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;


CREATE TABLE `mytable` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `AID` tinyint(5) NOT NULL,
  `BID` tinyint(5) NOT NULL,
  `CID` tinyint(5) NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `AID` (`AID`,`BID`,`CID`),
  KEY `BID` (`BID`),
  KEY `CID` (`CID`),
  CONSTRAINT `mytable_ibfk_1` FOREIGN KEY (`AID`) REFERENCES `mytable_a` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `mytable_ibfk_2` FOREIGN KEY (`BID`) REFERENCES `mytable_b` (`ID`) ON DELETE CASCADE,
  CONSTRAINT `mytable_ibfk_3` FOREIGN KEY (`CID`) REFERENCES `mytable_c` (`ID`) ON DELETE CASCADE
) ENGINE=InnoDB;




mysql> ALTER TABLE mytable DROP INDEX AID;
ERROR 1553 (HY000): Cannot drop index 'AID': needed in a foreign key constraint
 101
mysql
Author: Stewart, 2011-12-13
Source

6 answers

Tienes que soltar la clave foránea. Las claves foráneas en MySQL crean automáticamente un índice en la tabla (Había una Pregunta SO sobre el tema).

ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ;
 156
Author: Brian Fisher,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-05-23 12:34:26

Paso 1

Listar la clave foránea (TENGA en cuenta que es diferente del nombre del índice) 

SHOW CREATE TABLE  <Table Name>

El resultado le mostrará el nombre de la clave foránea.

Paso 2

Drop (Foráneo/primario/clave) Clave 

ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign key name>

Paso 3

Suelta el índice.

 54
Author: Abhishek Goel,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-03-31 00:18:36

Si quieres decir que puedes hacer esto: 

CREATE TABLE mytable_d (
ID          TINYINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name        VARCHAR(255) NOT NULL,
UNIQUE(Name)
) ENGINE=InnoDB;


ALTER TABLE mytable
ADD COLUMN DID tinyint(5) NOT NULL,
ADD CONSTRAINT mytable_ibfk_4 
      FOREIGN KEY (DID) 
        REFERENCES mytable_d (ID) ON DELETE CASCADE;

 > OK.

Pero entonces: 

ALTER TABLE mytable
DROP KEY AID ;

Da error.

Puede soltar el índice y crear una nueva instrucción one in one ALTER TABLE: 

ALTER TABLE mytable
DROP KEY AID ,
ADD UNIQUE KEY AID (AID, BID, CID, DID);
 15
Author: ypercubeᵀᴹ,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-12-12 23:41:13

Debido a que debe tener un índice en un campo de clave externa, puede crear un índice simple en el campo 'AID'

CREATE INDEX aid_index ON mytable (AID);

Y solo entonces eliminar el índice único 'AID'

ALTER TABLE mytable DROP INDEX AID;
 6
Author: Eli DM,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-25 10:40:34

Creo que esta es una manera fácil de eliminar el índice.

set FOREIGN_KEY_CHECKS=1;

ALTER TABLE mytable DROP INDEX AID;

set FOREIGN_KEY_CHECKS=0;
 2
Author: Ram E Sh,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-02-06 10:02:43

Una clave foránea siempre requiere un índice. Sin un índice, la imposición de la restricción requeriría un análisis completo de la tabla en la tabla referenciada para cada clave insertada o actualizada en la tabla de referencia. Y eso tendría un impacto inaceptable en el rendimiento. Esto tiene las siguientes 2 consecuencias:

  • Al crear una clave foránea, la base de datos comprueba si existe un índice. Si no se creará un índice. De forma predeterminada, tendrá el mismo nombre que la restricción.
  • Cuando hay es solo un índice que se puede utilizar para la clave foránea, no se puede dejar caer. Si realmente no desea soltarla, primero tiene que soltar la restricción de clave externa o crear otro índice para ella.
 0
Author: Stefan Mondelaers,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-09-11 11:14:43