Debian Codegraph @

mysql-test/suite/vcol/r/innodb_virtual_fk.result

raw
1 set default_storage_engine=innodb;
2 create table t1 (id int primary key, id2 int as (id) virtual, key id2 (id2));
3 create table t2 (id int key, constraint fk_id foreign key (id) references t1 (id) on delete cascade);
4 insert into t1 (id) values (1), (2);
5 insert into t2 (id) values (1), (2);
6 delete from t1;
7 select * from t1;
8 id id2
9 select * from t2;
10 id
11 drop table t2;
12 drop table t1;
13 #
14 # End of 10.2 tests
15 #
16 #
17 # MDEV-18114 Foreign Key Constraint actions don't affect Virtual Column
18 #
19 create table t1 (id int primary key);
20 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on update restrict);
21 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on update no action);
22 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on delete cascade);
23 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on update cascade);
24 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on delete set null);
25 create or replace table t2 (id int, id2 int as (id) virtual, key(id2), foreign key (id) references t1 (id) on update set null);
26 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on update restrict);
27 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on update no action);
28 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on delete cascade);
29 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on update cascade);
30 ERROR HY000: Function or expression 'id' cannot be used in the GENERATED ALWAYS AS clause of `id2`
31 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on delete set null);
32 ERROR HY000: Function or expression 'id' cannot be used in the GENERATED ALWAYS AS clause of `id2`
33 create or replace table t2 (id int, id2 int as (id) stored, foreign key (id) references t1 (id) on update set null);
34 ERROR HY000: Function or expression 'id' cannot be used in the GENERATED ALWAYS AS clause of `id2`
35 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on update restrict);
36 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on update no action);
37 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on delete cascade);
38 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on update cascade);
39 ERROR HY000: Function or expression 'id2' cannot be used in the GENERATED ALWAYS AS clause of `id3`
40 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on delete set null);
41 ERROR HY000: Function or expression 'id2' cannot be used in the GENERATED ALWAYS AS clause of `id3`
42 create or replace table t2 (id int, id2 int as (id) virtual, id3 int as (id2) stored, foreign key (id) references t1 (id) on update set null);
43 ERROR HY000: Function or expression 'id2' cannot be used in the GENERATED ALWAYS AS clause of `id3`
44 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on update restrict);
45 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on update no action);
46 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on delete cascade);
47 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on update cascade);
48 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on delete set null);
49 create or replace table t2 (id int, id2 int default (id), foreign key (id) references t1 (id) on update set null);
50 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on update restrict);
51 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on update no action);
52 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on delete cascade);
53 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on update cascade);
54 ERROR HY000: Function or expression 'id' cannot be used in the CHECK clause of `id2`
55 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on delete set null);
56 ERROR HY000: Function or expression 'id' cannot be used in the CHECK clause of `id2`
57 create or replace table t2 (id int, id2 int check (id), foreign key (id) references t1 (id) on update set null);
58 ERROR HY000: Function or expression 'id' cannot be used in the CHECK clause of `id2`
59 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on update restrict);
60 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on update no action);
61 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on delete cascade);
62 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on update cascade);
63 ERROR HY000: Function or expression 'id' cannot be used in the CHECK clause of `CONSTRAINT_1`
64 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on delete set null);
65 ERROR HY000: Function or expression 'id' cannot be used in the CHECK clause of `CONSTRAINT_1`
66 create or replace table t2 (id int, id2 int, check (id), foreign key (id) references t1 (id) on update set null);
67 ERROR HY000: Function or expression 'id' cannot be used in the CHECK clause of `CONSTRAINT_1`
68 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on update restrict);
69 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on update no action);
70 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on delete cascade);
71 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on update cascade);
72 ERROR HY000: Function or expression 'id2' cannot be used in the CHECK clause of `CONSTRAINT_1`
73 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on delete set null);
74 ERROR HY000: Function or expression 'id2' cannot be used in the CHECK clause of `CONSTRAINT_1`
75 create or replace table t2 (id int, id2 int as (id) virtual, check (id2), foreign key (id) references t1 (id) on update set null);
76 ERROR HY000: Function or expression 'id2' cannot be used in the CHECK clause of `CONSTRAINT_1`
77 drop table if exists t2, t1;
78 Warnings:
79 Note 1051 Unknown table 'test.t2'
80 #
81 # MDEV-31853 Assertion failure in Column_definition::check_vcol_for_key upon adding FK
82 #
83 create table t (a int, b int, c int generated always as (a), key(b), key(c));
84 alter table t add foreign key (a) references t (b) on update set null, algorithm=nocopy;
85 ERROR 0A000: ALGORITHM=NOCOPY is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
86 alter table t add foreign key (a) references t (b);
87 show create table t;
88 Table Create Table
89 t CREATE TABLE `t` (
90 `a` int(11) DEFAULT NULL,
91 `b` int(11) DEFAULT NULL,
92 `c` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL,
93 KEY `b` (`b`),
94 KEY `c` (`c`),
95 KEY `a` (`a`),
96 CONSTRAINT `t_ibfk_1` FOREIGN KEY (`a`) REFERENCES `t` (`b`)
97 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
98 drop table t;
99 #
100 # End of 10.5 tests
101 #