Bug #91963 Not able to apply foreign key constraint
Submitted: 10 Aug 2018 10:49 Modified: 10 Aug 2018 11:21
Reporter: Lavesh Pareek Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S5 (Performance)
Version:6.3.10, 8.0.12 OS:Microsoft Windows (Microsoft Windows 10 Pro)
Assigned to: CPU Architecture:Any
Tags: WBBugReporter

[10 Aug 2018 10:49] Lavesh Pareek
Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\101915\AppData\Roaming\MySQL\Workbench\log\wb.log]

Hi Team,

If we have json type field in one table then we cannot give foreign key reference for that table in any other table.

How to repeat:
Step 1: Run the following query to create the first table

CREATE TABLE `user_details` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(45) DEFAULT NULL,
  `firstName` varchar(45) DEFAULT NULL,
  `lastName` varchar(45) DEFAULT NULL,
  `password` varchar(45) DEFAULT NULL,
  `address` json DEFAULT NULL,
  `addresses` json DEFAULT NULL,
  `attributes` json DEFAULT NULL,
  `visible` bit(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step 2 : Run the following query to create the second table

CREATE TABLE `job` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `userId` int(10) unsigned NOT NULL,
  `salary` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Step 3 : Alter the JOb table to create a foreign key reference to user_details;
[10 Aug 2018 10:50] Lavesh Pareek
Screen to give the foreign key

Attachment: Capture 2.PNG (image/png, text), 16.79 KiB.

[10 Aug 2018 11:21] MySQL Verification Team
Hello Lavesh,

Thank you for the report!
Observed this with WB 8.0.12 on Win7.

Thanks,
Umesh