Bug #105090 FK cannot be created when the table has a UNIQUE KEY in a virtual generated col
Submitted: 30 Sep 17:23 Modified: 30 Sep 18:24
Reporter: Eduardo Ubide Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.35 OS:Any
Assigned to: CPU Architecture:x86
Tags: foreign keys, unique index, virutal columns

[30 Sep 17:23] Eduardo Ubide
Description:
When attempting to create an FK on a table that has a virtual generated column, the operation fails if the virtual column has a unique key. 

An example of this type of table is. 

 CREATE TABLE `t_main` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(32) GENERATED ALWAYS AS ('1') VIRTUAL ,
  `t_ref_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`unique_id`),
  FOREIGN KEY (`t_ref_id`) REFERENCES t_ref (id)
) ENGINE=InnoDB ; 

ERROR 1215 (HY000): Cannot add foreign key constraint

The above definition fails, but this other definition does not fail. 

CREATE TABLE `t_main` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(32) GENERATED ALWAYS AS ('1') VIRTUAL ,
  `t_ref_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
 	-- UNIQUE KEY (`unique_id`),
 	KEY (`unique_id`),
  FOREIGN KEY (`t_ref_id`) REFERENCES t_ref (id)
) ENGINE=InnoDB ; 

The problem only affects the latest versions of 5.7 (tested on 5.7.35, 5.7.31, 5.7.29, 5.7.25), in version 8.0.26 there is no problem. 

How to repeat:
DROP DATABASE if exists sandbox ;
CREATE DATABASE sandbox;
USE sandbox;

DROP TABLE IF EXISTS `t_ref`;
CREATE TABLE `t_ref` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `t_main`;
CREATE TABLE `t_main` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `unique_id` varchar(32) GENERATED ALWAYS AS ('1') VIRTUAL ,
  `t_ref_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`unique_id`),
  FOREIGN KEY (`t_ref_id`) REFERENCES t_ref (id)
) ENGINE=InnoDB ; 

The last DDL fails on 

ERROR 1215 (HY000): Cannot add foreign key constraint

Suggested fix:
1) Either the documentation is updated to explain this behavior 

https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html

A secondary index may be created on one or more virtual columns or on a combination of virtual columns and regular columns or stored generated columns. Secondary indexes that include virtual columns may be defined as UNIQUE.

2) or, if there is no technical reason, let the FK definition works as expected.
[30 Sep 18:24] MySQL Verification Team
Thank you for the bug report.