Debian Codegraph @

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

raw
1 drop table if exists t2, t1;
2 select SUM(used) INTO @used_dr from ndbinfo.resources
3 where resource_name='DISK_RECORDS';
4 select SUM(used) INTO @used_fk from ndbinfo.ndb$pools
5 where (pool_name like '%foreign%' or pool_name like '%trigger%');
6 # bug#14095785
7 create table t1 (
8 a int not null,
9 b int not null,
10 primary key using hash (a),
11 unique key using hash (b)
12 ) engine ndb;
13 create table t2 (
14 a int not null,
15 b int not null,
16 primary key using hash (a),
17 key (b)
18 ) engine ndb;
19 alter table t2
20 add constraint foreign key (b) references t1 (b);
21 alter table t2
22 drop foreign key;
23 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 2
24 drop table t2, t1;
25 # bug#14095838
26 create table t1 (
27 a int not null,
28 primary key using hash (a)
29 ) engine ndb;
30 create table t2 (
31 a int not null,
32 b int not null,
33 primary key using hash (a),
34 key (b)
35 ) engine ndb;
36 alter table t2
37 add constraint fk1 foreign key (b) references t1 (a)
38 on delete cascade on update cascade;
39 ERROR HY000: Cannot add foreign key constraint
40 show warnings;
41 Level Code Message
42 Warning 1296 Got error 21000 'Create foreign key failed - parent key is primary key and on-update-cascade is not allowed' from NDB
43 Error 1215 Cannot add foreign key constraint
44 alter table t2
45 add constraint fk1 foreign key (b) references t1 (a)
46 on delete cascade on update restrict;
47 drop table t2, t1;
48 # bug#14195170
49 CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) ENGINE=NDB;
50 CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
51 price DECIMAL, PRIMARY KEY(category, id)) ENGINE=NDB;
52 CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=ndb;
53 ERROR HY000: Cannot add foreign key constraint
54 show warnings;
55 Level Code Message
56 Warning 1296 Got error 21000 'Create foreign key failed - parent key is primary key and on-update-cascade is not allowed' from NDB
57 Error 1215 Cannot add foreign key constraint
58 CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, INDEX (customer_id), FOREIGN KEY (customer_id) REFERENCES customer(id)) ENGINE=ndb;
59 ERROR HY000: Cannot add foreign key constraint
60 show warnings;
61 Level Code Message
62 Warning 1296 Got error 21000 'Create foreign key failed - parent key is primary key and on-update-cascade is not allowed' from NDB
63 Error 1215 Cannot add foreign key constraint
64 DROP TABLE customer, product;
65 # bug#14124623
66 CREATE TABLE counties (county VARCHAR(30) NOT NULL PRIMARY KEY,
67 country VARCHAR(30), INDEX county_country_index(country)) ENGINE=ndb;
68 INSERT INTO counties VALUES
69 ('Berkshire','England'),('Shropshire','England'),('Oxfordshire','England'),('Buckinghamshire','England');
70 CREATE TABLE towns (town VARCHAR(30) NOT NULL PRIMARY KEY, county
71 VARCHAR(30), INDEX county_county_index (county), CONSTRAINT county_town
72 FOREIGN KEY (county) REFERENCES counties(county) ON DELETE RESTRICT ON UPDATE
73 RESTRICT) ENGINE=ndb;
74 INSERT INTO towns VALUES
75 ('Maidenhead','Berkshire'),('Reading','Berkshire'),('Shrewsbury','Shropshire')
76 ,('Oxford','Oxfordshire');
77 CREATE TABLE languages (language VARCHAR(30) NOT NULL PRIMARY KEY,
78 country VARCHAR(30), INDEX lang_country_index(country), CONSTRAINT
79 country_language FOREIGN KEY (country) REFERENCES counties(country) ON DELETE
80 RESTRICT ON UPDATE RESTRICT) engine=ndb;
81 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'country_language' in the referenced table 'counties'
82 CREATE TABLE languages (language VARCHAR(30) NOT NULL PRIMARY KEY,
83 country VARCHAR(30), INDEX lang_country_index(country), CONSTRAINT
84 country_language FOREIGN KEY (country) REFERENCES counties(country) ON DELETE
85 RESTRICT ON UPDATE RESTRICT) engine=ndb;
86 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'country_language' in the referenced table 'counties'
87 CREATE TABLE languages (id INT NOT NULL PRIMARY KEY) engine=ndb;
88 drop table languages, towns, counties;
89 # test: child vs parent column types
90 create table t1 (
91 a int not null,
92 b int not null,
93 primary key using hash (a),
94 unique key using hash (b)
95 ) engine ndb;
96 create table t2 (
97 a int not null,
98 b char(20) not null,
99 primary key using hash (a),
100 key (b),
101 constraint fk1 foreign key (b) references t1 (b)
102 ) engine ndb;
103 ERROR HY000: Referencing column 'b' and referenced column 'b' in foreign key constraint 'fk1' are incompatible.
104 create table t2 (
105 a int not null,
106 b char(10) not null,
107 primary key using hash (a),
108 key (b)
109 ) engine ndb;
110 alter table t2 algorithm=inplace,
111 add constraint fk1 foreign key (b) references t1 (b);
112 ERROR HY000: Referencing column 'b' and referenced column 'b' in foreign key constraint 'fk1' are incompatible.
113 drop table t2, t1;
114 # test: parent table not exist
115 create table t2 (
116 a int not null,
117 b int not null,
118 primary key using hash (a),
119 key (b),
120 constraint fk1 foreign key (b) references t1 (b)
121 ) engine ndb;
122 ERROR HY000: Failed to open the referenced table 't1'
123 create table t2 (
124 a int not null,
125 b int not null,
126 primary key using hash (a),
127 key (b)
128 ) engine ndb;
129 alter table t2 algorithm=inplace,
130 add constraint fk1 foreign key (b) references t1 (b);
131 ERROR HY000: Failed to open the referenced table 't1'
132 drop table t2;
133 # test: parent column not exist
134 create table t1 (
135 a int not null,
136 primary key using hash (a)
137 ) engine ndb;
138 create table t2 (
139 a int not null,
140 b int not null,
141 primary key using hash (a),
142 key (b),
143 constraint fk1 foreign key (b) references t1 (b)
144 ) engine ndb;
145 ERROR HY000: Failed to add the foreign key constraint. Missing column 'b' for constraint 'fk1' in the referenced table 't1'
146 create table t2 (
147 a int not null,
148 b int not null,
149 primary key using hash (a),
150 key (b)
151 ) engine ndb;
152 alter table t2 algorithm=inplace,
153 add constraint fk1 foreign key (b) references t1 (b);
154 ERROR HY000: Failed to add the foreign key constraint. Missing column 'b' for constraint 'fk1' in the referenced table 't1'
155 drop table t2, t1;
156 # test: parent key no index
157 create table t1 (
158 a int not null,
159 b int not null,
160 primary key using hash (a)
161 ) engine ndb;
162 create table t2 (
163 a int not null,
164 b int not null,
165 primary key using hash (a),
166 key (b),
167 constraint fk1 foreign key (b) references t1 (b)
168 ) engine ndb;
169 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
170 create table t2 (
171 a int not null,
172 b int not null,
173 primary key using hash (a),
174 key (b)
175 ) engine ndb;
176 alter table t2 algorithm=inplace,
177 add constraint fk1 foreign key (b) references t1 (b);
178 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
179 drop table t2, t1;
180 # test: parent key no unique index
181 create table t1 (
182 a int not null,
183 b int not null,
184 primary key using hash (a),
185 key (b)
186 ) engine ndb;
187 create table t2 (
188 a int not null,
189 b int not null,
190 primary key using hash (a),
191 key (b),
192 constraint fk1 foreign key (b) references t1 (b)
193 ) engine ndb;
194 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
195 create table t2 (
196 a int not null,
197 b int not null,
198 primary key using hash (a),
199 key (b)
200 ) engine ndb;
201 alter table t2 algorithm=inplace,
202 add constraint fk1 foreign key (b) references t1 (b);
203 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
204 drop table t2, t1;
205 # test: child column not exist
206 create table t1 (
207 a int not null,
208 b int not null,
209 primary key using hash (a),
210 unique key using hash (b)
211 ) engine ndb;
212 create table t2 (
213 a int not null,
214 b int not null,
215 primary key using hash (a),
216 key (b),
217 constraint fk1 foreign key (c) references t1 (b)
218 ) engine ndb;
219 ERROR 42000: Key column 'c' doesn't exist in table
220 show warnings;
221 Level Code Message
222 Error 1072 Key column 'c' doesn't exist in table
223 create table t2 (
224 a int not null,
225 b int not null,
226 primary key using hash (a),
227 key (b)
228 ) engine ndb;
229 alter table t2 algorithm=inplace,
230 add constraint fk1 foreign key (c) references t1 (b);
231 ERROR 42000: Key column 'c' doesn't exist in table
232 show warnings;
233 Level Code Message
234 Error 1072 Key column 'c' doesn't exist in table
235 drop table t2, t1;
236 # test: child column no index
237 # no error - server creates KEY
238 create table t1 (
239 a int not null,
240 b int not null,
241 primary key using hash (a),
242 unique key using hash (b)
243 ) engine ndb;
244 create table t2 (
245 a int not null,
246 b int not null,
247 primary key using hash (a),
248 constraint fk1 foreign key (b) references t1 (b)
249 ) engine ndb;
250 drop table t2;
251 create table t2 (
252 a int not null,
253 b int not null,
254 primary key using hash (a)
255 ) engine ndb;
256 alter table t2 algorithm=inplace,
257 add constraint fk1 foreign key (b) references t1 (b);
258 drop table t2, t1;
259 # test: child vs parent column count
260 create table t1 (
261 a int not null,
262 b int not null,
263 c int not null,
264 primary key using hash (a),
265 unique key using hash (b, c)
266 ) engine ndb;
267 create table t2 (
268 a int not null,
269 b int not null,
270 primary key using hash (a),
271 key (b),
272 constraint fk1 foreign key (b) references t1 (b)
273 ) engine ndb;
274 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
275 create table t2 (
276 a int not null,
277 b int not null,
278 primary key using hash (a),
279 key (b)
280 ) engine ndb;
281 alter table t2 algorithm=inplace,
282 add constraint fk1 foreign key (b) references t1 (b);
283 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the referenced table 't1'
284 drop table t2, t1;
285 # bug#16246874
286 create table t1 (
287 id int primary key
288 ) engine=ndb;
289 create table t2 (
290 id int,
291 foreign key(id) references t1(id)
292 ) engine=ndb;
293 select * from information_schema.key_column_usage;
294 select REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
295 from information_schema.key_column_usage
296 where TABLE_SCHEMA = 'test' and TABLE_NAME = 't2';
297 REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME REFERENCED_COLUMN_NAME
298 test t1 id
299 drop table t2, t1;
300 # bug#16275509 - case: alter engine from ndb
301 create table t1 (
302 a int not null,
303 primary key using hash (a)
304 ) engine=ndb;
305 create table t2 (
306 a int not null,
307 primary key using hash (a),
308 foreign key fk1 (a) references t1 (a)
309 ) engine=ndb;
310 alter table t1 engine=innodb;
311 ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
312 alter table t2 engine=innodb;
313 ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
314 drop table t2, t1;
315 create table t1 (
316 a int primary key,
317 b int,
318 key (b),
319 foreign key fk1 (b) references t1 (a)
320 ) engine=ndb;
321 alter table t1 engine=innodb;
322 ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
323 drop table t1;
324 # bug#16275509 - case: allow FKs on partitioned
325 set new=on;
326 Warnings:
327 Warning 1287 '@@new' is deprecated and will be removed in a future release.
328 create table t1 (
329 a int primary key
330 ) engine=ndb
331 partition by hash (a) partitions 5;
332 create table t2 (
333 a int primary key,
334 foreign key fk1 (a) references t1 (a)
335 ) engine=ndb;
336 drop table t2, t1;
337 create table t1 (
338 a int primary key
339 ) engine=ndb;
340 create table t2 (
341 a int primary key,
342 foreign key fk1 (a) references t1 (a)
343 ) engine=ndb;
344 alter table t1
345 partition by hash (a) partitions 5;
346 drop table t2, t1;
347 create table t1 (
348 a int primary key
349 ) engine=ndb;
350 create table t2 (
351 a int primary key,
352 foreign key fk1 (a) references t1 (a)
353 ) engine=ndb
354 partition by hash (a) partitions 5;
355 drop table t2;
356 create table t2 (
357 a int primary key,
358 foreign key fk1 (a) references t1 (a)
359 ) engine=ndb;
360 alter table t2
361 partition by hash (a) partitions 5;
362 drop table t2, t1;
363 # bug#16275684 - case: alter FK parent column
364 create table t1 (
365 a int primary key,
366 b enum('a','b','c') not null,
367 unique key (b)
368 ) engine=ndb;
369 create table t2 (
370 a int primary key,
371 b enum('a','b','c') not null,
372 key (b),
373 constraint fk1 foreign key (b) references t1 (b)
374 ) engine=ndb;
375 insert into t1 values (1,'a'),(2,'b');
376 insert into t2 values (1,'a'),(2,'a'),(3,'b');
377 alter table t1 modify column b enum('a','b') not null;
378 ERROR HY000: Cannot change column 'b': used in a foreign key constraint 'fk1' of table 'test.t2'
379 alter table t2 drop foreign key fk1;
380 alter table t1 modify column b enum('a','b') not null;
381 show create table t1;
382 Table Create Table
383 t1 CREATE TABLE `t1` (
384 `a` int NOT NULL,
385 `b` enum('a','b') NOT NULL,
386 PRIMARY KEY (`a`),
387 UNIQUE KEY `b` (`b`)
388 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
389 select * from t1 order by a;
390 a b
391 1 a
392 2 b
393 select * from t2 order by a;
394 a b
395 1 a
396 2 a
397 3 b
398 drop table t2, t1;
399 # bug#16286164 - case: drop when referenced by different table
400 set @i=2;
401 create table t1 (
402 a int not null,
403 primary key using hash (a)
404 ) engine=innodb;
405 Warnings:
406 Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
407 create table t2 (
408 a int not null,
409 b int,
410 primary key using hash (a),
411 key (b),
412 constraint fk1 foreign key (b) references t1 (a)
413 ) engine=innodb;
414 Warnings:
415 Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
416 drop table t1;
417 ERROR HY000: Cannot drop table 't1' referenced by a foreign key constraint 'fk1' on table 't2'.
418 drop table t2, t1;
419 set @i=1;
420 create table t1 (
421 a int not null,
422 primary key using hash (a)
423 ) engine=ndb;
424 create table t2 (
425 a int not null,
426 b int,
427 primary key using hash (a),
428 key (b),
429 constraint fk1 foreign key (b) references t1 (a)
430 ) engine=ndb;
431 drop table t1;
432 ERROR HY000: Cannot drop table 't1' referenced by a foreign key constraint 'fk1' on table 't2'.
433 drop table t2, t1;
434 # bug#16286164 - case: drop when referenced by same table
435 set @i=2;
436 create table t1 (
437 a int not null,
438 b int,
439 primary key using hash (a),
440 key (b),
441 constraint fk1 foreign key (b) references t1 (a)
442 ) engine=innodb;
443 Warnings:
444 Note 3502 This storage engine does not support the HASH index algorithm, storage engine default was used instead.
445 drop table t1;
446 set @i=1;
447 create table t1 (
448 a int not null,
449 b int,
450 primary key using hash (a),
451 key (b),
452 constraint fk1 foreign key (b) references t1 (a)
453 ) engine=ndb;
454 drop table t1;
455 # BugXXZ - multi part unique index parent
456 create table t1 (
457 a int not null,
458 b int not null,
459 primary key using hash (a),
460 unique key using hash (a,b)
461 ) engine ndb;
462 create table t2 (
463 a int not null,
464 b int not null,
465 primary key using hash (a),
466 key (a,b),
467 foreign key fk1 (a,b) references t1 (a,b)
468 ) engine ndb;
469 drop table t2, t1;
470 # child-part-key
471 create table t1 (
472 a int not null,
473 primary key using hash (a)
474 ) engine ndb;
475 insert into t1 values (1),(2);
476 create table t2 (
477 a int not null,
478 b int not null,
479 c int not null,
480 primary key using hash (a),
481 key xbc (b,c),
482 constraint fk1 foreign key (b) references t1 (a)
483 ) engine ndb;
484 show create table t2;
485 Table Create Table
486 t2 CREATE TABLE `t2` (
487 `a` int NOT NULL,
488 `b` int NOT NULL,
489 `c` int NOT NULL,
490 PRIMARY KEY (`a`) USING HASH,
491 KEY `xbc` (`b`,`c`),
492 CONSTRAINT `fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)
493 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
494 insert into t2 (a,b,c) values (1,1,11),(2,2,21),(3,2,22);
495 insert into t2 (a,b,c) values (4,3,33);
496 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
497 delete from t1 where a = 1;
498 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
499 drop table t2;
500 create table t2 (
501 a int not null,
502 b int not null,
503 c int not null,
504 primary key using hash (a),
505 key xbc (b,c),
506 constraint fk1 foreign key (c) references t1 (a)
507 ) engine ndb;
508 show create table t2;
509 Table Create Table
510 t2 CREATE TABLE `t2` (
511 `a` int NOT NULL,
512 `b` int NOT NULL,
513 `c` int NOT NULL,
514 PRIMARY KEY (`a`) USING HASH,
515 KEY `xbc` (`b`,`c`),
516 KEY `fk1` (`c`),
517 CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`)
518 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
519 insert into t2 (a,c,b) values (1,1,11),(2,2,21),(3,2,22);
520 insert into t2 (a,c,b) values (4,3,33);
521 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
522 delete from t1 where a = 1;
523 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
524 drop table t2;
525 create table t2 (
526 a int not null,
527 b int not null,
528 c int not null,
529 primary key using hash (a),
530 unique key xbc (b,c) using hash,
531 constraint fk1 foreign key (b) references t1 (a)
532 ) engine ndb;
533 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk1' in the foreign table 't2'
534 create table t2 (
535 a int not null,
536 b int not null,
537 c int not null,
538 primary key using hash (a),
539 unique key xbc (b,c) using hash,
540 constraint fk1 foreign key (c) references t1 (a)
541 ) engine ndb;
542 show create table t2;
543 Table Create Table
544 t2 CREATE TABLE `t2` (
545 `a` int NOT NULL,
546 `b` int NOT NULL,
547 `c` int NOT NULL,
548 PRIMARY KEY (`a`) USING HASH,
549 UNIQUE KEY `xbc` (`b`,`c`) USING HASH,
550 KEY `fk1` (`c`),
551 CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`)
552 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
553 insert into t2 (a,c,b) values (1,1,11),(2,2,21),(3,2,22);
554 insert into t2 (a,c,b) values (4,3,33);
555 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
556 delete from t1 where a = 1;
557 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION ON UPDATE NO ACTION)
558 drop table t2, t1;
559 create table t1_parent (pk1 char(255) primary key) charset=latin1, engine = ndb;
560 create table t1 (
561 c50 char(255) column_format fixed, c49 char(255) column_format fixed, c48 char(255) column_format fixed, c47 char(255) column_format fixed, c46 char(255) column_format fixed,
562 c45 char(255) column_format fixed, c44 char(255) column_format fixed, c43 char(255) column_format fixed, c42 char(255) column_format fixed, c41 char(255) column_format fixed,
563 c40 char(255) column_format fixed, c39 char(255) column_format fixed, c38 char(255) column_format fixed, c37 char(255) column_format fixed, c36 char(255) column_format fixed,
564 c35 char(255) column_format fixed, c34 char(255) column_format fixed, c33 char(255) column_format fixed, c32 char(255) column_format fixed, c31 char(255) column_format fixed,
565 c30 char(255) column_format fixed, c29 char(255) column_format fixed, c28 char(255) column_format fixed, c27 char(255) column_format fixed, c26 char(255) column_format fixed,
566 c25 char(255) column_format fixed, c24 char(255) column_format fixed, c23 char(255) column_format fixed, c22 char(255) column_format fixed, c21 char(255) column_format fixed,
567 c20 char(255) column_format fixed, c19 char(255) column_format fixed, c18 char(255) column_format fixed, c17 char(255) column_format fixed, c16 char(255) column_format fixed,
568 c15 char(255) column_format fixed, c14 char(255) column_format fixed, c13 char(255) column_format fixed, c12 char(255) column_format fixed, c11 char(255) column_format fixed,
569 c10 char(255) column_format fixed, c9 char(255) column_format fixed, c8 char(255) column_format fixed, c7 char(255) column_format fixed, c6 char(255) column_format fixed,
570 c5 char(255) column_format fixed, c4 char(255) column_format fixed, c3 char(255) column_format fixed, c2 char(255) column_format fixed, c1 char(255) column_format fixed,
571 primary key using hash(c1)
572 ,foreign key(c1) references t1_parent(pk1)
573 ) charset=latin1, engine=ndb;
574 ERROR HY000: Can't create table 't1' (use SHOW WARNINGS for more info).
575 SHOW WARNINGS;
576 Level Code Message
577 Warning 1296 Got error 851 'Fixed-size column offset exceeded max.Use VARCHAR or COLUMN_FORMAT DYNAMIC for memory-stored columns' from NDB
578 Warning 1296 Failed to commit NDB schema transaction
579 Error 1005 Can't create table 't1' (use SHOW WARNINGS for more info).
580 create table t1(a int primary key) engine = ndb;
581 drop table t1, t1_parent;
582 # bug#17591531 - ERROR ON DROP INDEX WHEN REFERENCING FOREIGN KEYS
583 create table t1 (
584 a int not null,
585 b int not null,
586 unique key t1x1 (b) using hash,
587 primary key using hash (a)
588 ) engine=ndb;
589 create table t2 (
590 a int not null,
591 b int not null,
592 c int not null,
593 key (c),
594 key t2x1 (b),
595 primary key using hash (a),
596 constraint t2fk1 foreign key (b) references t1 (b)
597 ) engine=ndb;
598 # try to drop indexes
599 alter table t1 drop index t1x1;
600 ERROR HY000: Cannot drop index 't1x1': needed in a foreign key constraint
601 alter table t2 drop index t2x1;
602 ERROR HY000: Cannot drop index 't2x1': needed in a foreign key constraint
603 insert into t1 values (1,10),(2,20),(3,30);
604 insert into t2 values (1,10,100),(2,20,200),(3,30,300);
605 insert into t2 values (4,40,400);
606 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION)
607 delete from t1 where a=1;
608 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2fk1` FOREIGN KEY (`b`) REFERENCES `t1` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION)
609 drop table t2, t1;
610 # bug#17232212 - NDB DROP NON-EXISTENT FK SUCCEEDS
611 create table t1 (
612 a int primary key
613 ) engine=ndb;
614 # algorithm=inplace
615 create table t2 (
616 a int primary key,
617 b int,
618 c int,
619 key xb (b),
620 key xc (c),
621 constraint fkb foreign key (b) references t1 (a),
622 constraint fkc foreign key (c) references t1 (a)
623 ) engine=ndb;
624 alter table t2
625 drop foreign key nosuchfk,
626 algorithm=inplace;
627 ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
628 show create table t2;
629 Table Create Table
630 t2 CREATE TABLE `t2` (
631 `a` int NOT NULL,
632 `b` int DEFAULT NULL,
633 `c` int DEFAULT NULL,
634 PRIMARY KEY (`a`),
635 KEY `xb` (`b`),
636 KEY `xc` (`c`),
637 CONSTRAINT `fkb` FOREIGN KEY (`b`) REFERENCES `t1` (`a`),
638 CONSTRAINT `fkc` FOREIGN KEY (`c`) REFERENCES `t1` (`a`)
639 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
640 alter table t2
641 drop foreign key fkb,
642 drop foreign key nosuchfk,
643 drop foreign key fkc,
644 algorithm=inplace;
645 ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
646 show create table t2;
647 Table Create Table
648 t2 CREATE TABLE `t2` (
649 `a` int NOT NULL,
650 `b` int DEFAULT NULL,
651 `c` int DEFAULT NULL,
652 PRIMARY KEY (`a`),
653 KEY `xb` (`b`),
654 KEY `xc` (`c`),
655 CONSTRAINT `fkb` FOREIGN KEY (`b`) REFERENCES `t1` (`a`),
656 CONSTRAINT `fkc` FOREIGN KEY (`c`) REFERENCES `t1` (`a`)
657 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
658 drop table t2;
659 # algorithm=copy
660 create table t2 (
661 a int primary key,
662 b int,
663 c int,
664 key xb (b),
665 key xc (c),
666 constraint fkb foreign key (b) references t1 (a),
667 constraint fkc foreign key (c) references t1 (a)
668 ) engine=ndb;
669 alter table t2
670 drop foreign key nosuchfk,
671 algorithm=copy;
672 ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
673 show create table t2;
674 Table Create Table
675 t2 CREATE TABLE `t2` (
676 `a` int NOT NULL,
677 `b` int DEFAULT NULL,
678 `c` int DEFAULT NULL,
679 PRIMARY KEY (`a`),
680 KEY `xb` (`b`),
681 KEY `xc` (`c`),
682 CONSTRAINT `fkb` FOREIGN KEY (`b`) REFERENCES `t1` (`a`),
683 CONSTRAINT `fkc` FOREIGN KEY (`c`) REFERENCES `t1` (`a`)
684 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
685 alter table t2
686 drop foreign key fkb,
687 drop foreign key nosuchfk,
688 drop foreign key fkc,
689 algorithm=copy;
690 ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
691 show create table t2;
692 Table Create Table
693 t2 CREATE TABLE `t2` (
694 `a` int NOT NULL,
695 `b` int DEFAULT NULL,
696 `c` int DEFAULT NULL,
697 PRIMARY KEY (`a`),
698 KEY `xb` (`b`),
699 KEY `xc` (`c`),
700 CONSTRAINT `fkb` FOREIGN KEY (`b`) REFERENCES `t1` (`a`),
701 CONSTRAINT `fkc` FOREIGN KEY (`c`) REFERENCES `t1` (`a`)
702 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
703 drop table t2, t1;
704 # bug#18662582 - NDB_FK_DISABLE.TEST FAILS TO DROP FOREIGN KEY
705 create table t1 (
706 a int, primary key using hash (a)
707 ) engine = ndb;
708 create table t2 (
709 a int, primary key using hash (a)
710 ) engine = ndb;
711 alter table t2 add constraint fkname2 foreign key (a) references t1 (a);
712 create table t3 (
713 a int, primary key using hash (a)
714 ) engine = ndb;
715 alter table t3 add constraint fkname3 foreign key (a) references t2 (a);
716 alter table t2 algorithm=inplace, drop foreign key nosuchfk;
717 ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
718 alter table t2 algorithm=inplace, drop foreign key fkname2;
719 alter table t3 algorithm=inplace, drop foreign key fkname3;
720 drop table t1, t2, t3;
721 create table t1 (
722 a int, primary key using hash (a)
723 ) engine = ndb;
724 create table t2 (
725 a int, primary key using hash (a)
726 ) engine = ndb;
727 alter table t2 add constraint fkname2 foreign key (a) references t1 (a);
728 create table t3 (
729 a int, primary key using hash (a)
730 ) engine = ndb;
731 alter table t3 add constraint fkname3 foreign key (a) references t2 (a);
732 alter table t2 algorithm=copy, drop foreign key nosuchfk;
733 ERROR 42000: Can't DROP 'nosuchfk'; check that column/key exists
734 alter table t2 algorithm=copy, drop foreign key fkname2;
735 alter table t3 algorithm=copy, drop foreign key fkname3;
736 drop table t1, t2, t3;
737 # bug#18824753 - NDB_RESTORE FAILS WITH FKS WITH SAME NAME
738 create table t1 (a int primary key) engine=ndb;
739 create table t2 (a int primary key) engine=ndb;
740 create table t3 (a int primary key) engine=ndb;
741 create table t4 (a int primary key) engine=ndb;
742 alter table t2
743 add constraint myfk2 foreign key (a) references t1 (a);
744 alter table t4
745 add constraint myfk4 foreign key (a) references t3 (a);
746 alter table t4
747 add constraint `11/22/myfk4` foreign key (a) references t3 (a);
748 ERROR HY000: Cannot add foreign key constraint
749 show warnings;
750 Level Code Message
751 Warning 1296 Got error 21090 'Create foreign key failed in NDB - name contains invalid character (/)' from NDB
752 Error 1215 Cannot add foreign key constraint
753 # run backup
754 drop table t2, t1;
755 drop table t4, t3;
756 # run restore meta
757 show create table t2;
758 Table Create Table
759 t2 CREATE TABLE `t2` (
760 `a` int NOT NULL,
761 PRIMARY KEY (`a`),
762 CONSTRAINT `myfk2` FOREIGN KEY (`a`) REFERENCES `t1` (`a`)
763 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
764 show create table t4;
765 Table Create Table
766 t4 CREATE TABLE `t4` (
767 `a` int NOT NULL,
768 PRIMARY KEY (`a`),
769 CONSTRAINT `myfk4` FOREIGN KEY (`a`) REFERENCES `t3` (`a`)
770 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
771 drop table t2, t1;
772 drop table t4, t3;
773 # bug18825966 - ADDING FK TO TABLE WITH DATA GIVES INTERNAL ERROR 208
774 create table t1 (
775 c0 int,
776 c1 int,
777 primary key using hash (c1)
778 ) engine=ndb;
779 create table t2 (
780 c0 int,
781 primary key using hash (c0)
782 ) engine=ndb;
783 insert into t1 (c1) values (1);
784 insert into t2 (c0) values (1);
785 alter table t2 add constraint foreign key fk (c0) references t1 (c1);
786 drop table t2, t1;
787 create table t1 (
788 c0 int,
789 c1 int,
790 c2 int,
791 c3 int,
792 c4 int,
793 c5 int,
794 primary key (c2,c5)
795 ) engine=ndb;
796 create table t2 (
797 c0 int,
798 c1 int,
799 c2 int,
800 c3 int,
801 c4 int,
802 c5 int,
803 primary key (c1,c4)
804 ) engine=ndb;
805 insert into t1 (c2,c5) values (1,10),(2,20),(3,30);
806 insert into t2 (c1,c4) values (1,10),(2,20),(3,30);
807 alter table t2 add constraint fk foreign key (c1,c4) references t1 (c2,c5);
808 insert into t2 (c1,c4) values (4,40);
809 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`c1`,`c4`) REFERENCES `t1` (`c2`,`c5`) ON DELETE NO ACTION ON UPDATE NO ACTION)
810 drop table t2, t1;
811 create table t1 (
812 c0 int,
813 c1 int,
814 c2 int not null,
815 c3 int,
816 c4 int,
817 c5 int not null,
818 primary key (c0),
819 unique key using hash (c2,c5)
820 ) engine=ndb;
821 create table t2 (
822 c0 int,
823 c1 int,
824 c2 int,
825 c3 int,
826 c4 int,
827 c5 int,
828 primary key (c1,c4)
829 ) engine=ndb;
830 insert into t1 (c0,c2,c5) values (1,1,10),(2,2,20),(3,3,30);
831 insert into t2 (c1,c4) values (1,10),(2,20),(3,30);
832 alter table t2 add constraint fk foreign key (c1,c4) references t1 (c2,c5);
833 insert into t2 (c1,c4) values (4,40);
834 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk` FOREIGN KEY (`c1`,`c4`) REFERENCES `t1` (`c2`,`c5`) ON DELETE NO ACTION ON UPDATE NO ACTION)
835 drop table t2, t1;
836 #
837 # BUG#21308928 : CANNOT ADD UNIQUE CONSTRAINT ON SET OF FIELDS WITH EXISTING FOREIGN KEY
838 #
839 #Create the tables
840 create table t1 (
841 a int not null primary key
842 ) engine ndb;
843 create table t2 (
844 a int primary key,
845 b int not null,
846 c int,
847 constraint fk1 foreign key (a) references t1(a),
848 constraint fk2 foreign key (b) references t1(a),
849 constraint fk3 foreign key (c) references t1(a)
850 ) engine ndb;
851 #Add a unique constraint to the foreign key column
852 alter table t2 add constraint uniquet2a unique (a);
853 alter table t2 add constraint uniquet2b unique (b);
854 alter table t2 add constraint uniquet2c unique (c);
855 show create table t2;
856 Table Create Table
857 t2 CREATE TABLE `t2` (
858 `a` int NOT NULL,
859 `b` int NOT NULL,
860 `c` int DEFAULT NULL,
861 PRIMARY KEY (`a`),
862 UNIQUE KEY `uniquet2a` (`a`),
863 UNIQUE KEY `uniquet2b` (`b`),
864 UNIQUE KEY `uniquet2c` (`c`),
865 CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `t1` (`a`),
866 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `t1` (`a`),
867 CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `t1` (`a`)
868 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
869 #Drop tables
870 drop table t2, t1;
871 #
872 # Bug#27930382 : FOREIGN KEY CONSTRAINT VIOLATIONS INCORRECTLY REPORTED
873
874 create table t1 (
875 kol1 int primary key,
876 kol2 int not null,
877 kol3 int not null,
878 kol4 int not null,
879 kol5 int not null,
880 unique key uq1(kol2),
881 foreign key fk1(kol3) references t1(kol2) on update restrict on delete restrict
882 ) engine = ndb;
883 insert into t1 values (1,1,1,1,1);
884 delete from t1 where kol1=1;
885 drop table t1;
886 create table t1 (
887 kol1 int primary key,
888 kol2 int not null,
889 kol3 int not null,
890 kol4 int not null,
891 kol5 int not null,
892 foreign key fk1(kol3) references t1(kol1) on update restrict on delete restrict
893 ) engine = ndb;
894 insert into t1 values (1,1,1,1,1);
895 delete from t1 where kol1=1;
896 drop table t1;
897 create table t1 (
898 kol1 int primary key,
899 kol2 int not null,
900 kol3 int not null,
901 kol4 int not null,
902 kol5 int not null,
903 unique key uq1(kol2),
904 unique key uq2(kol3),
905 foreign key fk1(kol3) references t1(kol2) on update restrict on delete restrict
906 ) engine = ndb;
907 insert into t1 values (1,1,1,1,1);
908 delete from t1 where kol1=1;
909 insert into t1 values (2,1,1,1,1);
910 delete from t1 where kol1=2;
911 insert into t1 values (3,1,1,1,1);
912 delete from t1 where kol1=3;
913 insert into t1 values (4,1,1,1,1);
914 delete from t1 where kol1=4;
915 insert into t1 values (1,0,0,0,0);
916 insert into t1 values (2,1,1,1,1);
917 insert into t1 values (3,2,2,2,2);
918 insert into t1 values (4,3,3,3,3);
919 delete from t1;
920 drop table t1;
921 select SUM(used) - @used_dr <= 8 from ndbinfo.resources where resource_name='DISK_RECORDS';
922 SUM(used) - @used_dr <= 8
923 1
924 select ROUND(SUM(used) - @used_fk) from ndbinfo.ndb$pools
925 where (pool_name like '%foreign%' or pool_name like '%trigger%');
926 ROUND(SUM(used) - @used_fk)
927 0