ERRO1215.MySqlInnoDB [英] ERRO 1215. MySql InnoDB

问题描述

Executing:
CREATE TABLE `calls`.`called` (
  `date` DATETIME NULL,
  `rate` VARCHAR(10) NULL,
  `duration` TIME NULL,
  `Name` VARCHAR(20) NOT NULL,
  `Code` VARCHAR(10) NOT NULL,
  `Number` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`Name`, `Code`, `Number`),
  INDEX `Code_idx` (`Code` ASC),
  INDEX `Number_idx` (`Number` ASC),
  CONSTRAINT `Name`
    FOREIGN KEY (`Name`)
    REFERENCES `calls`.`city` (`Name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Code`
    FOREIGN KEY (`Code`)
    REFERENCES `calls`.`city` (`Code`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Number`
    FOREIGN KEY (`Number`)
    REFERENCES `calls`.`subscriber` (`Number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1215: Cannot add foreign key constraint
SQL Statement:
CREATE TABLE `calls`.`called` (
  `date` DATETIME NULL,
  `rate` VARCHAR(10) NULL,
  `duration` TIME NULL,
  `Name` VARCHAR(20) NOT NULL,
  `Code` VARCHAR(10) NOT NULL,
  `Number` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`Name`, `Code`, `Number`),
  INDEX `Code_idx` (`Code` ASC),
  INDEX `Number_idx` (`Number` ASC),
  CONSTRAINT `Name`
    FOREIGN KEY (`Name`)
    REFERENCES `calls`.`city` (`Name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Code`
    FOREIGN KEY (`Code`)
    REFERENCES `calls`.`city` (`Code`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Number`
    FOREIGN KEY (`Number`)
    REFERENCES `calls`.`subscriber` (`Number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

ERROR 1215: Cannot add foreign key constraint
SQL Statement:
CREATE TABLE `calls`.`called` (
  `date` DATETIME NULL,
  `rate` VARCHAR(10) NULL,
  `duration` TIME NULL,
  `Name` VARCHAR(20) NOT NULL,
  `Code` VARCHAR(10) NOT NULL,
  `Number` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`Name`, `Code`, `Number`),
  INDEX `Code_idx` (`Code` ASC),
  INDEX `Number_idx` (`Number` ASC),
  CONSTRAINT `Name`
    FOREIGN KEY (`Name`)
    REFERENCES `calls`.`city` (`Name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Code`
    FOREIGN KEY (`Code`)
    REFERENCES `calls`.`city` (`Code`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Number`
    FOREIGN KEY (`Number`)
    REFERENCES `calls`.`subscriber` (`Number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)

出现错误的原因是什么?

What is the reason here that I am getting the error?

我看到了 答案.

原因可能会导致外键约束错误:

Reasons you may get a foreign key constraint error:

1.您并非在所有表上都使用InnoDB作为引擎.

1.You are not using InnoDB as the engine on all tables.

2.您正在尝试引用目标表上不存在的键.确保它是另一个表上的键(可以是主键或唯一键).

2.You are trying to reference a nonexistent key on the target table. Make sure it is a key on the other table (it can be a primary or unique key).

3.列的类型不同(引用表上的列可以为空).

3.The types of the columns are not the same (exception is the column on the referencing table can be nullable).

1. 原因之一也可能是您用于ON DELETE SET NULL的列未定义为null.因此，请确保将该列设置为默认null.

我检查了所有这些要点，对我来说都很好.

I checked all those points and they are fine for me.

这是我在创建表之前运行的代码:

Here is the code I run before creating the table:

CREATE SCHEMA `calls` ;

  CREATE TABLE `calls`.`subscriber` (
  `first name` VARCHAR(20) NULL,
  `second name` VARCHAR(20) NULL,
  `last name` VARCHAR(20) NULL,
  `address` VARCHAR(20) NULL,
  `Number` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`Number`));

  CREATE TABLE `calls`.`city` (
  `Name` VARCHAR(20) NOT NULL,
  `Code` VARCHAR(10) NOT NULL,
  PRIMARY KEY (`Name`, `Code`));

推荐答案

我测试了您的表创建.

然后我获得了有关外键错误的更多信息:

Then I got more information about the foreign key error:

mysql> show engine innodb status\G

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-02-20 14:51:33 700002d90000 Error in foreign key constraint of table calls/called:

    FOREIGN KEY (`Code`)
    REFERENCES `calls`.`city` (`Code`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `Number`
    FOREIGN KEY (`Number`)
    REFERENCES `calls`.`subscriber` (`Number`)
    ON DELETE CASCADE
    ON UPDATE CASCADE):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
...

我看到了问题:您在(Name, Code)列的city中有一个复合主键.为此，您必须创建 one 外键约束，并引用父级主键的 both 列.

I see the problem: you have a compound primary key in city on columns (Name, Code). For this, you must create one foreign key constraint referencing both columns of the parent's primary key.

赞:

CONSTRAINT `Name`
FOREIGN KEY (`Name`, `Code`)
REFERENCES `calls`.`city` (`Name`, `Code`)
ON DELETE CASCADE
ON UPDATE CASCADE

不要为每一列声明一个约束-请声明一个引用键的两个列的约束.

Don't declare a constraint for each column — declare one constraint that references both columns of the key.

这篇关于ERRO1215.MySqlInnoDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！