ERROR 1215 (HY000) at line 53: Cannot add foreign key constraint Operation failed with exitcode 1

Question

This is my sql script which I have generated from MySQL Workbench:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';


-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`systems`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`systems` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`, `name`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`formats`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`formats` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`, `name`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`protocols`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`protocols` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`, `name`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`systems_protocol_format`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`systems_protocol_format` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `system` VARCHAR(45) NOT NULL,
  `protocol` VARCHAR(45) NOT NULL,
  `format` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`, `system`, `protocol`, `format`),
  CONSTRAINT FOREIGN KEY (`system`)
    REFERENCES `mydb`.`systems` (`name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT FOREIGN KEY (`format`)
    REFERENCES `mydb`.`formats` (`name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT FOREIGN KEY (`protocol`)
    REFERENCES `mydb`.`protocols` (`name`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

I can't seem to find the error. The types of the referenced columns are the same as the foreign keys and the names match... Engine is also the same on all table create queries.

Could someone point me in the right direction?

Answer 1

You didn't provide any name of the foreign keys.

Please check with this schema:

-- ----------------------------
-- Table structure for `formats`
-- ----------------------------
DROP TABLE IF EXISTS `formats`;
CREATE TABLE `formats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`,`name`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `protocols`
-- ----------------------------
DROP TABLE IF EXISTS `protocols`;
CREATE TABLE `protocols` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`,`name`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `systems`
-- ----------------------------
DROP TABLE IF EXISTS `systems`;
CREATE TABLE `systems` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`,`name`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `systems_protocol_format`
-- ----------------------------
DROP TABLE IF EXISTS `systems_protocol_format`;
CREATE TABLE `systems_protocol_format` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `system` varchar(45) NOT NULL,
  `protocol` varchar(45) NOT NULL,
  `format` varchar(45) NOT NULL,
  PRIMARY KEY (`id`,`system`,`protocol`,`format`),
  KEY `FK_system` (`system`),
  KEY `FK_protocol` (`protocol`),
  KEY `FK_format` (`format`),
  CONSTRAINT `FK_format` FOREIGN KEY (`format`) REFERENCES `formats` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_protocol` FOREIGN KEY (`protocol`) REFERENCES `protocols` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_system` FOREIGN KEY (`system`) REFERENCES `systems` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;