Bug #91963 Not able to apply foreign key constraint

• Submitted: 10 Aug 2018 10:49
• Modified: 10 Aug 2018 11:21
• Reporter: Lavesh Pareek
• Status: Verified
• Category: MySQL Workbench
• Severity: S5 (Performance)
• Version: 6.3.10, 8.0.12
• OS: Windows (Microsoft Windows 10 Pro)
• 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