Error 1215 Cannot add foreign key constraint

Question

Table 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;

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;

create index in empregado

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

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....

Error while executing query.

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

What is causing the error?

Possible duplicate of http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa?rq=1 — Alma Do, Aug 06 '13 at 13:16

score 2 · Accepted Answer · answered Aug 06 '13 at 13:36

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 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

score 0 · Answer 2 · edited Mar 28 '18 at 14:58

0

I had the same kind of issue and I fixed it by making sure that collation and types matched in both FK and the parent PK.

edited Mar 28 '18 at 14:58

Stephen Kennedy

20,585
22
95
108

answered Mar 28 '18 at 14:37

Pachy

1
4

Error 1215 Cannot add foreign key constraint

2 Answers2

Linked

Related