Description:
When you have tables that have any sort of cyclical foreign key relationship, Forward Engineer fails on foreign key validation. It attempts to insert in an order that will minimize this, but fails if two tables refer to each other.
E.g. Customer table has a reference to User for salesperson_id, while User has a (nullable) reference to customer in customer_id.
With the FKs in place, or enforced for inserts, the stored inserts can't complete.
How to repeat:
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';
CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 ;
USE `test` ;
-- -----------------------------------------------------
-- Table `test`.`table2`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`table2` ;
CREATE TABLE IF NOT EXISTS `test`.`table2` (
`table2_id` INT NOT NULL ,
`t2_value` VARCHAR(50) NULL ,
`table1_id` INT NULL ,
PRIMARY KEY (`table2_id`) ,
INDEX `fk_table2_table11_idx` (`table1_id` ASC) ,
CONSTRAINT `fk_table2_table11`
FOREIGN KEY (`table1_id` )
REFERENCES `test`.`table1` (`table1_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `test`.`table1`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `test`.`table1` ;
CREATE TABLE IF NOT EXISTS `test`.`table1` (
`table1_id` INT NOT NULL ,
`t1_value` VARCHAR(50) NOT NULL ,
`table2_id` INT NOT NULL ,
PRIMARY KEY (`table1_id`) ,
INDEX `fk_table1_table2_idx` (`table2_id` ASC) ,
CONSTRAINT `fk_table1_table2`
FOREIGN KEY (`table2_id` )
REFERENCES `test`.`table2` (`table2_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
USE `test` ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- -----------------------------------------------------
-- Data for table `test`.`table2`
-- -----------------------------------------------------
START TRANSACTION;
USE `test`;
INSERT INTO `test`.`table2` (`table2_id`, `t2_value`, `table1_id`) VALUES (1, '1', NULL);
INSERT INTO `test`.`table2` (`table2_id`, `t2_value`, `table1_id`) VALUES (2, '2', 1);
COMMIT;
-- -----------------------------------------------------
-- Data for table `test`.`table1`
-- -----------------------------------------------------
START TRANSACTION;
USE `test`;
INSERT INTO `test`.`table1` (`table1_id`, `t1_value`, `table2_id`) VALUES (1, '1', 2);
INSERT INTO `test`.`table1` (`table1_id`, `t1_value`, `table2_id`) VALUES (2, '2', 1);
INSERT INTO `test`.`table1` (`table1_id`, `t1_value`, `table2_id`) VALUES (3, '3', 1);
INSERT INTO `test`.`table1` (`table1_id`, `t1_value`, `table2_id`) VALUES (4, '4', 2);
COMMIT;
Suggested fix:
Should have the Forward Engineer, at least with an option, allow for creating the foreign keys in a separate step, much like there's a checkbox for generating separate "CREATE INDEX Statements".
My current workaround is to check the boxes to skip foreign keys and fk indexes, then after the forward engineer has completed, use the model sync to complete creating the database.
Description: When you have tables that have any sort of cyclical foreign key relationship, Forward Engineer fails on foreign key validation. It attempts to insert in an order that will minimize this, but fails if two tables refer to each other. E.g. Customer table has a reference to User for salesperson_id, while User has a (nullable) reference to customer in customer_id. With the FKs in place, or enforced for inserts, the stored inserts can't complete. How to repeat: 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'; CREATE SCHEMA IF NOT EXISTS `test` DEFAULT CHARACTER SET latin1 ; USE `test` ; -- ----------------------------------------------------- -- Table `test`.`table2` -- ----------------------------------------------------- DROP TABLE IF EXISTS `test`.`table2` ; CREATE TABLE IF NOT EXISTS `test`.`table2` ( `table2_id` INT NOT NULL , `t2_value` VARCHAR(50) NULL , `table1_id` INT NULL , PRIMARY KEY (`table2_id`) , INDEX `fk_table2_table11_idx` (`table1_id` ASC) , CONSTRAINT `fk_table2_table11` FOREIGN KEY (`table1_id` ) REFERENCES `test`.`table1` (`table1_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `test`.`table1` -- ----------------------------------------------------- DROP TABLE IF EXISTS `test`.`table1` ; CREATE TABLE IF NOT EXISTS `test`.`table1` ( `table1_id` INT NOT NULL , `t1_value` VARCHAR(50) NOT NULL , `table2_id` INT NOT NULL , PRIMARY KEY (`table1_id`) , INDEX `fk_table1_table2_idx` (`table2_id` ASC) , CONSTRAINT `fk_table1_table2` FOREIGN KEY (`table2_id` ) REFERENCES `test`.`table2` (`table2_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; USE `test` ; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; -- ----------------------------------------------------- -- Data for table `test`.`table2` -- ----------------------------------------------------- START TRANSACTION; USE `test`; INSERT INTO `test`.`table2` (`table2_id`, `t2_value`, `table1_id`) VALUES (1, '1', NULL); INSERT INTO `test`.`table2` (`table2_id`, `t2_value`, `table1_id`) VALUES (2, '2', 1); COMMIT; -- ----------------------------------------------------- -- Data for table `test`.`table1` -- ----------------------------------------------------- START TRANSACTION; USE `test`; INSERT INTO `test`.`table1` (`table1_id`, `t1_value`, `table2_id`) VALUES (1, '1', 2); INSERT INTO `test`.`table1` (`table1_id`, `t1_value`, `table2_id`) VALUES (2, '2', 1); INSERT INTO `test`.`table1` (`table1_id`, `t1_value`, `table2_id`) VALUES (3, '3', 1); INSERT INTO `test`.`table1` (`table1_id`, `t1_value`, `table2_id`) VALUES (4, '4', 2); COMMIT; Suggested fix: Should have the Forward Engineer, at least with an option, allow for creating the foreign keys in a separate step, much like there's a checkbox for generating separate "CREATE INDEX Statements". My current workaround is to check the boxes to skip foreign keys and fk indexes, then after the forward engineer has completed, use the model sync to complete creating the database.