Bug #55157 Attempt to add existing foreing key
Submitted: 10 Jul 2010 19:23 Modified: 12 Jul 2010 9:09
Reporter: Andrei Frolov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S1 (Critical)
Version:5.2.25 OS:Windows
Assigned to: CPU Architecture:Any
Tags: model, synchronization

[10 Jul 2010 19:23] Andrei Frolov
Description:
WB trying to add existing foreign key.

Example:

table in database:

CREATE TABLE `item_category` (
  `ItemID` int(11) NOT NULL,
  `CategoryID` int(11) NOT NULL,
  `DepartmentID` int(11) NOT NULL,
  PRIMARY KEY  (`CategoryID`,`DepartmentID`,`ItemID`),
  KEY `icat_ItemID_fk` (`ItemID`),
  KEY `icat_CategoryID_fk` (`CategoryID`),
  KEY `icat_DepartmentID_fk` (`DepartmentID`),
  CONSTRAINT `icat_ItemID_fk` FOREIGN KEY (`ItemID`) REFERENCES `item` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `icat_CategoryID_fk` FOREIGN KEY (`CategoryID`) REFERENCES `category` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `icat_DepartmentID_fk` FOREIGN KEY (`DepartmentID`) REFERENCES `department` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

Part of WB synchronization script:

ALTER TABLE `pos`.`item_category` 
  ADD CONSTRAINT `icat_CategoryID_fk`
  FOREIGN KEY (`CategoryID` )
  REFERENCES `pos`.`category` (`ID` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

sometimes WB adds DROP FOREIGN KEY, but in most cases - not.

How to repeat:
--
[10 Jul 2010 19:43] Andrei Frolov
Found workaround at least in my case: for tables with problem described in the bug report choose 'update model' during synchronization.
[11 Jul 2010 12:24] Valeriy Kravchuk
I think this is a duplicate of bug #54363 essentially. Please, check.
[11 Jul 2010 18:56] Andrei Frolov
It could be. Actually in my case WB fails to recognize that change it is trying to do already done during previouse synchronization (even deletion of the key in database do not help) and keeps adding the same key over & over again with every synchronization. So it's understandable that the script like this can't be executed without errors. If Bug #54363 means something like this, then yes, mine is a duplicate.
[12 Jul 2010 9:09] Susanne Ebrecht
This bug here is set as duplicate of bug #54363