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.
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.