Description:
Can't generate a good "many to many" relation script to create the linking table.
The foreign key are not well managed.
How to repeat:
1 ) create 2 tables with a relation "many to many"
2) synchronise with database
3) the script is not a good one, the type of the FK is missing
the workaround consist in modify the script.
Suggested fix:
fix :
Modifify the script
original generated script (linking table part only):
===================================================
CREATE TABLE `test`.`PERSON_has_ADRESS` (
`FKPERSONpersonID` NOT NULL, <-------- here
`FKADRESSadressID` NOT NULL, <-------- here
CONSTRAINT `FKPERSON` FOREIGN KEY `FKPERSON` (`FKPERSONpersonID`)
REFERENCES `test`.`PERSON` (`personID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `FKADRESS` FOREIGN KEY `FKADRESS` (`FKADRESSadressID`)
REFERENCES `test`.`ADRESS` (`adressID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB;
Fixed script :
===============
CREATE TABLE `test`.`PERSON_has_ADRESS` (
`FKPERSONpersonID` INT UNSIGNED UNSIGNED NOT NULL, <-------- here
`FKADRESSadressID` INT UNSIGNED UNSIGNED NOT NULL, <-------- here
CONSTRAINT `FKPERSON` FOREIGN KEY `FKPERSON` (`FKPERSONpersonID`)
REFERENCES `test`.`PERSON` (`personID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `FKADRESS` FOREIGN KEY `FKADRESS` (`FKADRESSadressID`)
REFERENCES `test`.`ADRESS` (`adressID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
ENGINE = InnoDB;
Description: Can't generate a good "many to many" relation script to create the linking table. The foreign key are not well managed. How to repeat: 1 ) create 2 tables with a relation "many to many" 2) synchronise with database 3) the script is not a good one, the type of the FK is missing the workaround consist in modify the script. Suggested fix: fix : Modifify the script original generated script (linking table part only): =================================================== CREATE TABLE `test`.`PERSON_has_ADRESS` ( `FKPERSONpersonID` NOT NULL, <-------- here `FKADRESSadressID` NOT NULL, <-------- here CONSTRAINT `FKPERSON` FOREIGN KEY `FKPERSON` (`FKPERSONpersonID`) REFERENCES `test`.`PERSON` (`personID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FKADRESS` FOREIGN KEY `FKADRESS` (`FKADRESSadressID`) REFERENCES `test`.`ADRESS` (`adressID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = InnoDB; Fixed script : =============== CREATE TABLE `test`.`PERSON_has_ADRESS` ( `FKPERSONpersonID` INT UNSIGNED UNSIGNED NOT NULL, <-------- here `FKADRESSadressID` INT UNSIGNED UNSIGNED NOT NULL, <-------- here CONSTRAINT `FKPERSON` FOREIGN KEY `FKPERSON` (`FKPERSONpersonID`) REFERENCES `test`.`PERSON` (`personID`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FKADRESS` FOREIGN KEY `FKADRESS` (`FKADRESSadressID`) REFERENCES `test`.`ADRESS` (`adressID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE = InnoDB;