Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2
3 connect (con1,localhost,root,,test);
4 connect (con2,localhost,root,,test);
5
6 ###
7 ### PK vs PK
8 ###
9 create table parent (
10 a int primary key auto_increment,
11 b int not null,
12 c int not null,
13 unique(b) using hash,
14 index(c)) engine = ndb;
15
16 create table child (
17 a int primary key auto_increment,
18 b int not null,
19 c int not null,
20 unique(b) using hash,
21 index(c)) engine = ndb;
22
23 alter table child add constraint fkname foreign key (a) references parent(a) on delete restrict on update restrict;
24
25 insert into parent values (1,1,1);
26 insert into child values (1,1,1);
27
28 --error 1452
29 insert into child values (2,2,2);
30
31 begin;
32 --error 1451
33 delete from parent;
34 rollback;
35
36 delete from child;
37 delete from parent;
38
39 begin;
40 --error 1452
41 insert into child values (2,2,2);
42 rollback;
43
44 delete from child;
45 delete from parent;
46
47 insert into parent values (1,1,1);
48
49 connection con1;
50 begin;
51 insert into child values (1,1,1);
52
53 connection con2;
54 begin;
55 --error ER_LOCK_WAIT_TIMEOUT
56 delete from parent;
57
58 connection con1;
59 commit;
60
61 connection con2;
62 commit;
63
64 delete from child;
65 delete from parent;
66
67 alter table child drop foreign key fkname;
68
69 ###
70 ### PK vs UK
71 ###
72 alter table child add constraint fkname foreign key (b) references parent(a) on delete restrict on update restrict;
73
74 insert into parent values (1,1,1);
75 insert into child values (1,1,1);
76
77 --error 1452
78 insert into child values (2,2,2);
79
80 --error 1452
81 update child set b = 2 where a = 1;
82
83 begin;
84 --error 1451
85 delete from parent;
86 rollback;
87
88 delete from child;
89 delete from parent;
90
91 begin;
92 --error 1452
93 insert into child values (2,2,2);
94 rollback;
95
96 delete from child;
97 delete from parent;
98
99 insert into parent values (1,1,1);
100
101 connection con1;
102 begin;
103 insert into child values (1,1,1);
104
105 connection con2;
106 begin;
107 --error ER_LOCK_WAIT_TIMEOUT
108 delete from parent;
109
110 connection con1;
111 commit;
112
113 connection con2;
114 commit;
115
116 delete from child;
117 delete from parent;
118
119 alter table child drop foreign key fkname;
120
121 ###
122 ### UK vs PK
123 ###
124 alter table child add constraint fkname foreign key (a) references parent(b) on delete restrict on update restrict;
125
126 insert into parent values (1,1,1);
127 insert into child values (1,1,1);
128
129 --error 1452
130 insert into child values (2,2,2);
131
132 --error 1451
133 update parent set b = 2 where a = 1;
134
135 begin;
136 --error 1451
137 delete from parent;
138 rollback;
139
140 delete from child;
141 delete from parent;
142
143 begin;
144 --error 1452
145 insert into child values (2,2,2);
146 rollback;
147
148 delete from child;
149 delete from parent;
150
151 insert into parent values (1,1,1);
152
153 connection con1;
154 begin;
155 insert into child values (1,1,1);
156
157 connection con2;
158 begin;
159 --error ER_LOCK_WAIT_TIMEOUT
160 delete from parent;
161
162 connection con1;
163 commit;
164
165 connection con2;
166 commit;
167
168 delete from child;
169 delete from parent;
170
171 alter table child drop foreign key fkname;
172
173 ###
174 ### UK vs UK
175 ###
176 alter table child add constraint fkname foreign key (b) references parent(b) on delete restrict on update restrict;
177
178 insert into parent values (1,1,1);
179 insert into child values (1,1,1);
180
181 --error 1452
182 update child set b = 2 where a = 1;
183
184 --error 1452
185 insert into child values (2,2,2);
186
187 --error 1451
188 update parent set b = 2 where a = 1;
189
190 begin;
191 --error 1451
192 delete from parent;
193 rollback;
194
195 delete from child;
196 delete from parent;
197
198 begin;
199 --error 1452
200 insert into child values (2,2,2);
201 rollback;
202
203 delete from child;
204 delete from parent;
205
206 insert into parent values (1,1,1);
207
208 connection con1;
209 begin;
210 insert into child values (1,1,1);
211
212 connection con2;
213 begin;
214 --error ER_LOCK_WAIT_TIMEOUT
215 delete from parent;
216
217 connection con1;
218 commit;
219
220 connection con2;
221 commit;
222
223 delete from child;
224 delete from parent;
225
226 alter table child drop foreign key fkname;
227
228 ###
229 ### PK vs OI
230 ###
231 alter table child add constraint fkname foreign key (c) references parent(a) on delete restrict on update restrict;
232
233 insert into parent values (1,1,1);
234 insert into child values (1,1,1);
235
236 --error 1452
237 update child set c = 2 where a = 1;
238
239 --error 1452
240 insert into child values (2,2,2);
241
242 begin;
243 --error 1451
244 delete from parent;
245 rollback;
246
247 delete from child;
248 delete from parent;
249
250 begin;
251 --error 1452
252 insert into child values (2,2,2);
253 rollback;
254
255 delete from child;
256 delete from parent;
257
258 insert into parent values (1,1,1);
259
260 connection con1;
261 begin;
262 insert into child values (1,1,1);
263
264 connection con2;
265 begin;
266 --error ER_LOCK_WAIT_TIMEOUT
267 delete from parent;
268
269 connection con1;
270 commit;
271
272 connection con2;
273 commit;
274
275 delete from child;
276 delete from parent;
277
278 alter table child drop foreign key fkname;
279
280 ###
281 ### UK vs OI
282 ###
283 alter table child add constraint fkname foreign key (c) references parent(b) on delete restrict on update restrict;
284
285 insert into parent values (1,1,1);
286 insert into child values (1,1,1);
287
288 --error 1452
289 update child set c = 2 where a = 1;
290
291 --error 1452
292 insert into child values (2,2,2);
293
294 --error 1451
295 update parent set b = 2 where a = 1;
296
297 begin;
298 --error 1451
299 delete from parent;
300 rollback;
301
302 delete from child;
303 delete from parent;
304
305 begin;
306 --error 1452
307 insert into child values (2,2,2);
308 rollback;
309
310 delete from child;
311 delete from parent;
312
313 insert into parent values (1,1,1);
314
315 connection con1;
316 begin;
317 insert into child values (1,1,1);
318
319 connection con2;
320 begin;
321 --error ER_LOCK_WAIT_TIMEOUT
322 delete from parent;
323
324 connection con1;
325 commit;
326
327 connection con2;
328 commit;
329
330 delete from child;
331 delete from parent;
332
333 alter table child drop foreign key fkname;
334
335 drop table parent, child;