MySQL 删除外键索引时抛出错误。

Question

我创建了两个表并引用另一个表:

我喜欢这个:

表1:

CREATE TABLE species 
(
  id TINYINT NOT NULL AUTO_INCREMENT, 
  name VARCHAR(50) NOT NULL, 
  PRIMARY KEY(id)
) ENGINE=INNODB;

表2(上表引用)

CREATE TABLE zoo 
(
  id INT(4) NOT NULL, 
  name VARCHAR(50) NOT NULL,
  FK_species TINYINT(4) NOT NULL, 
  INDEX (FK_species), 
  FOREIGN KEY (FK_species) REFERENCES species (id), 
  PRIMARY KEY(id)
) ENGINE=INNODB;

它会自动为 zoo 表中的 FK_species 的 FOREIGN KEY 创建索引。

现在我尝试删除动物园表的索引:

ALTER TABLE zoo DROP INDEX FK_species;

它显示以下 MySQL 错误。

Error on rename of '.\test\#sql-1ec_9d' to '.\test\zoo' (errno: 150)

Answer 1

来自FOREIGN KEY Constraints @ dev.mysql.com :

InnoDB supports the use of ALTER TABLE to drop foreign keys:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

If the FOREIGN KEY clause included a CONSTRAINT name when you created the foreign key, you can refer to that name to drop the foreign key. Otherwise, the fk_symbol value is internally generated by InnoDB when the foreign key is created. To find out the symbol value when you want to drop a foreign key, use the SHOW CREATE TABLE statement.