Problems with foreign keys in MySQL

Question

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 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 question, I failed to get rid of the error. You can see in the above image marked with yellow the old tables that are generated (in lower case).

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.

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

Thank you for your help!

Here is the modified part of the database, generated by MySQL Workbench:

    -- 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';


-- -----------------------------------------------------
-- 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_have_Persons_Persons1_idx` (`Persons_ID` ASC),
  INDEX `fk_Addresses_have_Persons_Addresses1_idx` (`Addresses_ID` ASC),
  CONSTRAINT `fk_Addresses_have_Persons_Addresses1`
    FOREIGN KEY (`Addresses_ID`)
    REFERENCES `test_apptest`.`Addresses` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Addresses_have_Persons_Persons1`
    FOREIGN KEY (`Persons_ID`)
    REFERENCES `test_apptest`.`Persons` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
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_have_Persons_Persons1_idx` (`Persons_ID` ASC),
  INDEX `fk_Groups_have_Persons_Groups1_idx` (`Groups_ID` ASC),
  CONSTRAINT `fk_Groups_have_Persons_Groups1`
    FOREIGN KEY (`Groups_ID`)
    REFERENCES `test_apptest`.`Groups` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Groups_have_Persons_Persons1`
    FOREIGN KEY (`Persons_ID`)
    REFERENCES `test_apptest`.`Persons` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `test_apptest`.`Groups_have_Roles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Groups_have_Roles` (
  `Groups_ID` INT NOT NULL,
  `Roles_ID` INT NOT NULL,
  PRIMARY KEY (`Groups_ID`, `Roles_ID`),
  INDEX `fk_Groups_have_Roles_Roles1_idx` (`Roles_ID` ASC),
  INDEX `fk_Groups_have_Roles_Groups1_idx` (`Groups_ID` ASC),
  CONSTRAINT `fk_Groups_have_Roles_Groups1`
    FOREIGN KEY (`Groups_ID`)
    REFERENCES `test_apptest`.`Groups` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Groups_have_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_have_Persons`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `test_apptest`.`Roles_have_Persons` (
  `Roles_ID` INT NOT NULL,
  `Persons_ID` INT NOT NULL,
  PRIMARY KEY (`Roles_ID`, `Persons_ID`),
  INDEX `fk_Roles_have_Persons_Persons1_idx` (`Persons_ID` ASC),
  INDEX `fk_Roles_have_Persons_Roles1_idx` (`Roles_ID` ASC),
  CONSTRAINT `fk_Roles_have_Persons_Roles1`
    FOREIGN KEY (`Roles_ID`)
    REFERENCES `test_apptest`.`Roles` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Roles_have_Persons_Persons1`
    FOREIGN KEY (`Persons_ID`)
    REFERENCES `test_apptest`.`Persons` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
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_have_Rights_Rights1_idx` (`Rights_ID` ASC),
  INDEX `fk_Roles_have_Rights_Roles1_idx` (`Roles_ID` ASC),
  CONSTRAINT `fk_Roles_have_Rights_Roles1`
    FOREIGN KEY (`Roles_ID`)
    REFERENCES `test_apptest`.`Roles` (`ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Roles_have_Rights_Rights1`
    FOREIGN KEY (`Rights_ID`)
    REFERENCES `test_apptest`.`Rights` (`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;

And here is the originally unmodified generated code that also resulted in errors:

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

And here is the design of the new part from MySQL Workbench created the SQL script:

Unfortunately, MySQL hides the actual error message when it comes to foreign key definition errors. You need to run `SHOW ENGINE INNODB STATUS` and find it in the *LATEST FOREIGN KEY ERROR* section. Please note that [show engine queries](https://dev.mysql.com/doc/refman/5.7/en/show-engine.html) require the [`PROCESS` privilege](https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_process), which your regular MySQL user may not have—you can either assign the privilege or run the query as root. — Álvaro González, Jul 06 '17 at 12:54
Thank you for your reply! I already tried that but was unsuccessful because of missing rights. Unfortunately, our admin comes tomorrow. — Sae1962, Jul 06 '17 at 12:59

papkass · Answer 1 · 2017-07-07T07:29:22.660

0

I don't think you need to add primary keys on a mapping table. Try creating the table without the primary keys and the indexes (when using innodb, adding a foreign key will create an index for you).

Step 1:

CREATE TABLE IF NOT EXISTS `test_apptest`.`Addresses_have_Persons` (
  `Addresses_ID` INT NOT NULL,
  `Persons_ID` INT NOT NULL)
ENGINE = InnoDB;

Step 2:

ALTER TABLE Addresses_have_Persons 
    ADD CONSTRAINT fk_Addresses_have_Persons_Addresses1
    FOREIGN KEY(Addresses_ID)
    REFERENCES Addresses(ID);

Step 3:

ALTER TABLE Addresses_have_Persons 
    ADD CONSTRAINT fk_Addresses_have_Persons_Persons1
    FOREIGN KEY(Persons_ID)
    REFERENCES Persons(ID);

edited Jul 07 '17 at 07:29

answered Jul 06 '17 at 13:52

papkass

1,261
2
14
20

Thank you for your reply! Unfortunately, here, I got a "#1215 - Cannot add foreign key constraint" error with your suggestion. – Sae1962 Jul 07 '17 at 07:09
I've updated my answer. Try doing it in 3 steps. Create the table without any constraints. Then try to add the constraints 1 by 1. This should give you an idea of where the error occurs. – papkass Jul 07 '17 at 07:30
What I have to add is that when I go to Home in MySQL Workbench 6.3 & click under the "MySQL Connections" on the "test_apptest" symbol of the created connection, following warning appears: 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. – Sae1962 Jul 07 '17 at 07:47
The above message may be the reason for incompatibilities between the MySQL server & the SQL scripts generated by the MySQL Workbench. – Sae1962 Jul 07 '17 at 07:50
The MySQL version we are using is 5.6.36-1~dotdeb+7.1 that I found out with the command 'SELECT @@version'. – Sae1962 Jul 07 '17 at 07:59
I tried now another modified script, where I first create the tables w/o PK/FKs. Then, I add the relations to it w/o index creation. This time, I get the error following error: – Sae1962 Jul 07 '17 at 08:24
Fehler SQL-Befehl: […]ALTER TABLE `test_apptest`.`Addresses` ( ADD CONSTRAINT `fk_Addresses_Countries1` FOREIGN KEY ( `Countries_ID` ) REFERENCES `test_apptest`.`Countries` ( `ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION , ADD 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; – Sae1962 Jul 07 '17 at 08:25
MySQL meldet: Dokumentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ADD CONSTRAINT `fk_Addresses_Countries1` FOREIGN KEY (`Countries_ID`) ' at line 10 – Sae1962 Jul 07 '17 at 08:25
Here is the new script (test_apptest with roles new parts create first w_o PK & IDX.sql): – Sae1962 Jul 07 '17 at 08:53
-- 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 -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `test_apptest` DEFAULT CHARACTER SET latin1 ; USE `test_apptest` ; – Sae1962 Jul 07 '17 at 08:54
-- ----------------------------------------------------- -- ----------------------------------------------------- -- Create tables without relations -- ----------------------------------------------------- -- ----------------------------------------------------- -- ----------------------------------------------------- -- Table `test_apptest`.`Addresses` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `test_apptest`.`Addresses` ( `ID` INT NOT NULL, `ZIP` VARCHAR(12) NULL, `countryID` INT NOT NULL, `stateID` INT NULL, – Sae1962 Jul 07 '17 at 08:55
`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) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `test_apptest`.`Countries` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `test_apptest`.`Countries` ( `ID` INT NOT NULL, `name` VARCHAR(45) NULL) ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 08:55
-- ----------------------------------------------------- -- Table `test_apptest`.`Filters` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `test_apptest`.`Filters` ( `ID` INT NOT NULL, `filter` BLOB NULL) 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) – Sae1962 Jul 07 '17 at 08:56
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, – Sae1962 Jul 07 '17 at 08:56
`status` INT NULL, `publicMail` VARCHAR(255) NULL, `advisorID` INT NULL) 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) ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 08:56
-- ----------------------------------------------------- -- 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) 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) – Sae1962 Jul 07 '17 at 08:57
ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 08:57
I tried the following in the SQL window: ALTER TABLE `test_apptest`.`Addresses` ( ADD CONSTRAINT `fk_Addresses_Countries1` FOREIGN KEY (`Countries_ID`) REFERENCES `test_apptest`.`Countries` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; The error reads: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ADD CONSTRAINT `fk_Addresses_Countries1` FOREIGN KEY (`Countries_ID`) ' at line 1 – Sae1962 Jul 07 '17 at 09:00
Now, I tried ALTER TABLE `test_apptest`.`Addresses` ( ADD CONSTRAINT FOREIGN KEY (`Countries_ID`)) ENGINE = InnoDB; that results in the error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( ADD CONSTRAINT FOREIGN KEY (`Countries_ID`)) ENGINE = InnoDB' at line 1 – Sae1962 Jul 07 '17 at 09:02
I am creating now my own SQL script & hope that it will help me modify the database properly. I proceed command by command & add it to a file. What worked till now is below: – Sae1962 Jul 07 '17 at 12:29
ALTER TABLE `test_apptest`.`Addresses` ADD CONSTRAINT PRIMARY KEY (ID); ALTER TABLE `test_apptest`.`Addresses` ADD COLUMN `Countries_ID` INT; ALTER TABLE `test_apptest`.`Addresses` ADD CONSTRAINT `fk_Addresses_Countries1` FOREIGN KEY (`Countries_ID`) REFERENCES `test_apptest`.`Countries` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE `test_apptest`.`Addresses` ADD COLUMN `States_ID` INT; ALTER TABLE `test_apptest`.`Addresses` ADD COLUMN `States_Countries_ID` INT; – Sae1962 Jul 07 '17 at 12:30
ALTER TABLE `test_apptest`.`Addresses` ADD CONSTRAINT `fk_Addresses_Countries1` FOREIGN KEY (`Countries_ID`) REFERENCES `test_apptest`.`Countries` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION; ALTER TABLE `test_apptest`.`Addresses` ADD COLUMN `States_ID` INT; ALTER TABLE `test_apptest`.`Addresses` ADD COLUMN `States_Countries_ID` INT; – Sae1962 Jul 07 '17 at 12:31
ALTER TABLE `test_apptest`.`Addresses` ADD 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; ALTER TABLE `test_apptest`.`Addresses_have_Persons` ADD COLUMN `States_ID` INT; – Sae1962 Jul 07 '17 at 12:31
ALTER TABLE `test_apptest`.`Addresses_have_Persons` ADD CONSTRAINT `fk_Addresses_have_Persons_Addresses1` FOREIGN KEY (`Addresses_ID`) REFERENCES `test_apptest`.`Addresses` (`ID`); ALTER TABLE `test_apptest`.`Addresses_have_Persons` ADD CONSTRAINT `fk_Addresses_have_Persons_Persons1` FOREIGN KEY (`Persons_ID`) REFERENCES `test_apptest`.`Persons` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION; – Sae1962 Jul 07 '17 at 12:31
Then, I found out to add to this SQL dialect the 'InnoDB' part correctly & continued as follows: – Sae1962 Jul 07 '17 at 12:32
ALTER TABLE `test_apptest`.`Groups` ADD CONSTRAINT `fk_Groups_Filters1` FOREIGN KEY (`Filters_ID`) REFERENCES `test_apptest`.`Filters` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 12:32
ALTER TABLE `test_apptest`.`Groups_have_Persons` ADD CONSTRAINT `fk_Groups_have_Persons_Groups1` FOREIGN KEY (`Groups_ID`) REFERENCES `test_apptest`.`Groups` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_Groups_have_Persons_Persons1` FOREIGN KEY (`Persons_ID`) REFERENCES `test_apptest`.`Persons` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 12:32
CREATE TABLE IF NOT EXISTS `test_apptest`.`Groups_have_Roles` ( `Groups_ID` INT NOT NULL, `Roles_ID` INT NOT NULL) ENGINE = InnoDB; ALTER TABLE `test_apptest`.`Groups_have_Roles` ADD CONSTRAINT `fk_Groups_have_Roles_Groups1` FOREIGN KEY (`Groups_ID`) REFERENCES `test_apptest`.`Groups` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ADD CONSTRAINT `fk_Groups_have_Roles_Roles1` FOREIGN KEY (`Roles_ID`) REFERENCES `test_apptest`.`Roles` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 12:33
CREATE TABLE IF NOT EXISTS `test_apptest`.`Roles_have_Rights` ( `Roles_ID` INT NOT NULL, `Rights_ID` INT NOT NULL) ENGINE = InnoDB; ALTER TABLE `test_apptest`.`Roles_have_Rights` ADD CONSTRAINT `fk_Roles_have_Rights_Roles1` FOREIGN KEY (`Roles_ID`) REFERENCES `test_apptest`.`Roles` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `test_apptest`.`Roles` ( `ID` INT NOT NULL, `name` VARCHAR(64) NOT NULL, `description` VARCHAR(1024) NULL) ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 12:33
ALTER TABLE `test_apptest`.`Roles_have_Rights` ADD CONSTRAINT `fk_Roles_have_Rights_Rights1` FOREIGN KEY (`Rights_ID`) REFERENCES `test_apptest`.`Rights` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 12:33
Fehler SQL-Befehl: ALTER TABLE `test_apptest`.`Roles_have_Rights` ADD CONSTRAINT `fk_Roles_have_Rights_Rights1` FOREIGN KEY (`Rights_ID`) REFERENCES `test_apptest`.`Rights` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB MySQL meldet: Dokumentation #1005 - Kann Tabelle `test_apptest`.`#sql-eb0_fd` nicht erzeugen (Fehler: 150 "Foreign key constraint is incorrectly formed") (Details…) – Sae1962 Jul 07 '17 at 12:34
The error message reads in English: #1005 - Cannot create table test_apptest.#sql-eb0_fd (Error: 150 "Foreign key constraint is incorrectly formed") – Sae1962 Jul 07 '17 at 12:34
The error details are:------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2017-07-07 14:55:23 ffc Error in foreign key constraint of table `test_apptest`.`roles_have_rights`: FOREIGN KEY (`Rights_ID`) REFERENCES `test_apptest`.`Rights` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB: – Sae1962 Jul 07 '17 at 13:02
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. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html for correct foreign key definition. – Sae1962 Jul 07 '17 at 13:03
Alter table '`test_apptest`.`roles_have_rights`' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns near ' FOREIGN KEY (`Rights_ID`) REFERENCES `test_apptest`.`Rights` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB'. – Sae1962 Jul 07 '17 at 13:03
The problem with the last command was a missing primary key: ALTER TABLE `test_apptest`.`Rights` ADD CONSTRAINT PRIMARY KEY (ID); ALTER TABLE `test_apptest`.`Roles_have_Rights` ADD CONSTRAINT `fk_Roles_have_Rights_Rights1` FOREIGN KEY (`Rights_ID`) REFERENCES `test_apptest`.`Rights` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 13:45
Last two corrections are: ALTER TABLE `test_apptest`.`Rights` ADD CONSTRAINT PRIMARY KEY (ID); ALTER TABLE `test_apptest`.`Roles_have_Rights` ADD CONSTRAINT `fk_Roles_have_Rights_Rights1` FOREIGN KEY (`Rights_ID`) REFERENCES `test_apptest`.`Rights` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 13:50
ALTER TABLE `test_apptest`.`Countries` ADD CONSTRAINT PRIMARY KEY (ID); ALTER TABLE `test_apptest`.`States` ADD CONSTRAINT `fk_States_Countries1` FOREIGN KEY (`Countries_ID`) REFERENCES `test_apptest`.`Countries` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION, ENGINE = InnoDB; – Sae1962 Jul 07 '17 at 13:50

Problems with foreign keys in MySQL

1 Answers1