Debian Codegraph @

mysql-test/suite/ndb/r/ndb_fk_no_fk.result

raw
1 #
2 # Create child tables before parents
3 # by turning off foreign_key_checks for this session
4 #
5 select @@foreign_key_checks;
6 @@foreign_key_checks
7 1
8 set @@foreign_key_checks=0;
9 create table t1 (
10 pk int not null primary key,
11 b int,
12 foreign key (b) references t2(pk1)
13 ) engine=ndb;
14 create table t3 (
15 pk int not null primary key,
16 b int, c int,
17 d int, e int, f int,
18 g int, h int, i int, j int,
19 foreign key (b, c) references t4(pk1, pk2),
20 foreign key (d, e, f) references t5(uk1, uk2, uk3),
21 foreign key (g, h, i, j) references t6(oi1, oi2, oi3, oi4)
22 ) engine=ndb;
23 # There should be 4 mock tables created
24 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
25 count(*)
26 4
27 # There should be 1 mock table created for t1
28 select type, name from ndb_show_tables_results
29 where name like "'NDB$FKM_<id>%'";
30 type name
31 UserTable 'NDB$FKM_<id>_0_t2'
32 # There should be 3 mock tables created for t3
33 select type, name from ndb_show_tables_results
34 where name like "'NDB$FKM_<id>%'" order by name;
35 type name
36 UserTable 'NDB$FKM_<id>_0_t4'
37 UserTable 'NDB$FKM_<id>_1_t5'
38 UserTable 'NDB$FKM_<id>_2_t6'
39 drop table t3;
40 # There should be 1 mock table
41 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
42 count(*)
43 1
44 # Only t1 should have 1 mock table
45 select type, name from ndb_show_tables_results
46 where name like "'NDB$FKM_<id>%'";
47 type name
48 UserTable 'NDB$FKM_<id>_0_t2'
49 drop table t1;
50 # There should be 0 mock tables
51 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
52 count(*)
53 0
54 #
55 # Create child table before parent
56 # by turning off foreign_key_checks for this session
57 #
58 create table t1 (
59 pk int not null primary key,
60 b int,
61 foreign key fk1(b) references t2(pk1)
62 ) engine=ndb;
63 create table t2 (
64 pk1 int not null primary key,
65 c int
66 ) engine=ndb;
67 # There should be 0 mock tables
68 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
69 count(*)
70 0
71 # Verify that fk is working now when parent has been resolved
72 set foreign_key_checks=1;
73 insert into t1 values(1, 2);
74 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t1`, CONSTRAINT `t1_fk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`pk1`) ON DELETE NO ACTION ON UPDATE NO ACTION)
75 set foreign_key_checks=0;
76 drop table t1, t2;
77 #
78 # Exceed table name when creating the mock table by referencing
79 # a not yet created table with really long name
80 #
81 # 1) By blowing the mock_tabname buffer(currently 512 bytes)
82 create table t1 (
83 pk int not null primary key,
84 b int,
85 foreign key (b) references abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_(pk1)
86 ) engine=ndb;
87 ERROR 42000: Incorrect table name 'abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuv'
88 show warnings;
89 Level Code Message
90 Error 1103 Incorrect table name 'abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuvxyz_abcdefghijklmnopqrstuv'
91 #
92 # SHOW CREATE TABLE for table with mock table references
93 #
94 # In the past SHOW CREATE TABLE has shown mock table names
95 # in @@ndb_show_foreign_key_mock_tables=1 mode. However,
96 # after move of related code to SQL-layer, this is no
97 # longer supported.
98 create table t1 (
99 a int not null,
100 b int not null,
101 c int not null,
102 primary key (a),
103 unique key (b) using hash,
104 key (c),
105 constraint fk1 foreign key(a) references t2 (a),
106 constraint fk2 foreign key(b) references t3 (a),
107 constraint fk3 foreign key(c) references t4 (a),
108 constraint fk4 foreign key(a) references t2 (b),
109 constraint fk5 foreign key(b) references t3 (b),
110 constraint fk6 foreign key(c) references t4 (b)
111 ) engine=ndbcluster;
112 show create table t1;
113 Table Create Table
114 t1 CREATE TABLE `t1` (
115 `a` int NOT NULL,
116 `b` int NOT NULL,
117 `c` int NOT NULL,
118 PRIMARY KEY (`a`),
119 UNIQUE KEY `b` (`b`) USING HASH,
120 KEY `c` (`c`),
121 CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`),
122 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t3` (`a`),
123 CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `t4` (`a`),
124 CONSTRAINT `fk4` FOREIGN KEY (`a`) REFERENCES `t2` (`b`),
125 CONSTRAINT `fk5` FOREIGN KEY (`b`) REFERENCES `t3` (`b`),
126 CONSTRAINT `fk6` FOREIGN KEY (`c`) REFERENCES `t4` (`b`)
127 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
128 create table t2 (
129 a int primary key,
130 b int not null,
131 c int not null,
132 unique(b) using hash,
133 index(c)
134 ) engine = ndb;
135 show create table t1;
136 Table Create Table
137 t1 CREATE TABLE `t1` (
138 `a` int NOT NULL,
139 `b` int NOT NULL,
140 `c` int NOT NULL,
141 PRIMARY KEY (`a`),
142 UNIQUE KEY `b` (`b`) USING HASH,
143 KEY `c` (`c`),
144 CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`),
145 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t3` (`a`),
146 CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `t4` (`a`),
147 CONSTRAINT `fk4` FOREIGN KEY (`a`) REFERENCES `t2` (`b`),
148 CONSTRAINT `fk5` FOREIGN KEY (`b`) REFERENCES `t3` (`b`),
149 CONSTRAINT `fk6` FOREIGN KEY (`c`) REFERENCES `t4` (`b`)
150 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
151 set @@ndb_show_foreign_key_mock_tables= 1;
152 show create table t1;
153 Table Create Table
154 t1 CREATE TABLE `t1` (
155 `a` int NOT NULL,
156 `b` int NOT NULL,
157 `c` int NOT NULL,
158 PRIMARY KEY (`a`),
159 UNIQUE KEY `b` (`b`) USING HASH,
160 KEY `c` (`c`),
161 CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t2` (`a`),
162 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t3` (`a`),
163 CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `t4` (`a`),
164 CONSTRAINT `fk4` FOREIGN KEY (`a`) REFERENCES `t2` (`b`),
165 CONSTRAINT `fk5` FOREIGN KEY (`b`) REFERENCES `t3` (`b`),
166 CONSTRAINT `fk6` FOREIGN KEY (`c`) REFERENCES `t4` (`b`)
167 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
168 set @@ndb_show_foreign_key_mock_tables= 0;
169 drop table t1, t2;
170 #
171 # Create child table and then the parent
172 # which does not match the fk
173 #
174 create table t1 (
175 pk int not null primary key,
176 b int,
177 foreign key (b) references t2(pk1)
178 ) engine=ndb;
179 create table t2 (
180 pk2 int not null primary key,
181 c int
182 ) engine=ndb;
183 ERROR HY000: Failed to add the foreign key constraint. Missing column 'pk1' for constraint 't1_fk_1' in the referenced table 't2'
184 # There should be 1 mock table still
185 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
186 count(*)
187 1
188 drop table t1;
189 #
190 # Create child table and then the parent
191 # which does not match any index
192 #
193 create table t1 (
194 pk int not null primary key,
195 b int,
196 foreign key (b) references t2(c)
197 ) engine=ndb;
198 create table t2 (
199 pk1 int not null primary key,
200 c int
201 ) engine=ndb;
202 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 't1_fk_1' in the referenced table 't2'
203 # There should be 1 mock table still
204 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
205 count(*)
206 1
207 drop table t1;
208 set @@ndb_show_foreign_key_mock_tables= 1;
209 #
210 # Test where FK goes from t1->t2->t3->t4 and tables are created in that order
211 create table t1 (
212 pk11 int not null primary key,
213 b int,
214 foreign key (b) references t2(pk21)
215 ) engine=ndb;
216 Warnings:
217 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
218 create table t2 (
219 pk21 int not null primary key,
220 b int, c int,
221 foreign key (b, c) references t3(pk31, pk32)
222 ) engine=ndb;
223 Warnings:
224 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t3' referenced by 't2'
225 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
226 create table t3 (
227 pk31 int not null,
228 pk32 int not null,
229 primary key(pk31, pk32),
230 d int, e int,f int,
231 foreign key (d,e,f) references t4(pk41, pk42, pk43)
232 ) engine=ndb;
233 Warnings:
234 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t4' referenced by 't3'
235 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t3' - resolved by 't3'
236 create table t4 (
237 pk41 int not null,
238 pk42 int not null,
239 pk43 int not null,
240 primary key(pk41, pk42, pk43),
241 m varchar(55)
242 ) engine=ndb;
243 Warnings:
244 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t4' - resolved by 't4'
245 # There should be no mock tables
246 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
247 count(*)
248 0
249 drop table t1, t2, t3, t4;
250 #
251 # Test where FK 'fans out' t1->t2 t1->t3 t1->t4, create t1 first
252 create table t1 (
253 pk11 int not null primary key,
254 b int,
255 foreign key (b) references t2(c),
256 d int, e int,
257 foreign key (d, e) references t3(f, g),
258 h varchar(55), i int, j datetime,
259 foreign key (h, i, j) references t4(m, l, k)
260 ) engine=ndb;
261 Warnings:
262 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
263 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t3' referenced by 't1'
264 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t4' referenced by 't1'
265 create table t2 (
266 pk21 int not null primary key,
267 c int,
268 unique(c)
269 ) engine=ndb;
270 Warnings:
271 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
272 create table t3 (
273 pk31 int not null,
274 pk32 int not null,
275 primary key(pk31, pk32),
276 g int, f int,
277 unique(f, g)
278 ) engine=ndb;
279 Warnings:
280 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t3' - resolved by 't3'
281 create table t4 (
282 pk41 int not null,
283 pk42 int not null,
284 pk43 int not null,
285 primary key(pk41, pk42, pk43),
286 m varchar(55), l int, k datetime,
287 unique(k, l, m)
288 ) engine=ndb;
289 Warnings:
290 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t4' - resolved by 't4'
291 # There should be no mock tables
292 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
293 count(*)
294 0
295 drop table t1, t2, t3, t4;
296 #
297 # Test where FK refers to 2+ different indices in parent t1->t2(1) t1->t2(2)
298 create table t1 (
299 pk11 int not null primary key,
300 foreign key (pk11) references t2(pk21),
301 b int,
302 foreign key (b) references t2(c),
303 d int, e int,
304 foreign key (d, e) references t2(f, g),
305 h varchar(55), i int, j datetime,
306 foreign key (h, i, j) references t2(m, l, k)
307 ) engine=ndb;
308 Warnings:
309 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
310 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
311 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
312 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
313 create table t2 (
314 pk21 int not null primary key,
315 c int,
316 unique(c),
317 g int, f int,
318 unique(f, g),
319 m varchar(55), l int, k datetime,
320 unique(k, l, m)
321 ) engine=ndb;
322 Warnings:
323 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
324 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
325 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
326 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
327 # There should be no mock tables
328 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
329 count(*)
330 0
331 drop table t1, t2;
332 #
333 # Test drop of referenced table(mock table should be created)
334 #
335 create table t1 (
336 pk1 int not null primary key,
337 foreign key (pk1) references t2(pk2),
338 a varchar(255)
339 ) engine=ndb;
340 Warnings:
341 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
342 create table t2 (
343 pk2 int not null primary key,
344 b datetime
345 ) engine=ndb;
346 Warnings:
347 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
348 # There should be no mock tables
349 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
350 count(*)
351 0
352 drop table t2;
353 Warnings:
354 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
355 # There should be 1 mock table created for t1
356 select type, name from ndb_show_tables_results
357 where name like "'NDB$FKM_<id>%'";
358 type name
359 UserTable 'NDB$FKM_<id>_0_t2'
360 drop table t1;
361 Warnings:
362 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - referencing table dropped
363 # There should be no mock tables
364 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
365 count(*)
366 0
367 set @@ndb_show_foreign_key_mock_tables= 0;
368 #
369 # Test mock tables in different databases, mock table
370 # should be created in same database as the real parent would.
371 #
372 set @@ndb_show_foreign_key_mock_tables= 1;
373 create database mydb0;
374 create database mydb1;
375 create table mydb1.t1 (
376 a int primary key,
377 constraint fk1 foreign key(a) references mydb0.t2(a)
378 ) engine = ndb;
379 Warnings:
380 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
381 # There should be 1 mock table created for t1
382 select type, _database, name from ndb_show_tables_results
383 where name like "'NDB$FKM_<id>%'";
384 type _database name
385 UserTable 'mydb0' 'NDB$FKM_<id>_0_t2'
386 drop table mydb1.t1;
387 Warnings:
388 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - referencing table dropped
389 # There should be no mock tables
390 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
391 count(*)
392 0
393 create table mydb1.t1 (
394 a int primary key,
395 constraint fk1 foreign key(a) references mydb0.t2(a)
396 ) engine = ndb;
397 Warnings:
398 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
399 create table mydb0.t2 (
400 a int primary key
401 ) engine = ndb;
402 Warnings:
403 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
404 # There should be no mock tables
405 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
406 count(*)
407 0
408 # Drop child then parent
409 drop table mydb1.t1;
410 drop table mydb0.t2;
411 create table mydb1.t1 (
412 a int primary key,
413 constraint fk1 foreign key(a) references mydb0.t2(a)
414 ) engine = ndb;
415 Warnings:
416 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
417 create table mydb0.t2 (
418 a int primary key
419 ) engine = ndb;
420 Warnings:
421 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
422 # There should be no mock tables
423 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
424 count(*)
425 0
426 # Drop parent then child(creates mock which is then dropped)
427 drop table mydb0.t2;
428 Warnings:
429 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
430 drop table mydb1.t1;
431 Warnings:
432 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - referencing table dropped
433 # There should be no mock tables
434 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
435 count(*)
436 0
437 #
438 # Create spagetthi foreign keys between tables in three different databases
439 #
440 create table t1 (
441 pk int not null primary key,
442 b int,
443 foreign key (b) references mydb1.t2(pk1),
444 c int,
445 unique(c),
446 j int,
447 foreign key (j) references mydb0.t3(pk2),
448 k int,
449 foreign key (k) references mydb0.t1(pk5),
450 l int,
451 foreign key (l) references mydb1.t1(pk6),
452 y int,
453 foreign key (y) references mydb1.t1(z)
454 ) engine=ndb;
455 Warnings:
456 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t1' referenced by 't1'
457 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t1' referenced by 't1'
458 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t1' referenced by 't1'
459 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t2' referenced by 't1'
460 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t3' referenced by 't1'
461 create table mydb1.t2 (
462 pk1 int not null primary key,
463 d int,
464 foreign key (d) references mydb0.t3(pk2)
465 ) engine=ndb;
466 Warnings:
467 Warning 1003 Created mock table 'NDB$FKM_<child_id>_<id>_t3' referenced by 't2'
468 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
469 create table mydb0.t3 (
470 pk2 int not null primary key,
471 e int,
472 foreign key (e) references test.t1(c)
473 ) engine=ndb;
474 Warnings:
475 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t3' - resolved by 't3'
476 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t3' - resolved by 't3'
477 create table mydb0.t4 (
478 pk3 int not null primary key,
479 f int,
480 foreign key (f) references t3(pk2)
481 ) engine=ndb;
482 create table mydb0.t5 (
483 pk4 int not null primary key,
484 g int,
485 foreign key (g) references test.t1(pk),
486 h int,
487 foreign key (h) references mydb1.t2(pk1),
488 i int,
489 foreign key (i) references mydb0.t3(pk2)
490 ) engine=ndb;
491 create table mydb0.t1 (
492 pk5 int not null primary key,
493 m int,
494 foreign key (m) references test.t1(pk),
495 n int,
496 foreign key (n) references mydb1.t2(pk1),
497 o int,
498 foreign key (o) references mydb0.t3(pk2)
499 ) engine=ndb;
500 Warnings:
501 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t1' - resolved by 't1'
502 create table mydb1.t1 (
503 pk6 int not null primary key,
504 p int,
505 foreign key (p) references test.t1(pk),
506 q int,
507 foreign key (q) references mydb1.t2(pk1),
508 x int,
509 foreign key (x) references mydb0.t3(pk2),
510 z int,
511 unique(z)
512 ) engine=ndb;
513 Warnings:
514 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t1' - resolved by 't1'
515 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t1' - resolved by 't1'
516 set @@ndb_show_foreign_key_mock_tables= 0;
517 # There should be no mock tables
518 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
519 count(*)
520 0
521 # Drop the database mydb0 with all tables still there
522 drop database mydb0;
523 # There should be 4 mock tables created by dropping mydb0
524 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
525 count(*)
526 4
527 # Drop also database mydb1
528 drop database mydb1;
529 # There should be 5 mock tables all referenced by test.t1
530 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
531 count(*)
532 5
533 # Drop t1 and the 5 remaining mocks
534 drop table t1;
535 # There should be no mock tables
536 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
537 count(*)
538 0
539 #
540 # ALTER a table which references mock parent
541 #
542 create table t1 (
543 pk int not null primary key,
544 b int,
545 foreign key (b) references t2(pk1)
546 ) engine=ndb;
547 # There should be one mock table in place of t2
548 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
549 name
550 'NDB$FKM_<child_id>_<id>_t2'
551 alter table t1 add column c int;
552 Warnings:
553 Warning 1478 Converted FIXED field 'c' to DYNAMIC to enable online ADD COLUMN
554 # There should be one mock table still
555 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
556 name
557 'NDB$FKM_<child_id>_<id>_t2'
558 alter table t1 algorithm = copy, add column d int not null;
559 # There should be one mock table still
560 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
561 name
562 'NDB$FKM_<child_id>_<id>_t2'
563 drop table t1;
564 # There should be no mock tables
565 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
566 count(*)
567 0
568 #
569 # Alter table to add foreign keys referencing non existing parents. One
570 # new mock should be created per fk
571 #
572 create table child (
573 a int primary key auto_increment,
574 b int not null,
575 c int not null,
576 unique(b) using hash,
577 index(c)
578 ) engine = ndb;
579 alter table child algorithm=inplace, add constraint fk1 foreign key (a) references parent(a);
580 alter table child algorithm=inplace, add constraint fk2 foreign key (b) references parent(col1);
581 alter table child algorithm=inplace, add constraint fk3 foreign key (c) references parent(col2);
582 alter table child algorithm=inplace,
583 add constraint fk4 foreign key (c) references parent(col3),
584 add constraint fk5 foreign key (c) references parent(col4),
585 add constraint fk6 foreign key (c) references parent(col5);
586 # There should be 6 mock tables
587 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
588 name
589 'NDB$FKM_<child_id>_<id>_parent'
590 'NDB$FKM_<child_id>_<id>_parent'
591 'NDB$FKM_<child_id>_<id>_parent'
592 'NDB$FKM_<child_id>_<id>_parent'
593 'NDB$FKM_<child_id>_<id>_parent'
594 'NDB$FKM_<child_id>_<id>_parent'
595 alter table child algorithm=inplace, drop foreign key fk1;
596 # There should be 5 mock tables
597 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
598 name
599 'NDB$FKM_<child_id>_<id>_parent'
600 'NDB$FKM_<child_id>_<id>_parent'
601 'NDB$FKM_<child_id>_<id>_parent'
602 'NDB$FKM_<child_id>_<id>_parent'
603 'NDB$FKM_<child_id>_<id>_parent'
604 alter table child algorithm=inplace,
605 drop foreign key fk3,
606 drop foreign key fk4,
607 drop foreign key fk6;
608 # There should be 2 mock tables left
609 select name from ndb_show_tables_results where name like "'NDB\$FKM%'";
610 name
611 'NDB$FKM_<child_id>_<id>_parent'
612 'NDB$FKM_<child_id>_<id>_parent'
613 drop table child;
614 # There should be no mock tables
615 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
616 count(*)
617 0
618 #
619 # Alter table into place(i.e rename) as new parent, should remove
620 # the mock table
621 #
622 create table t1 (
623 pk1 int not null primary key,
624 b int,
625 foreign key (b) references t2(pk2)
626 ) engine=ndb;
627 create table t3 (
628 pk2 int not null primary key,
629 b int
630 ) engine=ndb;
631 # There should be one mock table for t1
632 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
633 count(*)
634 1
635 set @@ndb_show_foreign_key_mock_tables= 1;
636 alter table t3 rename t2;
637 Warnings:
638 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t2' - resolved by 't2'
639 set @@ndb_show_foreign_key_mock_tables= 0;
640 alter table t2 rename t3;
641 show create table t1;
642 Table Create Table
643 t1 CREATE TABLE `t1` (
644 `pk1` int NOT NULL,
645 `b` int DEFAULT NULL,
646 PRIMARY KEY (`pk1`),
647 KEY `b` (`b`),
648 CONSTRAINT `t1_fk_1` FOREIGN KEY (`b`) REFERENCES `t3` (`pk2`)
649 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
650 drop table t3;
651 create table t2 (
652 pk2 int not null primary key,
653 b int
654 ) engine=ndb;
655 # There should be one mock table for t1
656 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
657 count(*)
658 1
659 set @@ndb_show_foreign_key_mock_tables= 1;
660 alter table t2 algorithm=inplace, rename t3;
661 Warnings:
662 Warning 1003 Dropped mock table 'NDB$FKM_<child_id>_<id>_t3' - resolved by 't3'
663 set @@ndb_show_foreign_key_mock_tables= 0;
664 # There should be no mock tables
665 select count(*) from ndb_show_tables_results where name like "'NDB\$FKM%'";
666 count(*)
667 0
668 drop table t3, t1;
669 #
670 # Truncating table which references mock table
671 #
672 create table t1 (
673 pk1 int not null primary key,
674 b int,
675 foreign key fk1 (b) references t2(pk2)
676 ) engine=ndb;
677 set foreign_key_checks=1;
678 truncate table t1;
679 set foreign_key_checks=0;
680 set @@ndb_show_foreign_key_mock_tables= 1;
681 truncate table t1;
682 set @@ndb_show_foreign_key_mock_tables= 0;
683 show create table t1;
684 Table Create Table
685 t1 CREATE TABLE `t1` (
686 `pk1` int NOT NULL,
687 `b` int DEFAULT NULL,
688 PRIMARY KEY (`pk1`),
689 KEY `fk1` (`b`),
690 CONSTRAINT `t1_fk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`pk2`)
691 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
692 drop table t1;
693 #
694 # Truncating referenced table
695 #
696 create table t2 (
697 pk2 int not null primary key,
698 c int
699 ) engine=ndb;
700 create table t1 (
701 pk1 int not null primary key,
702 b int,
703 foreign key (b) references t2(pk2)
704 ) engine=ndb;
705 set foreign_key_checks=1;
706 truncate table t2;
707 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t1`, CONSTRAINT `t1_fk_1`)
708 set foreign_key_checks=0;
709 Foreign keys referenced by test.t2 : t1_fk_1
710 truncate table t2;
711 Verify all the foreign key are re-created after truncate
712 Foreign keys referenced by test.t2 : t1_fk_1
713 drop table t1, t2;
714 #
715 # Truncating referenced table which references
716 # another table
717 #
718 create table t3 (
719 pk3 int not null primary key,
720 d int
721 ) engine=ndb;
722 create table t2 (
723 pk2 int not null primary key,
724 c int,
725 foreign key (c) references t3(pk3)
726 ) engine=ndb;
727 create table t1 (
728 pk1 int not null primary key,
729 b int,
730 foreign key (b) references t2(pk2)
731 ) engine=ndb;
732 set foreign_key_checks=1;
733 truncate table t2;
734 ERROR 42000: Cannot truncate a table referenced in a foreign key constraint (`test`.`t1`, CONSTRAINT `t1_fk_1`)
735 set foreign_key_checks=0;
736 Foreign keys referenced by test.t2 : t1_fk_1
737 Foreign keys on test.t2 : t2_fk_1
738 truncate table t2;
739 Verify all the foreign key are re-created after truncate
740 Foreign keys referenced by test.t2 : t1_fk_1
741 Foreign keys on test.t2 : t2_fk_1
742 drop table t1, t2, t3;
743 #
744 # Truncating referencing table
745 #
746 create table t2 (
747 pk2 int not null primary key,
748 c int
749 ) engine=ndb;
750 create table t1 (
751 pk1 int not null primary key,
752 b int,
753 foreign key fk1 (b) references t2(pk2)
754 ) engine=ndb;
755 set @@ndb_show_foreign_key_mock_tables= 1;
756 set foreign_key_checks=1;
757 truncate table t1;
758 set foreign_key_checks=0;
759 truncate table t1;
760 set @@ndb_show_foreign_key_mock_tables= 0;
761 show create table t1;
762 Table Create Table
763 t1 CREATE TABLE `t1` (
764 `pk1` int NOT NULL,
765 `b` int DEFAULT NULL,
766 PRIMARY KEY (`pk1`),
767 KEY `fk1` (`b`),
768 CONSTRAINT `t1_fk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`pk2`)
769 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
770 drop table t1, t2;
771 #
772 # Truncate with multiple foreign keys
773 #
774 create table t2 (
775 pk2 int not null primary key,
776 c int
777 ) engine=ndb;
778 create table t1 (
779 pk1 int not null primary key,
780 b int,
781 c int,
782 d int,
783 e int,
784 foreign key fk1 (b) references t2(pk2),
785 foreign key fk2 (c) references t3(pk3),
786 foreign key fk3 (d) references t3(pk4),
787 foreign key fk4 (e) references t4(pk5)
788 ) engine=ndb;
789 set @@ndb_show_foreign_key_mock_tables= 1;
790 set foreign_key_checks=1;
791 truncate table t1;
792 set foreign_key_checks=0;
793 truncate table t1;
794 set @@ndb_show_foreign_key_mock_tables= 0;
795 show create table t1;
796 Table Create Table
797 t1 CREATE TABLE `t1` (
798 `pk1` int NOT NULL,
799 `b` int DEFAULT NULL,
800 `c` int DEFAULT NULL,
801 `d` int DEFAULT NULL,
802 `e` int DEFAULT NULL,
803 PRIMARY KEY (`pk1`),
804 KEY `fk1` (`b`),
805 KEY `fk2` (`c`),
806 KEY `fk3` (`d`),
807 KEY `fk4` (`e`),
808 CONSTRAINT `t1_fk_1` FOREIGN KEY (`b`) REFERENCES `t2` (`pk2`),
809 CONSTRAINT `t1_fk_2` FOREIGN KEY (`c`) REFERENCES `t3` (`pk3`),
810 CONSTRAINT `t1_fk_3` FOREIGN KEY (`d`) REFERENCES `t3` (`pk4`),
811 CONSTRAINT `t1_fk_4` FOREIGN KEY (`e`) REFERENCES `t4` (`pk5`)
812 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
813 drop table t1, t2;
814 #
815 # Create child table referencing mock parent, insert data and
816 # then create the real parent (like mysqldump does it)
817 #
818 CREATE TABLE `address` (
819 `no` int NOT NULL AUTO_INCREMENT,
820 `type` int NOT NULL DEFAULT '37',
821 `customer_id` int NOT NULL,
822 `street` varchar(255) DEFAULT NULL,
823 `country` int DEFAULT NULL,
824 PRIMARY KEY (`no`),
825 KEY `ca_fk1` (`customer_id`),
826 CONSTRAINT `ca_fk1` FOREIGN KEY(`customer_id`) REFERENCES `customer` (`id`)
827 ON DELETE CASCADE ON UPDATE RESTRICT
828 ) ENGINE=ndbcluster AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
829 INSERT INTO `address` VALUES (1,37,1,'Main st. 1215',48);
830 CREATE TABLE `customer` (
831 `id` int NOT NULL,
832 PRIMARY KEY (`id`)
833 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
834 # Check DML
835 insert into address (type, customer_id, street, country)
836 values (38,2,'Second st. 2326',49 );
837 insert into address (type, customer_id, street, country)
838 values (39,1,'Third st. 3437',50 );
839 update address set country=50 where no=2;
840 insert into customer values(1);
841 # Check DML with foreign_key_checks on
842 set foreign_key_checks=1;
843 update address set customer_id = 3 where no = 1;
844 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`address`, CONSTRAINT `ca_fk1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT)
845 delete from customer where id = 1;
846 select no, type, customer_id from address;
847 no type customer_id
848 2 38 2
849 set foreign_key_checks=0;
850 drop table address, customer;
851 #
852 # Create child table referencing real parent, insert data
853 # and then create fk(like mysqldump does not do it)
854 #
855 CREATE TABLE `customer` (
856 `id` int NOT NULL,
857 PRIMARY KEY (`id`)
858 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1;
859 INSERT INTO `customer` VALUES (1);
860 CREATE TABLE `address` (
861 `no` int NOT NULL AUTO_INCREMENT,
862 `type` int NOT NULL DEFAULT '37',
863 `customer_id` int NOT NULL,
864 `street` varchar(255) DEFAULT NULL,
865 `country` int DEFAULT NULL,
866 PRIMARY KEY (`no`),
867 KEY `ca_fk1` (`customer_id`)
868 ) ENGINE=ndbcluster AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
869 INSERT INTO `address` VALUES (1,37,1,'Main st. 1215',48);
870 INSERT INTO `address` VALUES (2,22,2,'Second st. 2222',2);
871 # Add fk inplace
872 set foreign_key_checks=1;
873 ALTER TABLE address ALGORITHM=inplace,
874 ADD CONSTRAINT `ca_fk1` FOREIGN KEY(`customer_id`) REFERENCES `customer` (`id`)
875 ON DELETE CASCADE ON UPDATE RESTRICT;
876 ERROR HY000: Cannot add foreign key constraint
877 set foreign_key_checks=0;
878 ALTER TABLE address ALGORITHM=inplace,
879 ADD CONSTRAINT `ca_fk1` FOREIGN KEY(`customer_id`) REFERENCES `customer` (`id`)
880 ON DELETE CASCADE ON UPDATE RESTRICT;
881 # Add another fk copying
882 set foreign_key_checks=1;
883 ALTER TABLE address ALGORITHM=copy,
884 ADD CONSTRAINT `ca_fk2` FOREIGN KEY(`customer_id`) REFERENCES `customer` (`id`)
885 ON DELETE CASCADE ON UPDATE RESTRICT;
886 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`#sql-temporary`, CONSTRAINT `ca_fk2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT)
887 set foreign_key_checks=0;
888 ALTER TABLE address ALGORITHM=copy,
889 ADD CONSTRAINT `ca_fk2` FOREIGN KEY(`customer_id`) REFERENCES `customer` (`id`)
890 ON DELETE CASCADE ON UPDATE RESTRICT;
891 ALTER TABLE address ALGORITHM=inplace,
892 ADD COLUMN a int COLUMN_FORMAT DYNAMIC;
893 ALTER TABLE address ALGORITHM=copy,
894 ADD COLUMN b int not null;
895 # Check DML with foreign_key_checks on
896 set foreign_key_checks=1;
897 update address set customer_id = 3 where no = 1;
898 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`address`, CONSTRAINT `<constraint_name>` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT)
899 update address set customer_id = 1 where no = 2;
900 delete from customer where id = 1;
901 select count(*) from address;
902 count(*)
903 0
904 set foreign_key_checks=0;
905 drop table address, customer;
906 DROP VIEW ndb_show_tables_results;