Debian Codegraph @

mysql-test/suite/innodb/r/foreign_key.result

raw
1 #
2 # Bug #19027905 ASSERT RET.SECOND DICT_CREATE_FOREIGN_CONSTRAINTS_LOW
3 # DICT_CREATE_FOREIGN_CONSTR
4 #
5 create table t1 (f1 int primary key) engine=InnoDB;
6 create table t2 (f1 int primary key,
7 constraint c1 foreign key (f1) references t1(f1),
8 constraint c1 foreign key (f1) references t1(f1)) engine=InnoDB;
9 ERROR HY000: Duplicate foreign key constraint name 'c1'
10 create table t2 (f1 int primary key,
11 constraint c1 foreign key (f1) references t1(f1)) engine=innodb;
12 alter table t2 add constraint c1 foreign key (f1) references t1(f1);
13 ERROR HY000: Duplicate foreign key constraint name 'c1'
14 set foreign_key_checks = 0;
15 alter table t2 add constraint c1 foreign key (f1) references t1(f1);
16 ERROR HY000: Duplicate foreign key constraint name 'c1'
17 drop table t2, t1;
18 #
19 # Bug #20031243 CREATE TABLE FAILS TO CHECK IF FOREIGN KEY COLUMN
20 # NULL/NOT NULL MISMATCH
21 #
22 set foreign_key_checks = 1;
23 show variables like 'foreign_key_checks';
24 Variable_name Value
25 foreign_key_checks ON
26 CREATE TABLE t1
27 (a INT NOT NULL,
28 b INT NOT NULL,
29 INDEX idx(a)) ENGINE=InnoDB;
30 CREATE TABLE t2
31 (a INT KEY,
32 b INT,
33 INDEX ind(b),
34 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
35 ENGINE=InnoDB;
36 show create table t1;
37 Table Create Table
38 t1 CREATE TABLE `t1` (
39 `a` int NOT NULL,
40 `b` int NOT NULL,
41 KEY `idx` (`a`)
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
43 show create table t2;
44 Table Create Table
45 t2 CREATE TABLE `t2` (
46 `a` int NOT NULL,
47 `b` int DEFAULT NULL,
48 PRIMARY KEY (`a`),
49 KEY `ind` (`b`),
50 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE
51 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
52 INSERT INTO t1 VALUES (1, 80);
53 INSERT INTO t1 VALUES (2, 81);
54 INSERT INTO t1 VALUES (3, 82);
55 INSERT INTO t1 VALUES (4, 83);
56 INSERT INTO t1 VALUES (5, 84);
57 INSERT INTO t2 VALUES (51, 1);
58 INSERT INTO t2 VALUES (52, 2);
59 INSERT INTO t2 VALUES (53, 3);
60 INSERT INTO t2 VALUES (54, 4);
61 INSERT INTO t2 VALUES (55, 5);
62 SELECT a, b FROM t1 ORDER BY a;
63 a b
64 1 80
65 2 81
66 3 82
67 4 83
68 5 84
69 SELECT a, b FROM t2 ORDER BY a;
70 a b
71 51 1
72 52 2
73 53 3
74 54 4
75 55 5
76 INSERT INTO t2 VALUES (56, 6);
77 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`) ON DELETE CASCADE ON UPDATE CASCADE)
78 NewDD TODO: Runtime Bug#25722221 - RENAME COLUMN DID NOT UPDATE MYSQL.FOREIGN_KEY_COLUMN_USAGE FOR FK CONSTRAINT
79 # Operations on child table
80 TODO: Waiting for Bug#24666169
81 DROP TABLE t2, t1;
82 #
83 # Bug#20752436: INNODB: FAILING ASSERTION: 0 IN FILE HANDLER0ALTER.CC
84 # LINE 6647
85 #
86 # Verify that index types that cannot be used as foreign keys are
87 # ignored when creating foreign keys.
88 set @@foreign_key_checks=0;
89 CREATE TABLE t1(a CHAR(100), b GEOMETRY NOT NULL SRID 0) ENGINE InnoDB;
90 # Creating a foreign key on a GEOMETRY column is not supported
91 ALTER TABLE t1 ADD CONSTRAINT fi_b FOREIGN KEY(b) REFERENCES ti2(b);
92 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fi_b' in the foreign table 't1'
93 # Adds FULLTEXT and SPATAL indices which cannot be used as foreign keys
94 ALTER TABLE t1 ADD FULLTEXT INDEX(a), ADD SPATIAL INDEX(b);
95 Warnings:
96 Warning 124 InnoDB rebuilding table to add column FTS_DOC_ID
97 # Adds a foreign key on column with FULLTEXT index.
98 # The FULLTEXT index cannot be used and the generated key must be kept
99 ALTER TABLE t1 ADD CONSTRAINT fi_a FOREIGN KEY(a) REFERENCES ti2(a);
100 ANALYZE TABLE t1;
101 Table Op Msg_type Msg_text
102 test.t1 analyze status OK
103 SHOW INDEXES FROM t1;
104 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
105 t1 1 b 1 b A 0 32 NULL SPATIAL YES NULL
106 t1 1 fi_a 1 a A 0 NULL NULL YES BTREE YES NULL
107 t1 1 a 1 a NULL 0 NULL NULL YES FULLTEXT YES NULL
108 # Attempt to add a foreign key on column with SPATIAL index.
109 # The SPATIAL index cannot be used so this becomes an attempt at
110 # creating a foreign key on a GEOMETRY column which is not supported
111 ALTER TABLE t1 ADD CONSTRAINT fi_b FOREIGN KEY(b) REFERENCES ti2(b);
112 ERROR HY000: Cannot drop index 'fi_a': needed in a foreign key constraint
113 DROP TABLE t1;
114 set @@foreign_key_checks= 1;
115 # bug#25126722 FOREIGN KEY CONSTRAINT NAME IS NULL AFTER RESTART
116 # base bug#24818604 [GR]
117 #
118 CREATE TABLE t1 (c1 INT PRIMARY KEY);
119 CREATE TABLE t2 (c1 INT PRIMARY KEY, FOREIGN KEY (c1) REFERENCES t1(c1));
120 INSERT INTO t1 VALUES (1);
121 INSERT INTO t2 VALUES (1);
122 SELECT unique_constraint_name FROM information_schema.referential_constraints
123 WHERE table_name = 't2';
124 UNIQUE_CONSTRAINT_NAME
125 PRIMARY
126 # restart
127 SELECT unique_constraint_name FROM information_schema.referential_constraints
128 WHERE table_name = 't2';
129 UNIQUE_CONSTRAINT_NAME
130 PRIMARY
131 SELECT * FROM t1;
132 c1
133 1
134 SELECT unique_constraint_name FROM information_schema.referential_constraints
135 WHERE table_name = 't2';
136 UNIQUE_CONSTRAINT_NAME
137 PRIMARY
138 DROP TABLE t2;
139 DROP TABLE t1;
140 #
141 # BUG#25976199 GROUP_REPLICATION.GR_FOREIGN_CHECKS_DISABLED TEST FAILING
142 # ON PB2
143 #
144 SET foreign_key_checks=0;
145 CREATE TABLE child_table (c1 INT PRIMARY KEY,
146 FOREIGN KEY (c1) REFERENCES parent_table(c1));
147 CREATE TABLE parent_table (c1 INT PRIMARY KEY);
148 SET foreign_key_checks=1;
149 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN WHERE
150 ID='test/child_table_ibfk_1';
151 ID FOR_NAME REF_NAME N_COLS TYPE
152 test/child_table_ibfk_1 test/child_table test/parent_table 1 48
153 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS WHERE
154 ID='test/child_table_ibfk_1';
155 ID FOR_COL_NAME REF_COL_NAME POS
156 test/child_table_ibfk_1 c1 c1 1
157 DROP TABLE parent_table;
158 ERROR HY000: Cannot drop table 'parent_table' referenced by a foreign key constraint 'child_table_ibfk_1' on table 'child_table'.
159 DROP TABLE child_table;
160 DROP TABLE parent_table;
161 #
162 # Bug#26764604 - INNODB: FAILING ASSERTION: COL_NR < TABLE->N_DEF
163 #
164 SELECT @@foreign_key_checks;
165 @@foreign_key_checks
166 1
167 CREATE TABLE t1 (
168 pk int(11) NOT NULL AUTO_INCREMENT,
169 unique1 int(11) DEFAULT NULL,
170 unique2 int(11) DEFAULT NULL,
171 two int(11) DEFAULT NULL,
172 four int(11) DEFAULT NULL,
173 ten int(11) DEFAULT NULL,
174 twenty int(11) DEFAULT NULL,
175 forty int(11) DEFAULT NULL,
176 sixty int(11) DEFAULT NULL,
177 eighty int(11) DEFAULT NULL,
178 tenPercent int(11) DEFAULT NULL,
179 twentyPercent int(11) DEFAULT NULL,
180 fiftyPercent int(11) DEFAULT NULL,
181 unique1gc int(11) GENERATED ALWAYS AS ((unique1 + unique2)) VIRTUAL,
182 unique2gc int(11) GENERATED ALWAYS AS ((unique1 * unique2)) VIRTUAL,
183 twogc int(11) GENERATED ALWAYS AS (((two * two) + 1)) VIRTUAL,
184 fourgc int(11) GENERATED ALWAYS AS (((four + four) - 1)) VIRTUAL,
185 tengc int(11) GENERATED ALWAYS AS ((twogc + ten)) VIRTUAL,
186 twentygc int(11) GENERATED ALWAYS AS ((((twenty * twogc) * unique1) % 20)) VIRTUAL,
187 fortygc int(11) GENERATED ALWAYS AS (forty) VIRTUAL,
188 sixtygc int(11) GENERATED ALWAYS AS (sixty) VIRTUAL,
189 eightygc int(11) GENERATED ALWAYS AS ((((eighty - twentygc) - ten) - two)) VIRTUAL,
190 PRIMARY KEY (pk),
191 KEY allkey(unique1,unique2,two,four,ten,twenty,forty,sixty,eighty,tenPercent,twentyPercent,fiftyPercent),
192 KEY kugc1 (unique1gc),
193 KEY kugc2 (unique2gc),
194 KEY kumixgc1 (unique1gc,unique2gc),
195 KEY kgc1 (twogc,fourgc),
196 KEY kgc2 (fourgc,tengc),
197 KEY kgc3 (tengc,twentygc),
198 KEY kgc4 (twentygc,fortygc),
199 KEY kgc5 (fortygc,sixtygc),
200 KEY kgc6 (sixtygc,eightygc),
201 KEY allkeygc(unique1gc,unique2gc,twogc,fourgc,tengc,twentygc,fortygc,sixtygc,eightygc)
202 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
203 Warnings:
204 Warning 1681 Integer display width is deprecated and will be removed in a future release.
205 Warning 1681 Integer display width is deprecated and will be removed in a future release.
206 Warning 1681 Integer display width is deprecated and will be removed in a future release.
207 Warning 1681 Integer display width is deprecated and will be removed in a future release.
208 Warning 1681 Integer display width is deprecated and will be removed in a future release.
209 Warning 1681 Integer display width is deprecated and will be removed in a future release.
210 Warning 1681 Integer display width is deprecated and will be removed in a future release.
211 Warning 1681 Integer display width is deprecated and will be removed in a future release.
212 Warning 1681 Integer display width is deprecated and will be removed in a future release.
213 Warning 1681 Integer display width is deprecated and will be removed in a future release.
214 Warning 1681 Integer display width is deprecated and will be removed in a future release.
215 Warning 1681 Integer display width is deprecated and will be removed in a future release.
216 Warning 1681 Integer display width is deprecated and will be removed in a future release.
217 Warning 1681 Integer display width is deprecated and will be removed in a future release.
218 Warning 1681 Integer display width is deprecated and will be removed in a future release.
219 Warning 1681 Integer display width is deprecated and will be removed in a future release.
220 Warning 1681 Integer display width is deprecated and will be removed in a future release.
221 Warning 1681 Integer display width is deprecated and will be removed in a future release.
222 Warning 1681 Integer display width is deprecated and will be removed in a future release.
223 Warning 1681 Integer display width is deprecated and will be removed in a future release.
224 Warning 1681 Integer display width is deprecated and will be removed in a future release.
225 Warning 1681 Integer display width is deprecated and will be removed in a future release.
226 CREATE TABLE t2_fk (
227 pk int(11) NOT NULL,
228 sixty int(11) DEFAULT NULL,
229 PRIMARY KEY (pk),
230 CONSTRAINT fk6 FOREIGN KEY (sixty) REFERENCES t1(sixty)
231 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
232 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk6' in the referenced table 't1'
233 DROP TABLE t1;
234 #
235 # Bug#27014308 - UPGRADING TO 8.0.3 FAILS IF FOREIGN KEYS OF EXACTLY 64 CHARACTERS ARE PRESENT
236 #
237 # test with FK identifier of length exactly 64 characters;
238 CREATE TABLE parent (
239 id INT NOT NULL,
240 PRIMARY KEY (id)
241 ) ENGINE=INNODB;
242 CREATE TABLE child (
243 id INT,
244 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
245 INT,
246 INDEX par_ind
247 (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx),
248 CONSTRAINT
249 `xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
250 FOREIGN
251 KEY (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)
252 REFERENCES parent(id)
253 ON DELETE CASCADE
254 ) ENGINE=INNODB;
255 DROP TABLE child;
256 DROP TABLE parent;
257 # test with FK identifier of length exactly 65 characters,
258 # child table creation should fail
259 CREATE TABLE parent (
260 id INT NOT NULL,
261 PRIMARY KEY (id)
262 ) ENGINE=INNODB;
263 CREATE TABLE child (
264 id INT,
265 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
266 INT,
267 INDEX par_ind
268 (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx),
269 CONSTRAINT
270 `xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx`
271 FOREIGN
272 KEY (xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)
273 REFERENCES parent(id)
274 ON DELETE CASCADE
275 ) ENGINE=INNODB;
276 ERROR 42000: Identifier name 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' is too long
277 DROP TABLE parent;
278 # Test delete/update rules combination.
279 CREATE TABLE t1
280 (a INT NOT NULL,
281 b INT NOT NULL,
282 INDEX idx(a)) ENGINE=InnoDB;
283 CREATE TABLE t2
284 (a INT KEY,
285 b INT,
286 INDEX ind(b),
287 FOREIGN KEY (b) REFERENCES t1(a))
288 ENGINE=InnoDB;
289 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
290 ID FOR_NAME REF_NAME N_COLS TYPE
291 test/t2_ibfk_1 test/t2 test/t1 1 48
292 DROP TABLE t2;
293 CREATE TABLE t2
294 (a INT KEY,
295 b INT,
296 INDEX ind(b),
297 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE)
298 ENGINE=InnoDB;
299 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
300 ID FOR_NAME REF_NAME N_COLS TYPE
301 test/t2_ibfk_1 test/t2 test/t1 1 33
302 DROP TABLE t2;
303 CREATE TABLE t2
304 (a INT KEY,
305 b INT,
306 INDEX ind(b),
307 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL)
308 ENGINE=InnoDB;
309 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
310 ID FOR_NAME REF_NAME N_COLS TYPE
311 test/t2_ibfk_1 test/t2 test/t1 1 34
312 DROP TABLE t2;
313 CREATE TABLE t2
314 (a INT KEY,
315 b INT,
316 INDEX ind(b),
317 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION)
318 ENGINE=InnoDB;
319 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
320 ID FOR_NAME REF_NAME N_COLS TYPE
321 test/t2_ibfk_1 test/t2 test/t1 1 48
322 DROP TABLE t2;
323 CREATE TABLE t2
324 (a INT KEY,
325 b INT,
326 INDEX ind(b),
327 FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE CASCADE)
328 ENGINE=InnoDB;
329 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
330 ID FOR_NAME REF_NAME N_COLS TYPE
331 test/t2_ibfk_1 test/t2 test/t1 1 20
332 DROP TABLE t2;
333 CREATE TABLE t2
334 (a INT KEY,
335 b INT,
336 INDEX ind(b),
337 FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE SET NULL)
338 ENGINE=InnoDB;
339 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
340 ID FOR_NAME REF_NAME N_COLS TYPE
341 test/t2_ibfk_1 test/t2 test/t1 1 24
342 DROP TABLE t2;
343 CREATE TABLE t2
344 (a INT KEY,
345 b INT,
346 INDEX ind(b),
347 FOREIGN KEY (b) REFERENCES t1(a) ON UPDATE NO ACTION)
348 ENGINE=InnoDB;
349 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
350 ID FOR_NAME REF_NAME N_COLS TYPE
351 test/t2_ibfk_1 test/t2 test/t1 1 48
352 DROP TABLE t2;
353 CREATE TABLE t2
354 (a INT KEY,
355 b INT,
356 INDEX ind(b),
357 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE)
358 ENGINE=InnoDB;
359 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
360 ID FOR_NAME REF_NAME N_COLS TYPE
361 test/t2_ibfk_1 test/t2 test/t1 1 5
362 DROP TABLE t2;
363 CREATE TABLE t2
364 (a INT KEY,
365 b INT,
366 INDEX ind(b),
367 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE SET NULL)
368 ENGINE=InnoDB;
369 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
370 ID FOR_NAME REF_NAME N_COLS TYPE
371 test/t2_ibfk_1 test/t2 test/t1 1 9
372 DROP TABLE t2;
373 CREATE TABLE t2
374 (a INT KEY,
375 b INT,
376 INDEX ind(b),
377 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE NO ACTION)
378 ENGINE=InnoDB;
379 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
380 ID FOR_NAME REF_NAME N_COLS TYPE
381 test/t2_ibfk_1 test/t2 test/t1 1 33
382 DROP TABLE t2;
383 CREATE TABLE t2
384 (a INT KEY,
385 b INT,
386 INDEX ind(b),
387 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE RESTRICT)
388 ENGINE=InnoDB;
389 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
390 ID FOR_NAME REF_NAME N_COLS TYPE
391 test/t2_ibfk_1 test/t2 test/t1 1 1
392 DROP TABLE t2;
393 CREATE TABLE t2
394 (a INT KEY,
395 b INT,
396 INDEX ind(b),
397 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE CASCADE)
398 ENGINE=InnoDB;
399 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
400 ID FOR_NAME REF_NAME N_COLS TYPE
401 test/t2_ibfk_1 test/t2 test/t1 1 6
402 DROP TABLE t2;
403 CREATE TABLE t2
404 (a INT KEY,
405 b INT,
406 INDEX ind(b),
407 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE SET NULL)
408 ENGINE=InnoDB;
409 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
410 ID FOR_NAME REF_NAME N_COLS TYPE
411 test/t2_ibfk_1 test/t2 test/t1 1 10
412 DROP TABLE t2;
413 CREATE TABLE t2
414 (a INT KEY,
415 b INT,
416 INDEX ind(b),
417 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE NO ACTION)
418 ENGINE=InnoDB;
419 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
420 ID FOR_NAME REF_NAME N_COLS TYPE
421 test/t2_ibfk_1 test/t2 test/t1 1 34
422 DROP TABLE t2;
423 CREATE TABLE t2
424 (a INT KEY,
425 b INT,
426 INDEX ind(b),
427 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE SET NULL ON UPDATE RESTRICT)
428 ENGINE=InnoDB;
429 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
430 ID FOR_NAME REF_NAME N_COLS TYPE
431 test/t2_ibfk_1 test/t2 test/t1 1 2
432 DROP TABLE t2;
433 CREATE TABLE t2
434 (a INT KEY,
435 b INT,
436 INDEX ind(b),
437 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE CASCADE)
438 ENGINE=InnoDB;
439 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
440 ID FOR_NAME REF_NAME N_COLS TYPE
441 test/t2_ibfk_1 test/t2 test/t1 1 20
442 DROP TABLE t2;
443 CREATE TABLE t2
444 (a INT KEY,
445 b INT,
446 INDEX ind(b),
447 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE SET NULL)
448 ENGINE=InnoDB;
449 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
450 ID FOR_NAME REF_NAME N_COLS TYPE
451 test/t2_ibfk_1 test/t2 test/t1 1 24
452 DROP TABLE t2;
453 CREATE TABLE t2
454 (a INT KEY,
455 b INT,
456 INDEX ind(b),
457 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE NO ACTION)
458 ENGINE=InnoDB;
459 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
460 ID FOR_NAME REF_NAME N_COLS TYPE
461 test/t2_ibfk_1 test/t2 test/t1 1 48
462 DROP TABLE t2;
463 CREATE TABLE t2
464 (a INT KEY,
465 b INT,
466 INDEX ind(b),
467 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE NO ACTION ON UPDATE RESTRICT)
468 ENGINE=InnoDB;
469 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
470 ID FOR_NAME REF_NAME N_COLS TYPE
471 test/t2_ibfk_1 test/t2 test/t1 1 16
472 DROP TABLE t2;
473 CREATE TABLE t2
474 (a INT KEY,
475 b INT,
476 INDEX ind(b),
477 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE CASCADE)
478 ENGINE=InnoDB;
479 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
480 ID FOR_NAME REF_NAME N_COLS TYPE
481 test/t2_ibfk_1 test/t2 test/t1 1 4
482 DROP TABLE t2;
483 CREATE TABLE t2
484 (a INT KEY,
485 b INT,
486 INDEX ind(b),
487 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE SET NULL)
488 ENGINE=InnoDB;
489 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
490 ID FOR_NAME REF_NAME N_COLS TYPE
491 test/t2_ibfk_1 test/t2 test/t1 1 8
492 DROP TABLE t2;
493 CREATE TABLE t2
494 (a INT KEY,
495 b INT,
496 INDEX ind(b),
497 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE NO ACTION)
498 ENGINE=InnoDB;
499 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
500 ID FOR_NAME REF_NAME N_COLS TYPE
501 test/t2_ibfk_1 test/t2 test/t1 1 32
502 DROP TABLE t2;
503 CREATE TABLE t2
504 (a INT KEY,
505 b INT,
506 INDEX ind(b),
507 FOREIGN KEY (b) REFERENCES t1(a) ON DELETE RESTRICT ON UPDATE RESTRICT)
508 ENGINE=InnoDB;
509 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN;
510 ID FOR_NAME REF_NAME N_COLS TYPE
511 test/t2_ibfk_1 test/t2 test/t1 1 0
512 DROP TABLE t2;
513 DROP TABLE t1;
514 #
515 # Bug#28581468 - RENAMING PARENT COLUMN IN A FOREIGN KEY FAILS WITH STRANGE ERROR.
516 #
517 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, INDEX idx(a)) ENGINE=InnoDB;
518 CREATE TABLE t2 (a INT KEY, b INT, INDEX ind(b), FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
519 ALTER TABLE t1 CHANGE a id INT;
520 CHECK TABLE t1;
521 Table Op Msg_type Msg_text
522 test.t1 check status OK
523 CHECK TABLE t2;
524 Table Op Msg_type Msg_text
525 test.t2 check status OK
526 DROP TABLE t1, t2;
527 CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, INDEX idx(a)) ENGINE=InnoDB;
528 CREATE TABLE t2 (a INT KEY, b INT, INDEX ind(b), FOREIGN KEY (b) REFERENCES t1(a) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
529 ALTER TABLE t1 CHANGE a id INT NOT NULL;
530 CHECK TABLE t1;
531 Table Op Msg_type Msg_text
532 test.t1 check status OK
533 CHECK TABLE t2;
534 Table Op Msg_type Msg_text
535 test.t2 check status OK
536 DROP TABLE t1, t2;
537 #
538 # Bug #27020089 "I_S.INNODB_SYS_FOREIGN LOST FOREIGN KEY INFORMATION."
539 #
540 CREATE TABLE t1(pk INT PRIMARY KEY);
541 CREATE TABLE `......................................`
542 (fk INT, FOREIGN KEY(fk) REFERENCES t1(pk));
543 SHOW CREATE TABLE `......................................`;
544 Table Create Table
545 ...................................... CREATE TABLE `......................................` (
546 `fk` int DEFAULT NULL,
547 KEY `fk` (`fk`),
548 CONSTRAINT `......................................_ibfk_1` FOREIGN KEY (`fk`) REFERENCES `t1` (`pk`)
549 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
550 SELECT id, for_name, ref_name FROM information_schema.innodb_foreign WHERE id LIKE 'test%';
551 id for_name ref_name
552 test/......................................_ibfk_1 test/...................................... test/t1
553 DROP TABLES `......................................`, t1;
554 #
555 # Bug#33053297 VIRTUAL INDEX CORRUPTED DURING CASCADE UPDATE ON CHILD TABLE
556 #
557 #Test-Case 1
558 CREATE TABLE `emails` (
559 `id` int unsigned NOT NULL AUTO_INCREMENT,
560 PRIMARY KEY (`id`)
561 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
562 ROW_FORMAT=DYNAMIC;
563 CREATE TABLE `email_stats` (
564 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
565 `email_id` int unsigned DEFAULT NULL,
566 `date_sent` datetime NOT NULL,
567 `generated_sent_date` date GENERATED ALWAYS AS
568 (concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'),
569 '-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL,
570 PRIMARY KEY (`id`),
571 KEY `IDX_ES1` (`email_id`),
572 KEY `mautic_generated_sent_date_email_id`
573 (`generated_sent_date`,`email_id`),
574 CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES
575 `emails` (`id`) ON DELETE SET NULL
576 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
577 ROW_FORMAT=DYNAMIC;
578 INSERT INTO `emails` VALUES (1);
579 INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES
580 (1,1,'2020-10-22 13:32:41');
581 SELECT * FROM `email_stats`;
582 id email_id date_sent generated_sent_date
583 1 1 2020-10-22 13:32:41 2020-10-22
584 DELETE FROM `emails`;
585 DELETE FROM `email_stats`;
586 DROP TABLE `email_stats`;
587 DROP TABLE `emails`;
588 # Test-Case 2
589 CREATE TABLE `emails` (
590 `id` int unsigned NOT NULL AUTO_INCREMENT,
591 PRIMARY KEY (`id`)
592 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
593 ROW_FORMAT=DYNAMIC;
594 CREATE TABLE `email_stats` (
595 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
596 `email_id` int unsigned DEFAULT NULL,
597 `date_sent` datetime NOT NULL,
598 `generated_sent_date` date GENERATED ALWAYS AS
599 (concat(year(`date_sent`),'-',lpad(month(`date_sent`),2,'0'),
600 '-',lpad(dayofmonth(`date_sent`),2,'0'))) VIRTUAL,
601 PRIMARY KEY (`id`),
602 KEY `IDX_ES1` (`email_id`),
603 KEY `mautic_generated_sent_date_email_id`
604 (`generated_sent_date`,`email_id`),
605 CONSTRAINT `FK_EA1` FOREIGN KEY (`email_id`) REFERENCES
606 `emails` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
607 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
608 ROW_FORMAT=DYNAMIC;
609 INSERT INTO `emails` VALUES (1);
610 INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES
611 (1,1,'2020-10-22 13:32:41');
612 UPDATE `emails` SET `id` = 2 where `id` = 1;
613 SELECT id FROM `email_stats` WHERE `generated_sent_date` IS NULL;
614 id
615 SELECT * FROM `email_stats`;
616 id email_id date_sent generated_sent_date
617 1 NULL 2020-10-22 13:32:41 2020-10-22
618 UPDATE `email_stats` SET `email_id`=2
619 WHERE DATE(`generated_sent_date`) = '2020-10-22';
620 SELECT * FROM `email_stats`;
621 id email_id date_sent generated_sent_date
622 1 2 2020-10-22 13:32:41 2020-10-22
623 DROP TABLE `email_stats`;
624 DROP TABLE `emails`;
625 # test-case 3
626 CREATE TABLE `emails` (
627 `id` int unsigned NOT NULL AUTO_INCREMENT,
628 PRIMARY KEY (`id`)
629 ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
630 ROW_FORMAT=DYNAMIC;
631 CREATE TABLE `email_stats` (
632 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
633 `email_id` int unsigned DEFAULT NULL,
634 `date_sent` datetime NOT NULL,
635 `generated_sent_email` varchar(20) GENERATED ALWAYS AS
636 (CONCAT(YEAR(`date_sent`),'-', COALESCE(`email_id`, ' '))) VIRTUAL,
637 PRIMARY KEY (`id`),
638 KEY `idx_es1` (`email_id`),
639 KEY `mautic_generated_sent_date_email`
640 (`generated_sent_email`,`email_id`),
641 CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES
642 `emails` (`id`) ON DELETE SET NULL
643 ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
644 ROW_FORMAT=DYNAMIC;
645 INSERT INTO `emails` VALUES (1);
646 INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES
647 (1,1,'2020-10-22 13:32:41');
648 SELECT * FROM `email_stats`;
649 id email_id date_sent generated_sent_email
650 1 1 2020-10-22 13:32:41 2020-1
651 SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1';
652 date_sent
653 2020-10-22 13:32:41
654 DELETE FROM `emails`;
655 SELECT * FROM `email_stats`;
656 id email_id date_sent generated_sent_email
657 1 NULL 2020-10-22 13:32:41 2020-
658 SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';
659 date_sent
660 2020-10-22 13:32:41
661 DROP TABLE `email_stats`;
662 DROP TABLE `emails`;
663 # test-case 4
664 CREATE TABLE `emails` (
665 `id` int unsigned NOT NULL AUTO_INCREMENT,
666 PRIMARY KEY (`id`)
667 ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
668 ROW_FORMAT=DYNAMIC;
669 CREATE TABLE `email_stats` (
670 `id` bigint unsigned NOT NULL AUTO_INCREMENT,
671 `email_id` int unsigned DEFAULT NULL,
672 `date_sent` datetime NOT NULL,
673 `generated_sent_email` varchar(20) GENERATED ALWAYS AS
674 (CONCAT(YEAR(`date_sent`),'-', COALESCE(`email_id`, ' '))) VIRTUAL,
675 PRIMARY KEY (`id`),
676 KEY `idx_es1` (`email_id`),
677 KEY `mautic_generated_sent_date_email`
678 (`generated_sent_email`,`email_id`),
679 CONSTRAINT `fk_ea1` FOREIGN KEY (`email_id`) REFERENCES
680 `emails` (`id`) ON UPDATE SET NULL
681 ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
682 ROW_FORMAT=DYNAMIC;
683 INSERT INTO `emails` VALUES (1);
684 INSERT INTO `email_stats` (`id`, `email_id`, `date_sent`) VALUES
685 (1,1,'2020-10-22 13:32:41');
686 SELECT * FROM `email_stats`;
687 id email_id date_sent generated_sent_email
688 1 1 2020-10-22 13:32:41 2020-1
689 SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-1';
690 date_sent
691 2020-10-22 13:32:41
692 UPDATE `emails` SET `id` = 2 WHERE `id` = 1;
693 SELECT * FROM `email_stats`;
694 id email_id date_sent generated_sent_email
695 1 NULL 2020-10-22 13:32:41 2020-
696 SELECT `date_sent` FROM `email_stats` WHERE `generated_sent_email` = '2020-';
697 date_sent
698 2020-10-22 13:32:41
699 DROP TABLE `email_stats`;
700 DROP TABLE `emails`;