Bug #39150 Remove a table referenced in foreing key constraint
Submitted: 1 Sep 2008 7:45 Modified: 13 Oct 2008 15:47
Reporter: Lorenzo Luconi Trombacchi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench Severity:S3 (Non-critical)
Version:5.0.24 OS:Any
Assigned to: Sergei Tkachenko CPU Architecture:Any

[1 Sep 2008 7:45] Lorenzo Luconi Trombacchi
Description:
If you create two tables, for example table1 and table2, and create a foreign key contraint for table1 to table2 and then you remove the second, Workbench do it without any alert message or action.

I don't know if is it better to remove all foreing keys referencing table2 or display an alert message, but Workbench should do something when you remove a referenced table.

If you export sql after you removed table2 you got this sql create script:

CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL ,
  `a` INT NOT NULL ,
  `table2` BIGINT UNSIGNED NOT NULL ,
  PRIMARY KEY (`idtable1`) ,
  INDEX fk1 (`table2` ASC) ,
  CONSTRAINT `fk1`
    FOREIGN KEY (`table2` )
    REFERENCES `mydb`.`table2` (`idtable2` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

But if you do the same export after you close and open again your Workbench project you got this sql create script:

CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `idtable1` INT NOT NULL ,
  `a` INT NOT NULL ,
  `table2` BIGINT UNSIGNED NOT NULL ,
  PRIMARY KEY (`idtable1`) ,
  INDEX fk1 (`table2` ASC) ,
  CONSTRAINT `fk1`
    FOREIGN KEY (`table2` )
    REFERENCES `` (`` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

The first create is invalid because you reference a table that does not exists any more, but the second is syntactically invalid!!

How to repeat:
Create two InnoDB tables, add a foreign key to the first referencing the second and then remove the referenced table.
[1 Sep 2008 12:04] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[9 Sep 2008 16:40] Sergei Tkachenko
WB now silently deletes all foreign keys referencing the deleted table and indexes corresponding to those foreign keys. Undoing the delete table action will restore foreign keys and indexes along with the table.
[10 Sep 2008 12:31] Johannes Taxacher
if the user deletes a table all FKs in other tables referencing the deleted table are removed as well.
this fix will be incorporated ion 5.0.25
[13 Oct 2008 15:47] Tony Bedford
An entry was added to the 5.0.25 changelog:

Foreign keys referencing a deleted table were not removed.