foreign keys - ERRO 1215. MySql InnoDB

Question

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?

I saw the answer.

Reasons you may get a foreign key constraint error:

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

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.The types of the columns are not the same (exception is the column on the referencing table can be nullable).

4. One of the reasons may also be that the column you are using for ON DELETE SET NULL is not defined to be null. So make sure that the column is set default 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`));

Answer 1

I tested your table creation.

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

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.

Like this:

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.