Debian Codegraph @

mysql-test/suite/innodb/t/stored_fk.test

raw
1 --source include/have_innodb.inc
2
3 create table t1(f1 int primary key) engine=innodb;
4
5 --echo # Create statement with FK on base column of stored column
6 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
7 create table t2(f1 int not null, f2 int as (f1) stored,
8 foreign key(f1) references t1(f1) on update cascade)engine=innodb;
9
10 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
11 create table t2(f1 int not null, f2 int as (f1) virtual, f3 int as (f2) stored,
12 foreign key(f1) references t1(f1) on update cascade)engine=innodb;
13
14 --echo # adding new stored column during alter table copy operation.
15 create table t2(f1 int not null, f2 int as (f1) virtual,
16 foreign key(f1) references t1(f1) on update cascade)engine=innodb;
17
18 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
19 alter table t2 add column f3 int as (f1) stored, add column f4 int as (f1) virtual;
20 show create table t2;
21 drop table t2;
22
23 --echo # adding foreign key constraint for base columns during alter copy.
24 create table t2(f1 int not null, f2 int as (f1) stored) engine=innodb;
25 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
26 alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=copy;
27 show create table t2;
28 drop table t2;
29
30 --echo # adding foreign key constraint for base columns during online alter.
31 create table t2(f1 int not null, f2 int as (f1) stored) engine=innodb;
32 set foreign_key_checks = 0;
33 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
34 alter table t2 add foreign key(f1) references t1(f1) on update cascade, algorithm=inplace;
35 drop table t2;
36
37 --echo # adding stored column via online alter.
38 create table t2(f1 int not null,
39 foreign key(f1) references t1(f1) on update cascade)engine=innodb;
40 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
41 alter table t2 add column f2 int as (f1) stored, algorithm=inplace;
42 drop table t2, t1;
43
44 --echo #
45 --echo # BUG#26731689 FK ON TABLE WITH GENERATED COLS: ASSERTION POS < N_DEF
46 --echo #
47
48 CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED, c INT,
49 d INT GENERATED ALWAYS AS (0) VIRTUAL, e INT) ENGINE=innodb;
50
51 CREATE TABLE t (a INT) ENGINE=innodb;
52
53 # This would fail. No corresponding index
54 --error ER_FK_NO_INDEX_PARENT
55 ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (e) REFERENCES t(a) ON UPDATE SET null;
56
57 ALTER TABLE t ADD PRIMARY KEY(a);
58
59 ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (e) REFERENCES t(a) ON UPDATE SET null;
60
61 DROP TABLE s,t;
62
63 CREATE TABLE s (a INT GENERATED ALWAYS AS (0) VIRTUAL,
64 b INT GENERATED ALWAYS AS (0) STORED, c INT) ENGINE=innodb;
65
66 CREATE TABLE t (a INT) ENGINE=innodb;
67
68 # This would fail. No corresponding index
69 --error ER_FK_NO_INDEX_PARENT
70 ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES t(a) ON UPDATE SET null;
71
72 ALTER TABLE t ADD PRIMARY KEY(a);
73
74 ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (c) REFERENCES t(a) ON UPDATE SET null;
75
76 DROP TABLE s,t;
77
78 CREATE TABLE s (a INT, b INT GENERATED ALWAYS AS (0) STORED) ENGINE=innodb;
79
80 CREATE TABLE t (a INT PRIMARY KEY) ENGINE=innodb;
81
82 ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null;
83
84 DROP TABLE s,t;
85
86 CREATE TABLE s (a INT, b INT) ENGINE=innodb;
87
88 CREATE TABLE t (a INT) ENGINE=innodb;
89
90 # This would fail. No corresponding index
91 --error ER_FK_NO_INDEX_PARENT
92 ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null;
93
94 ALTER TABLE t ADD PRIMARY KEY(a);
95
96 ALTER TABLE s ADD CONSTRAINT c FOREIGN KEY (a) REFERENCES t(a) ON UPDATE SET null;
97
98 DROP TABLE s,t;