MySQL Bugs: #55157: Attempt to add existing foreing key

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

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:
--

Found workaround at least in my case: for tables with problem described in the bug report choose 'update model' during synchronization.

I think this is a duplicate of bug #54363 essentially. Please, check.

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.

This bug here is set as duplicate of bug #54363