MySQL Bugs: #53136: Textual queries generated by "Copy SQL to Clipboard" don't work on Connected DB

Bug #53136	Textual queries generated by "Copy SQL to Clipboard" don't work on Connected DB
Submitted:	23 Apr 2010 23:39	Modified:	11 May 2010 10:02
Reporter:	Joshua Levin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	5.2.19 OSS Beta Rev. 5680	OS:	Windows (Vista)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
Note -- the connected database is on a Linux server on Godaddy.

I established a table on a Model on MySQL Workbench,. and tried to "copy" it to the MySQL server on Godaddy.  It did not accept certain statements related to CONSTRAINTS.

When using "Copy SQL to Clipboard", I got the following code:

CREATE  TABLE IF NOT EXISTS `mydb`.`ISSUES` (
  `IssueID` INT NOT NULL AUTO_INCREMENT ,
  `PubID` SMALLINT(6)  NULL COMMENT 'Publication' ,
  `IssueDate` DATE NULL ,
  PRIMARY KEY (`IssueID`) ,
  UNIQUE INDEX `IssueID_UNIQUE` (`IssueID` ASC) ,
  INDEX `PubID` (`PubID` ASC) ,
  CONSTRAINT `PubID`
    FOREIGN KEY (`PubID` )
    REFERENCES `mydb`.`PUBLICATIONS` (`IssuesPerYear` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

However, Workbench noted errors unless I removed several lines of this code, getting:

CREATE  TABLE IF NOT EXISTS `mydb`.`ISSUES` (
  `IssueID` INT NOT NULL AUTO_INCREMENT ,
  `PubID` SMALLINT(6)  NULL COMMENT 'Publication' ,
  `IssueDate` DATE NULL ,
  PRIMARY KEY (`IssueID`) ,
  UNIQUE INDEX `IssueID_UNIQUE` (`IssueID` ASC) ,
  INDEX `PubID` (`PubID` ASC) ,
  CONSTRAINT `PubID`)
ENGINE = InnoDB

Note that the CONSTRAINT statement is present, but essentially empty.  This essentially kills any reference to the Foreign Key.

How to repeat:
See "Description", above.

Related to Bug #53117

I cannot include my .mwb file, as it also contains some other tables that are confidential.  If the developers really need an .mwb file, I'll generate another one with only the two tables present.

This used to work.  I think that was back in version .15b

I'm not sure the "Category", above ("Modeling") is correct.

Suggested fix:
Make sure that the affected query statements are handled properly.

Can you, please, send CREATE TABLE statement for that PUBLICATIONS table (via the same Copy SQL to Clipboard menu item, for example). I need to know how `IssuesPerYear` column is defined.

The SQL queries to generate the PUBLICATIONS table is as follows (some comments have been removed for security reasons):

CREATE  TABLE IF NOT EXISTS `mydb`.`PUBLICATIONS` (
  `PubID` INT NOT NULL ,
  `PubName` VARCHAR(80) NULL ,
  `PubAbbrev` VARCHAR(8) NULL ,
  `PubAbbrevAlt` VARCHAR(8) NULL ,
  `IssuesPerYear` SMALLINT(6)  NULL ,
  PRIMARY KEY (`PubID`) ,
  INDEX `PubID` (`PubID` ASC) )
ENGINE = InnoDB
COMMENT = 'List of Publications'

Indeed, MySQL Workbench allows to declare incorrect foreign key that references to column without PRIMARY KEY or even UNIQUE key declaration, like this:

CREATE  TABLE IF NOT EXISTS `mydb`.`table1` (
  `id1` INT NOT NULL ,
  `id2` VARCHAR(45) NULL ,
  PRIMARY KEY (`id1`) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `mydb`.`table2` (
  `id1` INT NOT NULL ,
  `id2` VARCHAR(45) NULL ,
  PRIMARY KEY (`id1`) ,
  INDEX `fk1` (`id2` ASC) ,
  CONSTRAINT `fk1`
    FOREIGN KEY (`id2` )
    REFERENCES `mydb`.`table1` (`id2` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Maybe some validation module in SE edition will catch this, but for now this is a bug. IMHO interface should prevent creation of incorrect foreign keys.

In FK editor, added filtering to list of columns allowed for a referenced FK so that only PK and indexed columns are allowed.

This seems to have been completely corrected in 5.2.20 OSS Beta Rev. 5783

fix confirmed in repository

fix confirmed in repository

An entry has been added to the 5.2.21 changelog:

MySQL Workbench allowed the creation of invalid foreign keys that referenced a column that did not use a PRIMARY KEY or UNIQUE key declaration.

MySQL Workbench has been changed to filter the list of columns allowed for a referenced foreign key, so that only primary key and indexed columns are listed.