Re: Problems with foreign keys in MySQL
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 …)
Subject
Views
Written By
Posted
4081
July 07, 2017 03:46AM
Re: Problems with foreign keys in MySQL
759
July 07, 2017 07:05AM
486
July 07, 2017 07:46AM
470
July 07, 2017 07:52AM
478
July 11, 2017 05:39AM
Sorry, you can't reply to this topic. It has been closed.
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.