MySQL Bugs: #39124: No Validation of Index in Referenced Table when Referencing Non-Primary Key

Bug #39124	No Validation of Index in Referenced Table when Referencing Non-Primary Key
Submitted:	29 Aug 2008 16:10	Modified:	8 Feb 2013 15:18
Reporter:	Justin Noel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S3 (Non-critical)
Version:	5.024 SE	OS:	Windows (XP SP3)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	foreign key, INDEX, workbench

Description:
MySQL Workbench does not validate that the referenced column in a table has an index when adding a foreign key constraint in another table.  

Per the MySQL 5.0 reference docs : 
"InnoDB allows a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order. "

How to repeat:
Create 2 InnoDB tables (table1 and table2).

CREATE  TABLE IF NOT EXISTS `test_db`.`table1` (
  `table1_id` INT NOT NULL ,
  `info` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`table1_id`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `test_db`.`table2` (
  `table2_id` INT NOT NULL ,
  `info` VARCHAR(45) NOT NULL ,
  `test_id` INT NOT NULL ,
  PRIMARY KEY (`table2_id`) )
ENGINE = InnoDB

-----------------------------------------------------------------
Now, create a non-identifying relationship (1:n) between table1 and table 2.  The relationship and foreign keys will be created by default to the primay key of table2.

CREATE  TABLE IF NOT EXISTS `itp_linksets`.`table1` (
  `table1_id` INT NOT NULL ,
  `info` VARCHAR(45) NOT NULL ,
  `table2_id` INT NULL ,
  PRIMARY KEY (`table1_id`) ,
  INDEX fk_table1_table2 (`table2_id` ASC) ,
  CONSTRAINT `fk_table1_table2`
    FOREIGN KEY (`table2_id` )
    REFERENCES `itp_linksets`.`table2` (`table2_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `itp_linksets`.`table2` (
  `table2_id` INT NOT NULL ,
  `info` VARCHAR(45) NOT NULL ,
  `test_id` INT NOT NULL ,
  PRIMARY KEY (`table2_id`) )
ENGINE = InnoDB

----------------------------------------------------------------------

Now, edit table1 and change the the referenced column for the constraint "fk_table1_table2" from 'table2_id' to 'info'.  The SQL will now look like:

CREATE  TABLE IF NOT EXISTS `itp_linksets`.`table1` (
  `table1_id` INT NOT NULL ,
  `info` VARCHAR(45) NOT NULL ,
  `table2_id` INT NULL ,
  PRIMARY KEY (`table1_id`) ,
  INDEX fk_table1_table2 (`info` ASC) ,
  CONSTRAINT `fk_table1_table2`
    FOREIGN KEY (`info` )
    REFERENCES `itp_linksets`.`table2` (`info` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

-------------------------------------------------------------------------------
Now, export to SQL via File->Export->Forward Engineer SQL Script, provide a file name, press 'Next'. Do not filter anything, just press 'Next' again.  

Click on the 'Run Validation'.  You will get a warning that "Foreign key 'fk_table1_table2' of table 'table2' refers non-Primary Key field 'info'".

However, you do NOT get a warning that the foreign key constraint will fail because table2.info is not indexed.

Suggested fix:
The validation process should produce an ERROR when you attempt to create a foreign key constraint against a referenced field that has no index.

Thank you for the bug report. Could you please provide the project file which presents this behavior. Thanks in advance.

MWB File for 39124

Attachment: Bug 39124.mwb (application/octet-stream, text), 4.85 KiB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I provided the requested MWB on 2008-09-15.  No additional requests have been made.

Verified just as described using the .mwb file uploaded. Validation process should check for index.

we'll add a new validation check for this case

I suggest that this is related-to or a dupe of http://bugs.mysql.com/bug.php?id=44699

Bug #44699 has set as duplicate of this bug here.

No reproducible in the latest WB Version
Verified in:
Ubuntu 12.04x64
Windows7x64
WB 5.2.46 rev.10385