Description:
Model synchronization, as well as modeling tools like the EER diagram, need to be smarter with updating foreign key constraints.
How to repeat:
Create new schema with two tables, and one foreign key:
------------------------------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;
CREATE TABLE IF NOT EXISTS `mydb`.`table2` (
`table2_id` SMALLINT NOT NULL ,
PRIMARY KEY (`table2_id`) )
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mydb`.`table1` (
`table1_id` INT NOT NULL ,
`fk_one` SMALLINT NULL ,
PRIMARY KEY (`table1_id`) ,
INDEX `fk_one` (`fk_one` ASC) ,
CONSTRAINT `fk_one`
FOREIGN KEY (`fk_one` )
REFERENCES `mydb`.`table2` (`table2_id` )
ON DELETE NO ACTION
ON UPDATE CASCADE)
ENGINE = InnoDB;
------------------------------------------------------------------------
Run the script, so you now have the schema up and running on a mysql instance, and it is identical to the model.
In the model, change table2_id from SMALLINT to INT.
BUG #1:
Right here, Workbench should either A) Prompt you whether you'd like it to change table1.fk_one to INT as well, or B) automatically do it. It should not allow the discrepancy of SMALLINT vs INT to exist in the diagram when a constraint is in place.
Moving on.
Say I manually change both table2_id and table1.fk_one to INT in my model so everything is correct. When I try to synchronize the updated model with the existing mysql schema, it doesn't work.
BUG #2:
It proposes the following steps to synchronize the schemas:
ALTER TABLE `mydb`.`table1` CHANGE COLUMN `fk_one` `fk_one` INT(11) NULL DEFAULT NULL ;
ALTER TABLE `mydb`.`table2` CHANGE COLUMN `table2_id` `table2_id` INT(11) NOT NULL ;
And it fails.
It can't change the fields while there is a constraint in place. It needs to 1)Remove any constraints on the field, 2)Make the change from SMALLINT to INT, then 3)Add the constraint back, with the same parameters it originally had.
At this point, synchronization is useless because it will fail every time.
Suggested fix:
Suggested fixes outlined above.
I've been using Workbench on a big schema, and had to change an employee ID field from SMALLINT to INT, and that field was a foreign key in 6 other tables. So I had to manually update the field type in each of them, and now synchronization fails because it doesn't handle the constraints properly.
Description: Model synchronization, as well as modeling tools like the EER diagram, need to be smarter with updating foreign key constraints. How to repeat: Create new schema with two tables, and one foreign key: ------------------------------------------------------------------------ CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `mydb` ; CREATE TABLE IF NOT EXISTS `mydb`.`table2` ( `table2_id` SMALLINT NOT NULL , PRIMARY KEY (`table2_id`) ) ENGINE = InnoDB; CREATE TABLE IF NOT EXISTS `mydb`.`table1` ( `table1_id` INT NOT NULL , `fk_one` SMALLINT NULL , PRIMARY KEY (`table1_id`) , INDEX `fk_one` (`fk_one` ASC) , CONSTRAINT `fk_one` FOREIGN KEY (`fk_one` ) REFERENCES `mydb`.`table2` (`table2_id` ) ON DELETE NO ACTION ON UPDATE CASCADE) ENGINE = InnoDB; ------------------------------------------------------------------------ Run the script, so you now have the schema up and running on a mysql instance, and it is identical to the model. In the model, change table2_id from SMALLINT to INT. BUG #1: Right here, Workbench should either A) Prompt you whether you'd like it to change table1.fk_one to INT as well, or B) automatically do it. It should not allow the discrepancy of SMALLINT vs INT to exist in the diagram when a constraint is in place. Moving on. Say I manually change both table2_id and table1.fk_one to INT in my model so everything is correct. When I try to synchronize the updated model with the existing mysql schema, it doesn't work. BUG #2: It proposes the following steps to synchronize the schemas: ALTER TABLE `mydb`.`table1` CHANGE COLUMN `fk_one` `fk_one` INT(11) NULL DEFAULT NULL ; ALTER TABLE `mydb`.`table2` CHANGE COLUMN `table2_id` `table2_id` INT(11) NOT NULL ; And it fails. It can't change the fields while there is a constraint in place. It needs to 1)Remove any constraints on the field, 2)Make the change from SMALLINT to INT, then 3)Add the constraint back, with the same parameters it originally had. At this point, synchronization is useless because it will fail every time. Suggested fix: Suggested fixes outlined above. I've been using Workbench on a big schema, and had to change an employee ID field from SMALLINT to INT, and that field was a foreign key in 6 other tables. So I had to manually update the field type in each of them, and now synchronization fails because it doesn't handle the constraints properly.