mysql - 无法在我的数据库中添加外键约束

Question

我不知道为什么在创建数据库时出现此错误:

Executing SQL script in server
ERROR: Error 1215: Cannot add foreign key constraint


CREATE TABLE IF NOT EXISTS `CollegeDB`.`Eleve` (
 `idPersonne` VARCHAR(15) NOT NULL,
 `dateNaissance` DATE NULL,
 `lieuNaissance` VARCHAR(45) NULL,
PRIMARY KEY (`idPersonne`),
INDEX `fk_Eleve_Personne1_idx` (`idPersonne` ASC),
CONSTRAINT `fk_Eleve_Personne1`
FOREIGN KEY (`idPersonne`)
REFERENCES `CollegeDB`.`Personne` (`idPersonne`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL script execution finished: statements: 10 succeeded, 1 failed

Fetching back view definitions in final form.
Nothing to fetch

我有表 Personne 和从它继承的另外两个表:Eleve 和 Enseignant我的 EER 图工作台模型中总共有 10 个表，知道吗？非常感谢

表人员:

CREATE TABLE IF NOT EXISTS `CollegeDB`.`Personne` (
 `idPersonne` VARCHAR(15) NOT NULL,
 `nom` VARCHAR(45) NULL,
 `prenom` VARCHAR(45) NULL,
 `sexe` VARCHAR(45) NULL,
 `statut` VARCHAR(45) NULL,
 `rue` VARCHAR(45) NULL,
 `image` VARCHAR(45) NULL,
 `cp` INT NULL,
PRIMARY KEY (`idPersonne`),
INDEX `cp_idx` (`cp` ASC),
CONSTRAINT `cp`
FOREIGN KEY (`cp`)
REFERENCES `CollegeDB`.`Commune` (`cp`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = big5
PACK_KEYS = Default;

Answer 1

您需要在 Personne.idPersonne 上创建索引。

来自http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html :

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan.