Bug #54834 Error when creating a foreign key referring to the same table
Submitted: 27 Jun 2010 8:22 Modified: 27 Jun 2010 9:46
Reporter: Damian Wielocha Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Workbench: Modeling Severity:S2 (Serious)
Version:5.2.24 OS:Any
Assigned to: CPU Architecture:Any

[27 Jun 2010 8:22] Damian Wielocha
Description:
There is an error when launching forward engineer process for table with a foreign key referring to the same table.
Creating table without foreign key and doing after model synchro doesn't help.

How to repeat:
1. Create a table with foreign key referring to the same table, ex.

CREATE  TABLE IF NOT EXISTS `newsletter_group` (
  `newsletter_group_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(255) NULL ,
  `parent_newsletter_group_id` SMALLINT NULL ,
  PRIMARY KEY (`newsletter_group_id`) ,
  INDEX `fk_newsletter_group_newsletter_group1` (`parent_newsletter_group_id` ASC) ,
  CONSTRAINT `fk_newsletter_group_newsletter_group1`
    FOREIGN KEY (`parent_newsletter_group_id` )
    REFERENCES `newsletter_group` (`newsletter_group_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

2. run: Database -> Forward Engineer..

Suggested fix:
Maybe when script detect there is a foreign key referring to the same table if creates only indexes, and foreign key statments will be executes as a separate ALTER query.
[27 Jun 2010 9:46] Damian Wielocha
Sleep, then code.. ;/