mysql - 错误 1215 无法添加外键约束

Question

表 cargo

DROP TABLE IF EXISTS "hibernatecurso"."cargo";
CREATE TABLE  "hibernatecurso"."cargo" (
  "idcargo" int(11) NOT NULL DEFAULT '0',
  "funcao" varchar(45) DEFAULT NULL,
  PRIMARY KEY ("idcargo")
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

表 EMPREGADO

DROP TABLE IF EXISTS "hibernatecurso"."empregado";
CREATE TABLE  "hibernatecurso"."empregado" (
  "idempregado" int(11) NOT NULL DEFAULT '0',
  "nome" varchar(45) NOT NULL DEFAULT '',
  "cargo" varchar(45) NOT NULL DEFAULT '',
  PRIMARY KEY ("idempregado"),
  KEY "idx_cargo" ("cargo")
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

---

在empregado中创建索引

ALTER TABLE `hibernatecurso`.`empregado` ADD INDEX `idx_cargo`(`cargo`);

---

在empregado中创建FK

ALTER TABLE `hibernatecurso`.`empregado` DROP INDEX `idx_cargo`,
 ADD INDEX `idx_cargo`(`cargo`),
 ADD CONSTRAINT `FK_empregado_cargo` FOREIGN KEY `FK_empregado_cargo` (`cargo`)
    REFERENCES `cargo` (`funcao`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

在这部分....

执行查询时出错。

ALTER TABLE `hibernatecurso`.`empregado` DROP INDEX `idx_cargo`,
 ADD INDEX `idx_cargo`(`cargo`),
 ADD CONSTRAINT `FK_empregado_cargo` FOREIGN KEY `FK_empregado_cargo` (`cargo`)
    REFERENCES `cargo` (`funcao`)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

MySQL Error Number 1215 Cannot add foreign key constraint

是什么导致了错误？

Answer 1

我可能很难阅读，但我没有在 cargo.funcao 上看到任何索引。这很可能丢失了:

ALTER TABLE `hibernatecurso`.`cargo` ADD INDEX `idx_funcao`(`funcao`);

---

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html