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

桌子货物

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

是什么导致错误？

解决方法:

我可能很难读书,但对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.

标签：mysql

来源： https://codeday.me/bug/20191012/1902239.html