Bug #79772 Foreign key not allowed when a virtual index exists
Submitted: 25 Dec 2015 4:12 Modified: 14 Jun 2016 14:22
Reporter: Jesper wisborg Krogh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[25 Dec 2015 4:12] Jesper wisborg Krogh
Description:
In some cases when an index exists on a virtual column, it is not allowed to create a foreign key on the table.

How to repeat:
CREATE TABLE t1 (t1_id int unsigned NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (t1_id int unsigned NOT NULL, val int unsigned, val2 bigint unsigned GENERATED ALWAYS AS (val*val) VIRTUAL NOT NULL, PRIMARY KEY (t1_id), INDEX (val2), FOREIGN KEY (t1_id) REFERENCES t1 (t1_id) ON DELETE CASCADE ) ENGINE=InnoDB;

Fails with:

     ERROR 1215 (HY000): Cannot add foreign key constraint

Removing the index on val2 and the table is created.

mysq> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 150
Message: Create table 'db1/t2' with foreign key constraint failed. Cannot add foreign key constraint placed on the base column of indexed virtual column, or constraint placed on columns being part of virtual index.

*************************** 2. row ***************************
  Level: Error
   Code: 1215
Message: Cannot add foreign key constraint
2 rows in set (0.00 sec)

But the foreign key is not places on the base of the virtual column nor in any way related to the virtual column.
[25 Dec 2015 4:16] Jesper wisborg Krogh
Posted by developer:
 
Is allowed:
-----------

CREATE TABLE t1 (t1_id int unsigned NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (t1_id int unsigned NOT NULL, val int unsigned, val2 bigint unsigned GENERATED ALWAYS AS (val*val) VIRTUAL NOT NULL, PRIMARY KEY (t1_id), INDEX (val2), FOREIGN KEY (t1_id) REFERENCES t1 (t1_id) ) ENGINE=InnoDB;

So the trigger is ON DELETE CASCADE.
[15 Feb 2016 21:09] Jesper wisborg Krogh
Posted by developer:
 
See also Bug 22687023 / http://bugs.mysql.com/bug.php?id=80304
[30 May 2016 13:11] Daniel Price
Posted by developer:
 
commit 392bf0da8e3e331b5a93374c4d7f0ed67d6ad023
Author: Thirunarayanan Balathandayuthapani <thirunarayanan.balathandayuth@oracle
Date:   Mon May 30 12:12:50 2016 +0530

    Bug #22469130 FOREIGN KEY ON DELETE CASCADE NOT ALLOWED
                WHEN A VIRTUAL INDEX EXISTS
    
    Description:
    ===========
    i) Allow cascade operations on foreign key constraint on base column of
    virtual column if virtual index exists.
    
    ii) Allow cascade operations on virtual index contains foreign key
    constraint column.
[14 Jun 2016 14:22] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 5.7.14 release, and here's the changelog entry:

InnoDB now permits defining a foreign key constraint with a cascading
referential action on the base column of an indexed virtual column, and
defining cascading referential actions on non-virtual foreign key columns
that are explicitly included in a virtual index.
[1 Jul 2016 10:40] Erlend Dahl
Bug#80957 Alter table allows "Blocked FK constraint on base column for virtual column"

was marked as a duplicate.
[18 Apr 16:28] Dmitry Lenev
Posted by developer:
 
Bug#22606977 / bug#79918 "CANNOT ADD FOREIGN KEY CONSTRAINT"
has been marked as duplicate of this bug.