Problems with foreign keys in MySQL

[Ahmet SABAN]

Hello!

I am working on a project where I made an extension to the existing database schema design of our project. I decided to let MySQL Workbench create the ER diagram, where I added new tables with relations. The already existing part consisted of a number of tables that have no relations at all. After going through the design with colleagues, I exported the ER data in form of an SQL script by checking the YouTube video and got an SQL file. There was a problem saving the file, so I copied it into the cache and put it into a file. You can see the newly created part attached below.

When running the code, the old tables (the part not included in attachment) runs without problems, but the new tables with foreign keys cannot be created because of the MySQL Error 1215: Cannot add foreign key constraint issue. First, I tried the generated commands of the script one by one using copy/paste to the SQL window of the MySQL database, where I executed them separately, and I found out what I have already written above that the new tables with their relations cause the problem. Although I tried to fix the errors by following the answers on this Stackoverflow question, I failed to get rid of the error.

What I did in the script already is to make all engines InnoDB by replacing the other names that existed, but no avail. The key fields are all of the type INT, and there no ON DELETE SET NULL command. I also changed the CHARACTER SET from latin1 to a coherent utf8 in the SQL script, but now I do not know what to do else to make the generated script run properly on the MySQL database. I also tried to find out details of the error by using SHOW ENGINE INNODB STATUS and looking to the LATEST FOREIGN KEY ERROR section, but I do not have the PROCESS right & have to wait until our admin comes tomorrow.

Have you had a similar problem? What do you suppose me to do next?

Thank you for your help!

Here is the generated script:
-- MySQL Workbench Forward Engineering

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

-- -----------------------------------------------------
-- Schema test_apptest
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `test_apptest` DEFAULT CHARACTER SET latin1 ;
USE `test_apptest` ;

-- […]

-- -----------------------------------------------------
-- Table `test_apptest`.`Countries`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Countries` (
`ID` INT NOT NULL,
`name` VARCHAR(45) NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`States`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`States` (
`ID` INT NOT NULL,
`name` VARCHAR(45) NULL,
`Countries_ID` INT NOT NULL,
PRIMARY KEY (`ID`, `Countries_ID`),
INDEX `fk_States_Countries1_idx` (`Countries_ID` ASC),
CONSTRAINT `fk_States_Countries1`
FOREIGN KEY (`Countries_ID`)
REFERENCES `test_apptest`.`Countries` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Addresses`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Addresses` (
`ID` INT NOT NULL AUTO_INCREMENT,
`ZIP` VARCHAR(12) NULL,
`countryID` INT NOT NULL,
`stateID` INT NULL,
`settlement` VARCHAR(64) NOT NULL,
`street` VARCHAR(50) NOT NULL,
`addition` VARCHAR(64) NULL,
`phone` VARCHAR(22) NULL,
`Countries_ID` INT NOT NULL,
`States_ID` INT NOT NULL,
`States_Countries_ID` INT NOT NULL,
PRIMARY KEY (`ID`),
INDEX `fk_Addresses_Countries1_idx` (`Countries_ID` ASC),
INDEX `fk_Addresses_States1_idx` (`States_ID` ASC, `States_Countries_ID` ASC),
CONSTRAINT `fk_Addresses_Countries1`
FOREIGN KEY (`Countries_ID`)
REFERENCES `test_apptest`.`Countries` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Addresses_States1`
FOREIGN KEY (`States_ID` , `States_Countries_ID`)
REFERENCES `test_apptest`.`States` (`ID` , `Countries_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Filters`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Filters` (
`ID` INT NOT NULL,
`filter` BLOB NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Groups`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Groups` (
`ID` INT NOT NULL,
`name` VARCHAR(64) NOT NULL,
`description` VARCHAR(1024) NULL,
`Filters_ID` INT NOT NULL,
PRIMARY KEY (`ID`, `Filters_ID`),
INDEX `fk_Groups_Filters1_idx` (`Filters_ID` ASC),
CONSTRAINT `fk_Groups_Filters1`
FOREIGN KEY (`Filters_ID`)
REFERENCES `test_apptest`.`Filters` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Persons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Persons` (
`ID` INT NOT NULL,
`username` VARCHAR(255) NOT NULL,
`password` VARCHAR(255) NOT NULL,
`firstName` VARCHAR(255) NOT NULL,
`lastName` VARCHAR(255) NOT NULL,
`eMail` VARCHAR(50) NOT NULL,
`standardAddressID` INT NOT NULL,
`address1ID` INT NULL,
`address2ID` INT NULL,
`phone1` VARCHAR(50) NOT NULL,
`phone2` VARCHAR(50) NULL,
`status` INT NULL,
`publicMail` VARCHAR(255) NULL,
`advisorID` INT NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Roles` (
`ID` INT NOT NULL,
`name` VARCHAR(64) NOT NULL,
`description` VARCHAR(1024) NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Groups_have_Persons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Groups_have_Persons` (
`Groups_ID` INT NOT NULL,
`Persons_ID` INT NOT NULL,
PRIMARY KEY (`Groups_ID`, `Persons_ID`),
INDEX `fk_Groups_has_Persons_Persons1_idx` (`Persons_ID` ASC),
INDEX `fk_Groups_has_Persons_Groups1_idx` (`Groups_ID` ASC),
CONSTRAINT `fk_Groups_has_Persons_Groups1`
FOREIGN KEY (`Groups_ID`)
REFERENCES `test_apptest`.`Groups` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Groups_has_Persons_Persons1`
FOREIGN KEY (`Persons_ID`)
REFERENCES `test_apptest`.`Persons` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Addresses_have_Persons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Addresses_have_Persons` (
`Addresses_ID` INT NOT NULL,
`Persons_ID` INT NOT NULL,
PRIMARY KEY (`Addresses_ID`, `Persons_ID`),
INDEX `fk_Addresses_has_Persons_Persons1_idx` (`Persons_ID` ASC),
INDEX `fk_Addresses_has_Persons_Addresses1_idx` (`Addresses_ID` ASC),
CONSTRAINT `fk_Addresses_has_Persons_Addresses1`
FOREIGN KEY (`Addresses_ID`)
REFERENCES `test_apptest`.`Addresses` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Addresses_has_Persons_Persons1`
FOREIGN KEY (`Persons_ID`)
REFERENCES `test_apptest`.`Persons` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Rights`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Rights` (
`ID` INT NOT NULL,
`name` VARCHAR(64) NOT NULL,
`description` VARCHAR(1024) NULL,
PRIMARY KEY (`ID`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Roles_have_Rights`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Roles_have_Rights` (
`Roles_ID` INT NOT NULL,
`Rights_ID` INT NOT NULL,
PRIMARY KEY (`Roles_ID`, `Rights_ID`),
INDEX `fk_Roles_has_Rights_Rights1_idx` (`Rights_ID` ASC),
INDEX `fk_Roles_has_Rights_Roles1_idx` (`Roles_ID` ASC),
CONSTRAINT `fk_Roles_has_Rights_Roles1`
FOREIGN KEY (`Roles_ID`)
REFERENCES `test_apptest`.`Roles` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Roles_has_Rights_Rights1`
FOREIGN KEY (`Rights_ID`)
REFERENCES `test_apptest`.`Rights` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Groups_has_Roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Groups_has_Roles` (
`Groups_ID` INT NOT NULL,
`Roles_ID` INT NOT NULL,
PRIMARY KEY (`Groups_ID`, `Roles_ID`),
INDEX `fk_Groups_has_Roles_Roles1_idx` (`Roles_ID` ASC),
INDEX `fk_Groups_has_Roles_Groups1_idx` (`Groups_ID` ASC),
CONSTRAINT `fk_Groups_has_Roles_Groups1`
FOREIGN KEY (`Groups_ID`)
REFERENCES `test_apptest`.`Groups` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Groups_has_Roles_Roles1`
FOREIGN KEY (`Roles_ID`)
REFERENCES `test_apptest`.`Roles` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Roles_has_Persons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Roles_has_Persons` (
`Roles_ID` INT NOT NULL,
`Persons_ID` INT NOT NULL,
PRIMARY KEY (`Roles_ID`, `Persons_ID`),
INDEX `fk_Roles_has_Persons_Persons1_idx` (`Persons_ID` ASC),
INDEX `fk_Roles_has_Persons_Roles1_idx` (`Roles_ID` ASC),
CONSTRAINT `fk_Roles_has_Persons_Roles1`
FOREIGN KEY (`Roles_ID`)
REFERENCES `test_apptest`.`Roles` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Roles_has_Persons_Persons1`
FOREIGN KEY (`Persons_ID`)
REFERENCES `test_apptest`.`Persons` (`ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;

-- […]


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

This results in MySQL 5.6.36-1~dotdeb+7.1 (German) in the following error message:
Fehler
SQL-Befehl:

-- -----------------------------------------------------
-- Table `test_apptest`.`Roles_has_Persons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Roles_has_Persons` (

`Roles_ID` INT NOT NULL ,
`Persons_ID` INT NOT NULL ,
PRIMARY KEY ( `Roles_ID` , `Persons_ID` ) ,
INDEX `fk_Roles_has_Persons_Persons1_idx` ( `Persons_ID` ASC ) ,
INDEX `fk_Roles_has_Persons_Roles1_idx` ( `Roles_ID` ASC ) ,
CONSTRAINT `fk_Roles_has_Persons_Roles1` FOREIGN KEY ( `Roles_ID` ) REFERENCES `test_apptest`.`Roles` (
`ID`
) ON DELETE NO ACTION ON UPDATE NO ACTION ,
CONSTRAINT `fk_Roles_has_Persons_Persons1` FOREIGN KEY ( `Persons_ID` ) REFERENCES `test_apptest`.`Persons` (
`ID`
) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = INNODB;

MySQL meldet: Dokumentation

#1215 - Cannot add foreign key constraint

Notice: When I click in "MySQL Connections" at Home of MySQL Workbench 6.3 on my connection called "test_apptest", I get the following warning:
MySQL Workbench
/!\ Connection Warning (test_apptest)
Incompatible/nonstandard server version of connection protocol detected (10.1.22).
A connection to this DB can be established but some MySQL Workbench features may not work properly since the DB is not fully compatible with the supported version of MySQL.
MySQL Workbench is developed & tested for MySQL Server versions 5.1, 5.5, 5.6 & 5.7.
[Continue Anyway] [Cancel]