MySQL Bugs: #39150: Remove a table referenced in foreing key constraint

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

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.

Thank you for a bug report. Verified just as described.

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.

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

An entry was added to the 5.0.25 changelog:

Foreign keys referencing a deleted table were not removed.