MySQL Forums
Forum List  »  MySQL Workbench

Re: Problems with foreign keys in MySQL
Posted by: Ahmet SABAN
Date: July 07, 2017 07:05AM

To solve the problem, I try now derived SQL commands from the generated code & execute them one-by-one. Here are the commands I executed till now:
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;

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;

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;

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;

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;

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;

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;

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;

The last command results yields an error:
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…)

In English: #1005 - Cannot create table `test_apptest`.`#sql-eb0_fd` (Error: 150 …)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Problems with foreign keys in MySQL
75
July 07, 2017 07:05AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.