Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2
3 ###
4 ### PK vs PK
5 ###
6 create table parent (
7 a int primary key auto_increment,
8 b int not null,
9 c int not null,
10 unique(b) using hash,
11 index(c)) engine = ndb;
12
13 create table child (
14 a int primary key auto_increment,
15 b int not null,
16 c int not null,
17 unique(b) using hash,
18 index(c)) engine = ndb;
19
20 create table blobchild (
21 a int primary key auto_increment,
22 b int not null,
23 c int not null,
24 d text,
25 unique(b) using hash,
26 index(c)) engine = ndb;
27
28 --error 1215
29 alter table blobchild add constraint fkbad foreign key(a) references parent (a) on delete cascade on update restrict;
30 show warnings;
31
32 alter table child add constraint fkname foreign key (a) references parent (a) on delete cascade on update restrict;
33
34 insert into parent values (1,1,1),(2,2,2);
35 insert into child values (1,1,1),(2,2,2);
36
37 --error 1452
38 insert into child values (3,3,3);
39
40 begin;
41 delete from parent where a = 1;
42 select * from child order by 1,2,3;
43 commit;
44
45 delete from child;
46 delete from parent;
47
48 alter table child drop foreign key fkname;
49
50 ###
51 ### PK vs UK
52 ###
53 --error 1215
54 alter table blobchild add constraint fkbad foreign key(b) references parent (a) on delete cascade on update restrict;
55 show warnings;
56
57 alter table child add constraint fkname foreign key (b) references parent (a) on delete cascade on update restrict;
58
59 insert into parent values (1,1,1),(2,2,2);
60 insert into child values (1,1,1),(2,2,2);
61
62 --error 1452
63 insert into child values (3,3,3);
64
65 --error 1452
66 update child set b = 3 where a = 1;
67
68 begin;
69 delete from parent where a = 1;
70 select * from child order by 1,2,3;
71 commit;
72
73 delete from child;
74 delete from parent;
75
76 alter table child drop foreign key fkname;
77
78 ###
79 ### UK vs PK
80 ###
81 --error 1215
82 alter table blobchild add constraint fkbad foreign key(a) references parent (b) on delete cascade on update restrict;
83 show warnings;
84
85 alter table child add constraint fkname foreign key (a) references parent (b) on delete cascade on update restrict;
86
87 insert into parent values (1,1,1),(2,2,2);
88 insert into child values (1,1,1),(2,2,2);
89
90 --error 1452
91 insert into child values (3,3,3);
92
93 --error 1451
94 update parent set b = 3 where a = 1;
95
96 begin;
97 delete from parent where a = 1;
98 select * from child order by 1,2,3;
99 commit;
100
101 delete from child;
102 delete from parent;
103
104 alter table child drop foreign key fkname;
105
106 ###
107 ### UK vs UK
108 ###
109 --error 1215
110 alter table blobchild add constraint fkbad foreign key(b) references parent (b) on delete cascade on update restrict;
111 show warnings;
112
113 alter table child add constraint fkname foreign key (b) references parent (b) on delete cascade on update restrict;
114
115 insert into parent values (1,1,1),(2,2,2);
116 insert into child values (1,1,1),(2,2,2);
117
118 --error 1452
119 update child set b = 3 where a = 1;
120
121 --error 1452
122 insert into child values (3,3,3);
123
124 --error 1451
125 update parent set b = 3 where a = 1;
126
127 begin;
128 delete from parent where a = 1;
129 select * from child order by 1,2,3;
130 commit;
131
132 delete from child;
133 delete from parent;
134
135 alter table child drop foreign key fkname;
136
137 ###
138 ### PK vs OI
139 ###
140 --error 1215
141 alter table blobchild add constraint fkbad foreign key(c) references parent (a) on delete cascade on update restrict;
142 show warnings;
143
144 alter table child add constraint fkname foreign key (c) references parent (a) on delete cascade on update restrict;
145
146 insert into parent values (1,1,1),(2,2,2);
147 insert into child values (1,1,1),(2,2,2);
148
149 --error 1452
150 update child set c = 3 where a = 1;
151
152 --error 1452
153 insert into child values (3,3,3);
154
155 begin;
156 delete from parent where a = 1;
157 select * from child order by 1,2,3;
158 commit;
159
160 delete from child;
161 delete from parent;
162
163 alter table child drop foreign key fkname;
164
165 ###
166 ### UK vs OI
167 ###
168 --error 1215
169 alter table blobchild add constraint fkbad foreign key(c) references parent (b) on delete cascade on update restrict;
170 show warnings;
171
172 alter table child add constraint fkname foreign key (c) references parent (b) on delete cascade on update restrict;
173
174 insert into parent values (1,1,1),(2,2,2);
175 insert into child values (1,1,1),(2,2,2);
176
177 --error 1452
178 update child set c = 3 where a = 1;
179
180 --error 1452
181 insert into child values (3,3,3);
182
183 --error 1451
184 update parent set b = 3 where a = 1;
185
186 begin;
187 delete from parent where a = 1;
188 select * from child order by 1,2,3;
189 commit;
190
191 ###
192 #
193 # test with a few more rows
194 insert into parent values (1,1,1);
195 insert into child values (1,1,1);
196 insert into child select a+2,a+2,1+(a % 2) from child;
197 insert into child select a+4,a+4,1+(a % 2) from child;
198 insert into child select a+8,a+8,1+(a % 2) from child;
199 insert into child select a+16,a+16,1+(a % 2) from child;
200 insert into child select a+32,a+32,1+(a % 2) from child;
201 insert into child select a+64,a+64,1+(a % 2) from child;
202 select c, count(*) from child
203 group by c
204 order by 1,2;
205 delete from parent where a = 1;
206 select c, count(*) from child
207 group by c
208 order by 1,2;
209 delete from parent where a = 2;
210
211 delete from child;
212 delete from parent;
213
214 alter table child drop foreign key fkname;
215
216 drop table parent, child, blobchild;