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:Microsoft Windows
Assigned to: CPU Architecture:Any

[29 Jul 2016 0:38] Matt Liston
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)
[29 Jul 2016 0:39] Matt Liston
Error Screenshot

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

[29 Jul 2016 4:47] Umesh Shastry
Hello Matt Liston,

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

Thanks,
Umesh
[16 Jul 2017 21:38] Andrew Martin
Is this a limitation on MySQL with JSON? still happening in 6.3.9 about 1 year later.
[15 Nov 2018 20:48] carlos jimenez
Hi, this bug still happening in Mysql Workbench 8.0.11 2 years later, ┬┐Someone has any comment about this?
Thanks
[25 Jan 18:46] Justin Levene
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.