Debian Codegraph @

mysql-test/suite/innodb/r/innodb-fk-virtual.result

raw
1 create or replace table a (
2 cola int(10) primary key,
3 v_cola int(10) as (cola mod 10) virtual,
4 p_cola int(10) as (cola mod 10) persistent
5 ) engine=innodb;
6 create index v_cola on a (v_cola);
7 create index p_cola on a (p_cola);
8 create or replace table b(
9 cola int(10),
10 v_cola int(10),
11 p_cola int(10),
12 c_cola int(10) as (cola + 2) virtual
13 ) engine=innodb;
14 alter table b add constraint `p_cola_fk`
15 foreign key (p_cola) references a (p_cola)
16 on delete restrict
17 on update restrict;
18 show warnings;
19 Level Code Message
20 show create table b;
21 Table Create Table
22 b CREATE TABLE `b` (
23 `cola` int(10) DEFAULT NULL,
24 `v_cola` int(10) DEFAULT NULL,
25 `p_cola` int(10) DEFAULT NULL,
26 `c_cola` int(10) GENERATED ALWAYS AS (`cola` + 2) VIRTUAL,
27 KEY `p_cola_fk` (`p_cola`),
28 CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`)
29 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
30 alter table b add constraint `v_cola_fk`
31 foreign key (v_cola) references a (v_cola)
32 on delete restrict
33 on update restrict;
34 show warnings;
35 Level Code Message
36 show create table b;
37 Table Create Table
38 b CREATE TABLE `b` (
39 `cola` int(10) DEFAULT NULL,
40 `v_cola` int(10) DEFAULT NULL,
41 `p_cola` int(10) DEFAULT NULL,
42 `c_cola` int(10) GENERATED ALWAYS AS (`cola` + 2) VIRTUAL,
43 KEY `p_cola_fk` (`p_cola`),
44 KEY `v_cola_fk` (`v_cola`),
45 CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`),
46 CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`)
47 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
48 alter table b add constraint `c_cola_fk`
49 foreign key (c_cola) references a (cola)
50 on delete restrict
51 on update restrict;
52 show warnings;
53 Level Code Message
54 show create table b;
55 Table Create Table
56 b CREATE TABLE `b` (
57 `cola` int(10) DEFAULT NULL,
58 `v_cola` int(10) DEFAULT NULL,
59 `p_cola` int(10) DEFAULT NULL,
60 `c_cola` int(10) GENERATED ALWAYS AS (`cola` + 2) VIRTUAL,
61 KEY `p_cola_fk` (`p_cola`),
62 KEY `v_cola_fk` (`v_cola`),
63 KEY `c_cola_fk` (`c_cola`),
64 CONSTRAINT `c_cola_fk` FOREIGN KEY (`c_cola`) REFERENCES `a` (`cola`),
65 CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`),
66 CONSTRAINT `v_cola_fk` FOREIGN KEY (`v_cola`) REFERENCES `a` (`v_cola`)
67 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
68 insert into a(cola) values (12);
69 select * from a;
70 cola v_cola p_cola
71 12 2 2
72 insert into b(cola, v_cola, p_cola) values (10,2,2);
73 select * from b;
74 cola v_cola p_cola c_cola
75 10 2 2 12
76 insert into b(cola, v_cola, p_cola) values (10,1,1);
77 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `p_cola_fk` FOREIGN KEY (`p_cola`) REFERENCES `a` (`p_cola`))
78 delete from a;
79 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`b`, CONSTRAINT `c_cola_fk` FOREIGN KEY (`c_cola`) REFERENCES `a` (`cola`))
80 select * from a;
81 cola v_cola p_cola
82 12 2 2
83 select * from b;
84 cola v_cola p_cola c_cola
85 10 2 2 12
86 drop table b, a;