MySQL Bugs: #79918: Cannot add foreign key constraint

Bug #79918	Cannot add foreign key constraint
Submitted:	11 Jan 2016 16:52	Modified:	18 Apr 2019 16:25
Reporter:	Stefano Giacone	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.7.10	OS:	Any (Win7 and Ubuntu)
Assigned to:		CPU Architecture:	Any

Description:
Creating a table with a virtual column in a index and a foreign key constraint raise the error "Cannot add foreign key constraint" (it shouldn't happen). This is not happening on 5.7.9. Tested on Ubuntu and Windows 7 on 5.7.10
This is critical since it's not possible to import in 5.7.10 a database created with 5.7.9.

The code below is working correctly on 5.7.9, but raise "Cannot add foreign key constraint" on 5.7.10. 
NOTE: SHOW ENGINE INNODB STATUS; does not show "LATEST FOREIGN KEY ERROR"

How to repeat:
set @@foreign_key_checks=0;

create table first_table(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
field1 int(11) unsigned,
field2 int(11) GENERATED ALWAYS AS (field1+1) virtual,
test varchar(50),
PRIMARY KEY (id),
key test (test),
key test2 (test,field2) USING BTREE,
FOREIGN KEY (`test`) REFERENCES second_table (`test`) ON DELETE cascade ON UPDATE CASCADE
);

the following, instead, is working (removed the index with the virtual column):

create table first_table(
id int(11) unsigned NOT NULL AUTO_INCREMENT,
field1 int(11) unsigned,
field2 int(11) GENERATED ALWAYS AS (field1+1) virtual,
test varchar(50),
PRIMARY KEY (id),
key test (test),
-- key test2 (test,field2) USING BTREE,
FOREIGN KEY (`test`) REFERENCES second_table (`test`) ON DELETE cascade ON UPDATE CASCADE
);

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.11 Source distribution PULL: 2015-DEC-27

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > set @@foreign_key_checks=0;
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 >
mysql 5.7 > create table first_table(
    -> id int(11) unsigned NOT NULL AUTO_INCREMENT,
    -> field1 int(11) unsigned,
    -> field2 int(11) GENERATED ALWAYS AS (field1+1) virtual,
    -> test varchar(50),
    -> PRIMARY KEY (id),
    -> key test (test),
    -> key test2 (test,field2) USING BTREE,
    -> FOREIGN KEY (`test`) REFERENCES second_table (`test`) ON DELETE cascade ON UPDATE CASCADE
    -> );
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql 5.7 >

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set @@foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> create table first_table(
    -> id int(11) unsigned NOT NULL AUTO_INCREMENT,
    -> field1 int(11) unsigned,
    -> field2 int(11) GENERATED ALWAYS AS (field1+1) virtual,
    -> test varchar(50),
    -> PRIMARY KEY (id),
    -> key test (test),
    -> key test2 (test,field2) USING BTREE,
    -> FOREIGN KEY (`test`) REFERENCES second_table (`test`) ON DELETE cascade ON UPDATE CASCADE
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>

Please check:

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-10.html

"InnoDB: A secondary index is not permitted on a virtual column that is based on a foreign key-referenced column that uses ON DELETE CASCADE, ON DELETE SET NULL, ON UPDATE CASCADE, or ON UPDATE SET NULL. The restriction was not enforced. (Bug #21508402)"

Thanks.

Thanks very much for your feedback, maybe I misunderstood the documentation:

in the example provided, as you can see, there are 2 indexes: I believe the foreign key should use the index "test" and not the index "test2" that is the "faulty" secondary index. From the documentation "A secondary index on a virtual column cannot be used as the index for a foreign key.": what I'm saying is that I'm not using that secondary index as the index for the foreign key. "Secondary indexes are not supported on virtual columns that have a base column that is referenced in a foreign key constraint": the foreign key constraint in the example should not use the "faulty" secondary index. Maybe we're saying you can't tell mysql which index to use for a foreign key constraint?

Not sure if:
- I'm misunderstanding the documentation (could be, sorry for that!!)
- The documentation can be more clear on it
- It's an actual bug (maybe it was enforced in a slightly wrong way)

Let me know your thoughts on it, thanks again.

Thank you for the feedback.

I am on MacOS Sierra 10.12.5 . I have installed MySQL 5.7.18 through HomeBrew. I am facing an issue which is very similar to what is described by the OP.

The following does NOT work. When creating the "reports" table, it throws an error: Kernel error: Error( 1215 ) HY000: "Cannot add foreign key constraint"
=================================
CREATE TABLE `templates` (
	`id` varbinary(16) NOT NULL PRIMARY KEY, 
	`uuid` varchar(36) AS (insert( insert( insert( insert( hex(id),9,0,'-' ), 14,0,'-' ), 19,0,'-' ), 24,0,'-' )), 
	`name` varchar(255) NOT NULL, 
	UNIQUE INDEX `index_templates_on_uuid`  (`uuid`)
) ENGINE=InnoDB

CREATE TABLE `reports` (
	`id` varbinary(16) NOT NULL PRIMARY KEY, 
	`uuid` varchar(36) AS (insert( insert( insert( insert( hex(id),9,0,'-' ), 14,0,'-' ), 19,0,'-' ), 24,0,'-' )),
	`template_id` varbinary(16) NOT NULL, 
	`css` text, 
	UNIQUE INDEX `index_reports_on_uuid`  (`uuid`),  
	INDEX `index_reports_on_template_id`  (`template_id`),  
	CONSTRAINT `fk_rails_71741adcd8` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`)
) ENGINE=InnoDB
================================

Note the columns order in the "reports" table is:
* id
* uuid (virtual column)
* template_id (foreign key)
* css

If I just make a small change to the "CREATE TABLE" of the "reports" table, it works.
================================
CREATE TABLE `reports` (
	`id` varbinary(16) NOT NULL PRIMARY KEY, 
	`uuid` varchar(36) AS (insert( insert( insert( insert( hex(id),9,0,'-' ), 14,0,'-' ), 19,0,'-' ), 24,0,'-' )),
	`css` text, 
	`template_id` varbinary(16) NOT NULL, 
	UNIQUE INDEX `index_reports_on_uuid`  (`uuid`),  
	INDEX `index_reports_on_template_id`  (`template_id`),  
	CONSTRAINT `fk_rails_71741adcd8` FOREIGN KEY (`template_id`) REFERENCES `templates` (`id`)
) ENGINE=InnoDB
================================

Note: the change I made is to just place the "css" column between the "uuid" and "template_id" columns. So, the new order of columns is:
* id
* uuid (virtual column)
* css
* template_id (foreign key)

Just re-ordering the columns makes it work!

The pattern I have noticed is: If the foreign key column is immediately after a virtual column, this error is thrown. If there is a non-foreign-key column in between, it works. Or, if the virtual column is placed at the end, it works.

This has to be a bug, right?

Posted by developer:
 
Hello!

This issue is addressed by the same patch as bug #22469130 / bug#79772
"FOREIGN KEY ON DELETE CASCADE NOT ALLOWED WHEN A VIRTUAL INDEX EXISTS".

So I am marking this report as a duplicate of the latter bug report.