MySQL Bugs: #82378: Table editor cannot create foreign key if referenced table has JSON column

Bug #82378	Table editor cannot create foreign key if referenced table has JSON column
Submitted:	29 Jul 2016 0:38	Modified:	29 Jul 2016 4:47
Reporter:	Matt Liston	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.3.7	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I am unable to create a foreign key using the table editor for tables containing a JSON type column. When trying to select a value in "Referenced Column", the drop down is blank except for "Specify column...", which does nothing when clicked. 

I am still able to create the foreign key by writing the SQL code and executing it in the editor area.

How to repeat:
1. Run this code to create two tables:

DROP TABLE IF EXISTS test1;
CREATE TABLE `test1` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `store_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS test2;
CREATE TABLE `test2` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Use the table editor to add a foreign key to table test1 with the following values:
Name = anything
Referenced Table = test2
Column = store_id
Referenced Column = id (but the drop down is empty)

Error Screenshot

Attachment: fk_test.png (image/png, text), 21.57 KiB.

Hello Matt Liston,

Thank you for the report and test case.
Observed this with WB 6.3.7 on Win7.

Thanks,
Umesh

Is this a limitation on MySQL with JSON? still happening in 6.3.9 about 1 year later.

Hi, this bug still happening in Mysql Workbench 8.0.11 2 years later, ¿Someone has any comment about this?
Thanks

I have found this bug as well.  I have to script foreign keys as MySQL Workbench can't do them due to this bug.

It doesn't seem to be tables with JSON fields in them (where the foreign key is being added), rather linking to a table with a JSON field in it.

In version 8.023 remains the problem. Only manually I can create the fk