{noformat}
create or replace table a (
cola int(10) primary key,
v_cola int(10) as (cola mod 10) virtual,
p_cola int(10) as (cola mod 10) persistent
);
create index v_cola on a (v_cola);
create index p_cola on a (p_cola);
create or replace table b(
cola int(10),
v_cola int(10),
p_cola int(10)
);
alter table b add constraint `p_cola_fk`
foreign key (p_cola) references a (p_cola)
on delete restrict
on update restrict;
alter table b add constraint `v_cola_fk`
foreign key (v_cola) references a (v_cola)
on delete restrict
on update restrict;
{noformat}
{noformat}
create or replace table a (
cola int(10) primary key,
v_cola int(10) as (cola mod 10) virtual,
p_cola int(10) as (cola mod 10) persistent
);
create index v_cola on a (v_cola);
create index p_cola on a (p_cola);
create or replace table b(
cola int(10),
v_cola int(10),
p_cola int(10)
);
alter table b add constraint `p_cola_fk`
foreign key (p_cola) references a (p_cola)
on delete restrict
on update restrict;
alter table b add constraint `v_cola_fk`
foreign key (v_cola) references a (v_cola)
on delete restrict
on update restrict;
{noformat}
{code}
ERROR 1005 (HY000): Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed")
MariaDB [test]> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 150 | Alter table `test`.`b` with foreign key constraint failed. Parse error in '
foreign key (v_cola) references a (v_cola)
on delete restrict
on update restrict' near 'v_cola)
on delete restrict
on update restrict'. |
| Error | 1005 | Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed") |
| Warning | 1215 | Cannot add foreign key constraint |
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
Is it possible to match the columns by position (with a debug assertion that the names match with strcmp), instead of looping over all columns and looking for a matching name using a case-insensitive search?
Also, please import (and if necessary, adapt) all the virtual column tests from MySQL 5.7 that are related to foreign keys, such as innodb.virtual_basic, innodb.virtual_fk, innodb.virtual_fk_restart. We want to make sure that the patch is not breaking other functionality.
Marko Mäkelä
added a comment - Is it possible to match the columns by position (with a debug assertion that the names match with strcmp), instead of looping over all columns and looking for a matching name using a case-insensitive search?
Also, please import (and if necessary, adapt) all the virtual column tests from MySQL 5.7 that are related to foreign keys, such as innodb.virtual_basic, innodb.virtual_fk, innodb.virtual_fk_restart. We want to make sure that the patch is not breaking other functionality.
I could not find easy way to map column positions to virtual column number. Tests you mention are already part of the gcol suite. Note that MySQL 5.7 does not support the above syntax or foreign keys to virtual columns:
commit 45f451c769668e6a351b0c023a994fdf9c07b1f7
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Fri Jan 20 12:10:13 2017 +0200
MDEV-11850: Can't create foreign key referencing a virtual column
Both dict_foreign_find_index and dict_foreign_qualify_index
did not consider virtual columns as possible foreign key
columns and there was assertion to disable virtual columns.
Fixed by also looking referencing and referenced column
from virtual columns if needed.
Jan Lindström (Inactive)
added a comment - commit 45f451c769668e6a351b0c023a994fdf9c07b1f7
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Fri Jan 20 12:10:13 2017 +0200
MDEV-11850 : Can't create foreign key referencing a virtual column
Both dict_foreign_find_index and dict_foreign_qualify_index
did not consider virtual columns as possible foreign key
columns and there was assertion to disable virtual columns.
Fixed by also looking referencing and referenced column
from virtual columns if needed.
http://lists.askmonty.org/pipermail/commits/2017-January/010471.html