Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_db.test

raw
1 -- source include/have_ndb.inc
2
3 create database mydb0;
4 use mydb0;
5
6 create table parent (
7 a int primary key,
8 b int not null,
9 c int not null,
10 unique(b) using hash,
11 index(c)) engine = ndb;
12
13 create database mydb1;
14 use mydb1;
15
16 create table child (
17 a int primary key,
18 b int not null,
19 c int not null,
20 constraint fk1 foreign key(a) references mydb0.parent (a),
21 unique(b) using hash,
22 index(c)) engine = ndb;
23
24 --disable_warnings
25 show create table child;
26 --enable_warnings
27
28 alter table child algorithm=copy, add constraint fk2 foreign key (b)
29 references mydb0.parent(a);
30
31 --disable_warnings
32 show create table child;
33 --enable_warnings
34
35 alter table child algorithm=inplace, add constraint fk3 foreign key (c)
36 references mydb0.parent(a);
37
38 --disable_warnings
39 show create table child;
40 --enable_warnings
41
42 alter table child algorithm=copy, drop foreign key fk2;
43
44 --disable_warnings
45 show create table child;
46 --enable_warnings
47
48 alter table child algorithm=inplace, drop foreign key fk3;
49
50 --disable_warnings
51 show create table child;
52 --enable_warnings
53
54 alter table mydb0.parent algorithm=copy, rename mydb1.parent;
55
56 --disable_warnings
57 show create table child;
58 --enable_warnings
59
60 alter table mydb1.parent algorithm=inplace, rename mydb0.parent;
61
62 --disable_warnings
63 show create table child;
64 --enable_warnings
65
66 use test;
67 alter table mydb1.child algorithm=copy, add constraint fk2 foreign key (b)
68 references mydb0.parent(a);
69 use mydb1;
70 --disable_warnings
71 show create table child;
72 --enable_warnings
73
74 use test;
75 alter table mydb1.child algorithm=inplace, add constraint fk3 foreign key (c)
76 references mydb0.parent(a);
77 use mydb1;
78 --disable_warnings
79 show create table child;
80 --enable_warnings
81
82 # bug#16286164: the fix requires correct DROP order
83 use test;
84 drop database mydb1;
85 drop database mydb0;
86
87 #
88 #
89 #
90 let $i=2;
91 while ($i)
92 {
93 eval set @i=$i;
94 let $engine=`select if(@i=1,'ndb','innodb')`;
95 create database mydb0;
96 use mydb0;
97 eval create table t0 (a int primary key) engine = $engine;
98 eval create table t1 (a int primary key) engine = $engine;
99
100 alter table mydb0.t0 add constraint fk0 foreign key (a)
101 references mydb0.t1(a);
102
103 alter table mydb0.t1 add constraint fk1 foreign key (a)
104 references mydb0.t0(a);
105
106 drop database mydb0;
107
108 dec $i;
109 }
110
111 let $i=2;
112 while ($i)
113 {
114 eval set @i=$i;
115 let $engine=`select if(@i=1,'ndb','innodb')`;
116 create database mydb0;
117 use mydb0;
118 eval create table t0 (a int primary key) engine = $engine;
119
120 create database mydb1;
121 use mydb1;
122 eval create table t1 (a int primary key) engine = $engine;
123
124 alter table mydb0.t0 add constraint fk0 foreign key (a)
125 references mydb1.t1(a);
126
127 alter table mydb1.t1 add constraint fk1 foreign key (a)
128 references mydb0.t0(a);
129
130 --error ER_FK_CANNOT_DROP_PARENT
131 drop database mydb0;
132 --error ER_FK_CANNOT_DROP_PARENT
133 drop database mydb1;
134
135 alter table mydb0.t0 drop foreign key fk0;
136
137 --error ER_FK_CANNOT_DROP_PARENT
138 drop database mydb0;
139
140 # t1 point into mydb0, but this is ok
141 drop database mydb1;
142 drop database mydb0;
143
144 dec $i;
145 }