MySQL은 외래 키 제약 조건에서 필요한 인덱스를 삭제할 수 없습니다

MySQL은 외래 키 제약 조건에서 필요한 인덱스를 삭제할 수 없습니다

---

기존 데이터베이스를 변경하여 열을 추가해야합니다. 결과적으로 새 열을 포함하도록 UNIQUE 필드를 업데이트하고 싶습니다. 현재 색인을 제거하려고하지만 오류가 계속 발생합니다.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

---

외래 키를 삭제해야합니다. MySQL의 외래 키는 테이블에 인덱스를 자동으로 생성합니다 ( 주제에 대한 SO 질문 이 있음).

ALTER TABLE mytable DROP FOREIGN KEY mytable_ibfk_1 ; 

---

1 단계

외래 키 나열 (인덱스 이름과 다른 점)

SHOW CREATE TABLE  <Table Name>

결과는 외래 키 이름을 보여줍니다.

체재:

CONSTRAINT `FOREIGN_KEY_NAME` FOREIGN KEY (`FOREIGN_KEY_COLUMN`) REFERENCES `FOREIGN_KEY_TABLE` (`id`),

2 단계

드롭 (외국 / 기본 / 키) 키

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

3 단계

색인을 삭제하십시오.

---

당신이 이것을 할 수 있다는 것을 의미한다면 :

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.

하지만:

ALTER TABLE mytable
DROP KEY AID ;

오류를 제공합니다.

---

하나의 ALTER TABLE명령문으로 인덱스를 삭제하고 새 인덱스를 작성할 수 있습니다 .

ALTER TABLE mytable
DROP KEY AID ,
ADD UNIQUE KEY AID (AID, BID, CID, DID);

---

Because you have to have an index on a foreign key field you can just create a simple index on the field 'AID'

CREATE INDEX aid_index ON mytable (AID);

and only then drop the unique index 'AID'

ALTER TABLE mytable DROP INDEX AID;

---

A foreign key always requires an index. Without an index enforcing the constraint would require a full table scan on the referenced table for every inserted or updated key in the referencing table. And that would have an unacceptable performance impact. This has the following 2 consequences:

• When creating a foreign key, the database checks if an index exists. If not an index will be created. By default, it will have the same name as the constraint.
• When there is only one index that can be used for the foreign key, it can't be dropped. If you really wan't to drop it, you either have to drop the foreign key constraint or to create another index for it first.

---

I think this is easy way to drop the index.

set FOREIGN_KEY_CHECKS=1;

ALTER TABLE mytable DROP INDEX AID;

set FOREIGN_KEY_CHECKS=0;

참고URL : https://stackoverflow.com/questions/8482346/mysql-cannot-drop-index-needed-in-a-foreign-key-constraint