MySQL Bugs: #92221: MySQL Workbench will not show multiple column foreign key in the Navigator

Bug #92221	MySQL Workbench will not show multiple column foreign key in the Navigator
Submitted:	29 Aug 2018 10:31	Modified:	29 Aug 2018 11:31
Reporter:	Jens Andersen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	8.0 CE, 8.0.12	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
When creating a foreign key on multiple columns on a table, Workbench will not show the foreign key in the "Navigator" of the child table.

But if you right-click on the child table and then click "Alter table" --> "Foreign Keys" you can see that there IS a foreign key relationship.

Running the script below will create everyhting that is needed to show the error. If you remove one of the columns in the foreign key of the child table, it WILL show up in the Navigator.

How to repeat:
CREATE SCHEMA `TEST_SCHEMA` ;

CREATE TABLE `TEST_SCHEMA`.`PARENT_TABLE` (
  `KEY_1` int(11) NOT NULL,
  `KEY_2` int(11) NOT NULL,
  PRIMARY KEY (`KEY_1`,`KEY_2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `TEST_SCHEMA`.`CHILD_TABLE` (
  `ID` int(11) NOT NULL,
  `PARENT_KEY_1` int(11) NOT NULL,
  `PARENT_KEY_2` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK_idx` (`PARENT_KEY_1`,`PARENT_KEY_2`),
  CONSTRAINT `FK_CHILD_TABLE` FOREIGN KEY (`PARENT_KEY_1`, `PARENT_KEY_2`) REFERENCES `PARENT_TABLE` (`KEY_1`, `KEY_2`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Hello Jens Andersen,

Thank you for the report!
Observed this with MySQL Workbench Commercial (Proprietary) for Windows version 8.0.12 SE build 13312926 (64 bit) on Win7.

regards,
Umesh

Hello,

I have a similar issue with MySQL Workbench 8.0 CE 8.0.28 under macOS.
The constraint absent in navigator under Constraints.
When clicking on the (i) near the table name in the navigator and moving to Foreign Keys tab, it shows 2 rows instead of 1.
With a right click on the table, then select alter table then foreign keys, the information is correct.

Here is my SQL code. 

CREATE SCHEMA `test_schema`;

CREATE TABLE IF NOT EXISTS `test_schema`.`test` (
  `k1` INT NOT NULL,
  `k2` INT NOT NULL,
  `k1_father` INT NOT NULL,
  `k2_father` INT NOT NULL,
  PRIMARY KEY (`k1`, `k2`),
  CONSTRAINT `fk_father`
    FOREIGN KEY (`k1_father`, `k2_father`)
    REFERENCES `test_schema`.`test` (`k1`, `k2`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
;

Best regards.

Regards Michel.