| 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: | |
| 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: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 2019 16:28]
Dmitry Lenev
Posted by developer: Bug#22606977 / bug#79918 "CANNOT ADD FOREIGN KEY CONSTRAINT" has been marked as duplicate of this bug.
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.