错误1215无法添加外键约束 [英] Error 1215 Cannot add foreign key constraint

问题描述

桌子货物

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

---

Table 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中创建索引

---

create index in empregado

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

---

在empregado中创建FK

---

Create FK in empregado

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;

在这部分......

in this part....

执行查询时出错.

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错误号1215 无法添加外键约束

MySQL Error Number 1215 Cannot add foreign key constraint

是什么原因导致错误?

推荐答案

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

I maybe have hard time reading, but I don't see any index on cargo.funcao. This is very probably missing:

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

---

InnoDB允许外键引用任何索引列或一组列. 但是，在引用的表中，必须有一个 索引，其中引用的列被列为的第一列 相同的顺序.

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

这篇关于错误1215无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！