错误 1215.MySql InnoDB

2022-01-20 00:00:00 foreign-keys 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.

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

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 statusG

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

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

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.

相关文章