Debian Codegraph @

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