MySQL Bugs: #33656: Workbench Generates Invalid Foreign Index Statements

Bug #33656	Workbench Generates Invalid Foreign Index Statements
Submitted:	3 Jan 2008 16:28	Modified:	4 Feb 2008 20:49
Reporter:	Justin Noel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.0.11 SE Beta Rev 2342	OS:	Windows (XP 32 bit)
Assigned to:	Johannes Taxacher	CPU Architecture:	Any
Tags:	foreign, keys, SQL, statements, workbench

Description:
Workbench generates SQL statements that fail to execute in MySQL 5.0.15.

When I export my EER Diagram to "Forward Engineer Create SQL Script", the script has errors that prevent execution.  

Here is a small sample of the script:
CREATE  TABLE IF NOT EXISTS `coma`.`orders` (
  `orders_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `order_number` CHAR(15) NOT NULL ,
  `version` TINYINT UNSIGNED NOT NULL DEFAULT 0 ,
  `order_service_type` CHAR(4) NOT NULL ,
  `order_status_id` INT UNSIGNED NOT NULL ,
  `nd_order_status_id` INT UNSIGNED NOT NULL ,
  `company_id` INT UNSIGNED NOT NULL ,
  `signaling_partner_id` SMALLINT UNSIGNED NOT NULL ,
  `order_type_id` INT UNSIGNED NOT NULL ,
  `modified` TIMESTAMP NOT NULL ,
  `created` TIMESTAMP NULL ,
  PRIMARY KEY (`orders_id`, `nd_order_status_id`, `company_id`, `order_status_id`, `signaling_partner_id`, `order_type_id`) ,
  FOREIGN INDEX fk_orders_nd_order_status (`nd_order_status_id` ASC) ,
  FOREIGN INDEX fk_orders_company (`company_id` ASC) ,
  FOREIGN INDEX fk_orders_order_status (`order_status_id` ASC) ,
  FOREIGN INDEX fk_orders_signaling_partner (`signaling_partner_id` ASC) ,
  FOREIGN INDEX fk_orders_order_type (`order_type_id` ASC) ,
  CONSTRAINT `fk_orders_nd_order_status`
    FOREIGN KEY (`nd_order_status_id` )
    REFERENCES `coma`.`nd_order_status` (`nd_order_status_id` )
    ON DELETE RESTRICT
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_orders_company`
    FOREIGN KEY (`company_id` )
    REFERENCES `coma`.`company` (`company_id` )
    ON DELETE RESTRICT
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_orders_order_status`
    FOREIGN KEY (`order_status_id` )
    REFERENCES `coma`.`order_status` (`order_status_id` )
    ON DELETE RESTRICT
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_orders_signaling_partner`
    FOREIGN KEY (`signaling_partner_id` )
    REFERENCES `coma`.`signaling_partner` (`signaling_partner_id` )
    ON DELETE RESTRICT
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_orders_order_type`
    FOREIGN KEY (`order_type_id` )
    REFERENCES `coma`.`order_type` (`order_type_id` )
    ON DELETE RESTRICT
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Here is a sample of the errors this create statement generates:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INDEX fk_orders_nd_order_status (`nd_order_status_id` ASC) ,
  FOREIGN INDEX fk' at line 14

How to repeat:
Generate Innodb tables in Workbench with foreign keys.

Suggested fix:
Correct the mechanism that produces incorrect SQL syntax.

Thank you for the bug report.

-- -----------------------------------------------------
-- Table `coma`.`signaling_partner`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `coma`.`signaling_partner` (
  `signaling_partner_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(35) NOT NULL ,
  `sos_company_id_number` SMALLINT UNSIGNED NOT NULL ,
  PRIMARY KEY (`signaling_partner_id`) )
ENGINE = InnoDB
Error 1005: Can't create table 'coma.nd_order_status' (errno: 150)

Notice that the example model supplied for testing still cause errors when applied to server because of datatype mismatches. This is an expected behavior.

correct INDEX-Statements are now created