Debian Codegraph @

mysql-test/suite/innodb/t/innodb-fk-virtual.test

raw
1 --source include/have_innodb.inc
2
3 #
4 # MDEV-11850: Can't create foreign key referencing a virtual column
5 #
6
7 create or replace table a (
8 cola int(10) primary key,
9 v_cola int(10) as (cola mod 10) virtual,
10 p_cola int(10) as (cola mod 10) persistent
11 ) engine=innodb;
12
13 create index v_cola on a (v_cola);
14 create index p_cola on a (p_cola);
15
16 create or replace table b(
17 cola int(10),
18 v_cola int(10),
19 p_cola int(10),
20 c_cola int(10) as (cola + 2) virtual
21 ) engine=innodb;
22
23 alter table b add constraint `p_cola_fk`
24 foreign key (p_cola) references a (p_cola)
25 on delete restrict
26 on update restrict;
27
28 show warnings;
29 show create table b;
30
31 alter table b add constraint `v_cola_fk`
32 foreign key (v_cola) references a (v_cola)
33 on delete restrict
34 on update restrict;
35
36 show warnings;
37 show create table b;
38
39 alter table b add constraint `c_cola_fk`
40 foreign key (c_cola) references a (cola)
41 on delete restrict
42 on update restrict;
43
44 show warnings;
45 show create table b;
46
47 #
48 # Test that fk really works
49 #
50
51 insert into a(cola) values (12);
52 select * from a;
53 insert into b(cola, v_cola, p_cola) values (10,2,2);
54 select * from b;
55 --error 1452
56 insert into b(cola, v_cola, p_cola) values (10,1,1);
57 --error 1451
58 delete from a;
59 select * from a;
60 select * from b;
61 drop table b, a;