Debian Codegraph @

mysql-test/suite/innodb/r/innodb-index-online-fk.result

raw
1 CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
2 INSERT INTO parent VALUES(1,2),(2,3);
3 CREATE INDEX tb ON parent(b);
4 INSERT INTO parent VALUES(10,20),(20,30);
5 CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
6 CREATE INDEX tb ON child(a2);
7 INSERT INTO child VALUES(10,20);
8 ALTER TABLE child ADD FOREIGN KEY(a2) REFERENCES parent(b),
9 ALGORITHM = INPLACE;
10 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY.
11 SET foreign_key_checks = 0;
12 ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
13 REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
14 ALGORITHM = INPLACE;
15 SELECT * FROM information_schema.INNODB_FOREIGN ORDER BY ID;
16 ID FOR_NAME REF_NAME N_COLS TYPE
17 test/fk_1 test/child test/parent 1 6
18 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
19 ID FOR_COL_NAME REF_COL_NAME POS
20 test/fk_1 a2 b 1
21 ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
22 REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
23 ALGORITHM = INPLACE;
24 ERROR HY000: Duplicate foreign key constraint name 'fk_1'
25 SET foreign_key_checks = 1;
26 INSERT INTO child VALUES(1,2),(2,3);
27 INSERT INTO child VALUES(4,4);
28 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE)
29 SELECT * FROM parent;
30 a b
31 1 2
32 2 3
33 10 20
34 20 30
35 SET foreign_key_checks = 0;
36 ALTER TABLE child ADD CONSTRAINT fk_20 FOREIGN KEY (a1, a2)
37 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
38 ALGORITHM = INPLACE;
39 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
40 SHOW WARNINGS;
41 Level Code Message
42 Error 1822 Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
43 SHOW ERRORS;
44 Level Code Message
45 Error 1822 Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
46 CREATE INDEX idx1 on parent(a, b);
47 ALTER TABLE child ADD CONSTRAINT fk_10 FOREIGN KEY (a1, a2)
48 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
49 ALGORITHM = INPLACE;
50 ALTER TABLE child ADD CONSTRAINT fk_2 FOREIGN KEY (a1, a2)
51 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ADD INDEX idx1(a1,a2),
52 ALGORITHM = INPLACE;
53 ALTER TABLE child ADD CONSTRAINT fk_3 FOREIGN KEY (a1, a2)
54 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE;
55 SELECT * FROM information_schema.INNODB_FOREIGN ORDER BY ID;
56 ID FOR_NAME REF_NAME N_COLS TYPE
57 test/fk_1 test/child test/parent 1 6
58 test/fk_10 test/child test/parent 2 5
59 test/fk_2 test/child test/parent 2 5
60 test/fk_3 test/child test/parent 2 5
61 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
62 ID FOR_COL_NAME REF_COL_NAME POS
63 test/fk_1 a2 b 1
64 test/fk_10 a1 a 1
65 test/fk_10 a2 b 2
66 test/fk_2 a1 a 1
67 test/fk_2 a2 b 2
68 test/fk_3 a1 a 1
69 test/fk_3 a2 b 2
70 SET foreign_key_checks = 1;
71 INSERT INTO child VALUES(5,4);
72 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE)
73 SHOW CREATE TABLE child;
74 Table Create Table
75 child CREATE TABLE `child` (
76 `a1` int NOT NULL,
77 `a2` int DEFAULT NULL,
78 PRIMARY KEY (`a1`),
79 KEY `tb` (`a2`),
80 KEY `idx1` (`a1`,`a2`),
81 CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
82 CONSTRAINT `fk_10` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE,
83 CONSTRAINT `fk_2` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE,
84 CONSTRAINT `fk_3` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
86 DELETE FROM parent where a = 1;
87 SELECT * FROM child;
88 a1 a2
89 1 NULL
90 2 3
91 10 20
92 SET foreign_key_checks = 0;
93 SET DEBUG = '+d,innodb_test_open_ref_fail';
94 ALTER TABLE child ADD CONSTRAINT fk_4 FOREIGN KEY (a1, a2)
95 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
96 ALGORITHM = INPLACE;
97 SET DEBUG = '-d,innodb_test_open_ref_fail';
98 SELECT * FROM information_schema.INNODB_FOREIGN ORDER BY ID;
99 ID FOR_NAME REF_NAME N_COLS TYPE
100 test/fk_1 test/child test/parent 1 6
101 test/fk_10 test/child test/parent 2 5
102 test/fk_2 test/child test/parent 2 5
103 test/fk_3 test/child test/parent 2 5
104 test/fk_4 test/child test/parent 2 5
105 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
106 ID FOR_COL_NAME REF_COL_NAME POS
107 test/fk_1 a2 b 1
108 test/fk_10 a1 a 1
109 test/fk_10 a2 b 2
110 test/fk_2 a1 a 1
111 test/fk_2 a2 b 2
112 test/fk_3 a1 a 1
113 test/fk_3 a2 b 2
114 test/fk_4 a1 a 1
115 test/fk_4 a2 b 2
116 SELECT t2.name, t1.name FROM information_schema.innodb_columns t1, information_schema.innodb_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
117 name name
118 test/child a1
119 test/child a2
120 SELECT NAME FROM information_schema.INNODB_TABLES WHERE NAME not like 'sys\/%'
121 AND name NOT LIKE 'mysql\/%';
122 NAME
123 mtr/test_suppressions
124 mtr/asserted_test_suppressions
125 mtr/test_ignored_global_suppressions
126 mtr/global_suppressions
127 test/parent
128 test/child
129 INSERT INTO child VALUES(5,4);
130 SET foreign_key_checks = 1;
131 INSERT INTO child VALUES(6,5);
132 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE)
133 SET foreign_key_checks = 0;
134 CREATE TABLE `#parent` (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
135 CREATE INDEX tb ON `#parent`(a, b);
136 CREATE TABLE `#child` (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
137 CREATE INDEX tb ON `#child`(a1, a2);
138 SET DEBUG = '+d,innodb_test_no_foreign_idx';
139 ALTER TABLE `#child` ADD CONSTRAINT fk_40 FOREIGN KEY (a1, a2)
140 REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
141 ALGORITHM = INPLACE;
142 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_40' in the foreign table '#child'
143 SHOW ERRORS;
144 Level Code Message
145 Error 1821 Failed to add the foreign key constraint. Missing index for constraint 'fk_40' in the foreign table '#child'
146 SET DEBUG = '-d,innodb_test_no_foreign_idx';
147 SELECT * FROM information_schema.INNODB_FOREIGN ORDER BY ID;
148 ID FOR_NAME REF_NAME N_COLS TYPE
149 test/fk_1 test/child test/parent 1 6
150 test/fk_10 test/child test/parent 2 5
151 test/fk_2 test/child test/parent 2 5
152 test/fk_3 test/child test/parent 2 5
153 test/fk_4 test/child test/parent 2 5
154 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
155 ID FOR_COL_NAME REF_COL_NAME POS
156 test/fk_1 a2 b 1
157 test/fk_10 a1 a 1
158 test/fk_10 a2 b 2
159 test/fk_2 a1 a 1
160 test/fk_2 a2 b 2
161 test/fk_3 a1 a 1
162 test/fk_3 a2 b 2
163 test/fk_4 a1 a 1
164 test/fk_4 a2 b 2
165 SET DEBUG = '+d,innodb_test_no_reference_idx';
166 ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2)
167 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
168 ALGORITHM = INPLACE;
169 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_42' in the referenced table 'parent'
170 SHOW ERRORS;
171 Level Code Message
172 Error 1822 Failed to add the foreign key constraint. Missing index for constraint 'fk_42' in the referenced table 'parent'
173 SET DEBUG = '-d,innodb_test_no_reference_idx';
174 SET DEBUG = '+d,innodb_test_wrong_fk_option';
175 ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2)
176 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
177 ALGORITHM = INPLACE;
178 ERROR HY000: Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'test/fk_42'
179 SET DEBUG = '-d,innodb_test_wrong_fk_option';
180 SELECT * FROM information_schema.INNODB_FOREIGN ORDER BY ID;
181 ID FOR_NAME REF_NAME N_COLS TYPE
182 test/fk_1 test/child test/parent 1 6
183 test/fk_10 test/child test/parent 2 5
184 test/fk_2 test/child test/parent 2 5
185 test/fk_3 test/child test/parent 2 5
186 test/fk_4 test/child test/parent 2 5
187 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
188 ID FOR_COL_NAME REF_COL_NAME POS
189 test/fk_1 a2 b 1
190 test/fk_10 a1 a 1
191 test/fk_10 a2 b 2
192 test/fk_2 a1 a 1
193 test/fk_2 a2 b 2
194 test/fk_3 a1 a 1
195 test/fk_3 a2 b 2
196 test/fk_4 a1 a 1
197 test/fk_4 a2 b 2
198 SET DEBUG = '+d,innodb_test_cannot_add_fk_system';
199 ALTER TABLE `#child` ADD CONSTRAINT fk_43 FOREIGN KEY (a1, a2)
200 REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
201 ALGORITHM = INPLACE;
202 ERROR HY000: Failed to add the foreign key constraint 'test/fk_43' to system tables
203 SHOW ERRORS;
204 Level Code Message
205 Error 1823 Failed to add the foreign key constraint 'test/fk_43' to system tables
206 SET DEBUG = '-d,innodb_test_cannot_add_fk_system';
207 DROP TABLE `#child`;
208 DROP TABLE `#parent`;
209 SET foreign_key_checks = 0;
210 ALTER TABLE child ADD CONSTRAINT fk_5 FOREIGN KEY (a2) REFERENCES parent(b)
211 ON DELETE SET NULL ON UPDATE CASCADE,
212 ADD CONSTRAINT fk_6 FOREIGN KEY (a1, a2)
213 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
214 ALGORITHM = INPLACE;
215 SELECT * FROM information_schema.INNODB_FOREIGN ORDER BY ID;
216 ID FOR_NAME REF_NAME N_COLS TYPE
217 test/fk_1 test/child test/parent 1 6
218 test/fk_10 test/child test/parent 2 5
219 test/fk_2 test/child test/parent 2 5
220 test/fk_3 test/child test/parent 2 5
221 test/fk_4 test/child test/parent 2 5
222 test/fk_5 test/child test/parent 1 6
223 test/fk_6 test/child test/parent 2 5
224 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
225 ID FOR_COL_NAME REF_COL_NAME POS
226 test/fk_1 a2 b 1
227 test/fk_10 a1 a 1
228 test/fk_10 a2 b 2
229 test/fk_2 a1 a 1
230 test/fk_2 a2 b 2
231 test/fk_3 a1 a 1
232 test/fk_3 a2 b 2
233 test/fk_4 a1 a 1
234 test/fk_4 a2 b 2
235 test/fk_5 a2 b 1
236 test/fk_6 a1 a 1
237 test/fk_6 a2 b 2
238 DROP TABLE child;
239 DROP TABLE parent;
240 CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
241 INSERT INTO parent VALUES(1,2),(2,3);
242 CREATE INDEX tb ON parent(b);
243 INSERT INTO parent VALUES(10,20),(20,30);
244 CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
245 CREATE INDEX tb ON child(a2);
246 INSERT INTO child VALUES(10,20);
247 SET foreign_key_checks = 0;
248 ALTER TABLE child DROP INDEX tb, ADD CONSTRAINT fk_4 FOREIGN KEY (a2)
249 REFERENCES parent(b) ON DELETE CASCADE ON UPDATE CASCADE,
250 ALGORITHM = INPLACE;
251 SHOW CREATE TABLE child;
252 Table Create Table
253 child CREATE TABLE `child` (
254 `a1` int NOT NULL,
255 `a2` int DEFAULT NULL,
256 PRIMARY KEY (`a1`),
257 KEY `fk_4` (`a2`),
258 CONSTRAINT `fk_4` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE CASCADE
259 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
260 SELECT * FROM information_schema.INNODB_FOREIGN ORDER BY ID;
261 ID FOR_NAME REF_NAME N_COLS TYPE
262 test/fk_4 test/child test/parent 1 5
263 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
264 ID FOR_COL_NAME REF_COL_NAME POS
265 test/fk_4 a2 b 1
266 SET foreign_key_checks = 1;
267 DROP TABLE child;
268 DROP TABLE parent;
269 CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
270 INSERT INTO parent VALUES(1,2),(2,3);
271 CREATE INDEX tb ON parent(b);
272 INSERT INTO parent VALUES(10,20),(20,30);
273 CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
274 CREATE INDEX tb ON child(a2);
275 SET foreign_key_checks = 0;
276 ALTER TABLE child CHANGE a2 a3 INT,
277 ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
278 ON DELETE SET NULL ON UPDATE CASCADE,
279 ALGORITHM = INPLACE;
280 ERROR 42000: Key column 'a2' doesn't exist in table
281 ALTER TABLE child CHANGE a2 a3 INT,
282 ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
283 ON DELETE SET NULL ON UPDATE CASCADE,
284 ALGORITHM = INPLACE;
285 DROP TABLE child;
286 DROP TABLE parent;
287 CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
288 INSERT INTO parent VALUES(1,2),(2,3);
289 CREATE INDEX tb ON parent(b);
290 INSERT INTO parent VALUES(10,20),(20,30);
291 CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
292 CREATE INDEX tb ON child(a2);
293 SET foreign_key_checks = 0;
294 SET DEBUG = '+d,innodb_test_cannot_add_fk_system';
295 ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
296 ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
297 ON DELETE SET NULL ON UPDATE CASCADE,
298 ALGORITHM = INPLACE;
299 ERROR HY000: Failed to add the foreign key constraint 'test/fk_1' to system tables
300 SET DEBUG = '-d,innodb_test_cannot_add_fk_system';
301 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN ORDER BY ID;
302 ID FOR_NAME REF_NAME N_COLS TYPE
303 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
304 ID FOR_COL_NAME REF_COL_NAME POS
305 SELECT t2.name, t1.name FROM information_schema.innodb_columns t1, information_schema.innodb_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
306 name name
307 test/child a1
308 test/child a2
309 SELECT NAME FROM information_schema.INNODB_TABLES WHERE NAME not like 'sys\/%'
310 AND name NOT LIKE 'mysql\/%';
311 NAME
312 mtr/test_suppressions
313 mtr/asserted_test_suppressions
314 mtr/test_ignored_global_suppressions
315 mtr/global_suppressions
316 test/parent
317 test/child
318 ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
319 ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
320 ON DELETE SET NULL ON UPDATE CASCADE,
321 ALGORITHM = INPLACE;
322 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN ORDER BY ID;
323 ID FOR_NAME REF_NAME N_COLS TYPE
324 test/fk_1 test/child test/parent 1 6
325 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
326 ID FOR_COL_NAME REF_COL_NAME POS
327 test/fk_1 a2 b 1
328 SELECT t2.name, t1.name FROM information_schema.innodb_columns t1, information_schema.innodb_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
329 name name
330 test/child a2
331 test/child a3
332 SELECT NAME FROM information_schema.INNODB_TABLES WHERE NAME not like 'sys\/%'
333 AND name NOT LIKE 'mysql\/%';
334 NAME
335 mtr/test_suppressions
336 mtr/asserted_test_suppressions
337 mtr/test_ignored_global_suppressions
338 mtr/global_suppressions
339 test/parent
340 test/child
341 SHOW CREATE TABLE child;
342 Table Create Table
343 child CREATE TABLE `child` (
344 `a3` int NOT NULL,
345 `a2` int DEFAULT NULL,
346 PRIMARY KEY (`a3`),
347 KEY `tb` (`a2`),
348 CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
349 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
350 DROP TABLE child;
351 CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
352 ALTER TABLE child ADD PRIMARY KEY idx (a1),
353 ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
354 ON DELETE SET NULL ON UPDATE CASCADE,
355 ALGORITHM = INPLACE;
356 SELECT * from information_schema.INNODB_FOREIGN ORDER BY ID;
357 ID FOR_NAME REF_NAME N_COLS TYPE
358 test/fk_1 test/child test/parent 1 6
359 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
360 ID FOR_COL_NAME REF_COL_NAME POS
361 test/fk_1 a2 b 1
362 SELECT t2.name, t1.name FROM information_schema.innodb_columns t1, information_schema.innodb_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
363 name name
364 test/child a1
365 test/child a2
366 SELECT NAME FROM information_schema.INNODB_TABLES WHERE NAME not like 'sys\/%'
367 AND name NOT LIKE 'mysql\/%';
368 NAME
369 mtr/test_suppressions
370 mtr/asserted_test_suppressions
371 mtr/test_ignored_global_suppressions
372 mtr/global_suppressions
373 test/parent
374 test/child
375 SHOW CREATE TABLE child;
376 Table Create Table
377 child CREATE TABLE `child` (
378 `a1` int NOT NULL,
379 `a2` int DEFAULT NULL,
380 PRIMARY KEY (`a1`),
381 KEY `fk_1` (`a2`),
382 CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
383 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
384 DROP TABLE child;
385 CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
386 ALTER TABLE child CHANGE a1 a3 INT,
387 ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
388 ON DELETE SET NULL ON UPDATE CASCADE,
389 ALGORITHM = INPLACE;
390 SELECT * from information_schema.INNODB_FOREIGN ORDER BY ID;
391 ID FOR_NAME REF_NAME N_COLS TYPE
392 test/fk_1 test/child test/parent 1 6
393 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
394 ID FOR_COL_NAME REF_COL_NAME POS
395 test/fk_1 a3 b 1
396 SELECT t2.name, t1.name FROM information_schema.innodb_columns t1, information_schema.innodb_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
397 name name
398 test/child a2
399 test/child a3
400 SELECT NAME FROM information_schema.INNODB_TABLES WHERE NAME not like 'sys\/%'
401 AND name NOT LIKE 'mysql\/%';
402 NAME
403 mtr/test_suppressions
404 mtr/asserted_test_suppressions
405 mtr/test_ignored_global_suppressions
406 mtr/global_suppressions
407 test/parent
408 test/child
409 SHOW CREATE TABLE child;
410 Table Create Table
411 child CREATE TABLE `child` (
412 `a3` int DEFAULT NULL,
413 `a2` int DEFAULT NULL,
414 KEY `fk_1` (`a3`),
415 CONSTRAINT `fk_1` FOREIGN KEY (`a3`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
416 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
417 DROP TABLE child;
418 CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
419 ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
420 ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
421 ON DELETE SET NULL ON UPDATE CASCADE,
422 ALGORITHM = INPLACE;
423 ERROR HY000: Column 'a3' cannot be NOT NULL: needed in a foreign key constraint 'fk_1' SET NULL
424 DROP TABLE parent;
425 DROP TABLE child;
426 CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL, c INT) ENGINE = InnoDB;
427 INSERT INTO parent VALUES(1,2,3),(2,3,4);
428 CREATE INDEX tb ON parent(b);
429 CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB;
430 CREATE INDEX tb ON child(a2);
431 ALTER TABLE child
432 ADD CONSTRAINT fk_a FOREIGN KEY (a2) REFERENCES parent(b)
433 ON DELETE SET NULL ON UPDATE CASCADE,
434 ALGORITHM = INPLACE;
435 ALTER TABLE child
436 ADD CONSTRAINT fk_b FOREIGN KEY (a1) REFERENCES parent(a),
437 ALGORITHM = INPLACE;
438 ALTER TABLE child CHANGE a2 a2_new INT, CHANGE a1 a1_new INT;
439 SHOW CREATE TABLE child;
440 Table Create Table
441 child CREATE TABLE `child` (
442 `a1_new` int DEFAULT NULL,
443 `a2_new` int DEFAULT NULL,
444 `a3` int DEFAULT NULL,
445 KEY `tb` (`a2_new`),
446 KEY `fk_b` (`a1_new`),
447 CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
448 CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`)
449 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
450 SELECT * from information_schema.INNODB_FOREIGN ORDER BY ID;
451 ID FOR_NAME REF_NAME N_COLS TYPE
452 test/fk_a test/child test/parent 1 6
453 test/fk_b test/child test/parent 1 48
454 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
455 ID FOR_COL_NAME REF_COL_NAME POS
456 test/fk_a a2_new b 1
457 test/fk_b a1_new a 1
458 ALTER TABLE child
459 ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b),
460 ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a),
461 ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c),
462 ALGORITHM = INPLACE;
463 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_new_3' in the referenced table 'parent'
464 SHOW CREATE TABLE child;
465 Table Create Table
466 child CREATE TABLE `child` (
467 `a1_new` int DEFAULT NULL,
468 `a2_new` int DEFAULT NULL,
469 `a3` int DEFAULT NULL,
470 KEY `tb` (`a2_new`),
471 KEY `fk_b` (`a1_new`),
472 CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
473 CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`)
474 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
475 SELECT * from information_schema.INNODB_FOREIGN ORDER BY ID;
476 ID FOR_NAME REF_NAME N_COLS TYPE
477 test/fk_a test/child test/parent 1 6
478 test/fk_b test/child test/parent 1 48
479 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
480 ID FOR_COL_NAME REF_COL_NAME POS
481 test/fk_a a2_new b 1
482 test/fk_b a1_new a 1
483 ALTER TABLE child
484 ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b),
485 ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a),
486 ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a),
487 ALGORITHM = INPLACE;
488 SHOW CREATE TABLE child;
489 Table Create Table
490 child CREATE TABLE `child` (
491 `a1_new` int DEFAULT NULL,
492 `a2_new` int DEFAULT NULL,
493 `a3` int DEFAULT NULL,
494 KEY `tb` (`a2_new`),
495 KEY `fk_new_1` (`a1_new`),
496 KEY `fk_new_3` (`a3`),
497 CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
498 CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`),
499 CONSTRAINT `fk_new_1` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`b`),
500 CONSTRAINT `fk_new_2` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`a`),
501 CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`)
502 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
503 SELECT * from information_schema.INNODB_FOREIGN ORDER BY ID;
504 ID FOR_NAME REF_NAME N_COLS TYPE
505 test/fk_a test/child test/parent 1 6
506 test/fk_b test/child test/parent 1 48
507 test/fk_new_1 test/child test/parent 1 48
508 test/fk_new_2 test/child test/parent 1 48
509 test/fk_new_3 test/child test/parent 1 48
510 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
511 ID FOR_COL_NAME REF_COL_NAME POS
512 test/fk_a a2_new b 1
513 test/fk_b a1_new a 1
514 test/fk_new_1 a1_new b 1
515 test/fk_new_2 a2_new a 1
516 test/fk_new_3 a3 a 1
517 DROP TABLE child;
518 CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB;
519 CREATE INDEX tb ON child(a2);
520 ALTER TABLE child ADD PRIMARY KEY idx (a1),
521 ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b),
522 ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a),
523 ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c),
524 ALGORITHM = INPLACE;
525 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_new_3' in the referenced table 'parent'
526 SHOW CREATE TABLE child;
527 Table Create Table
528 child CREATE TABLE `child` (
529 `a1` int NOT NULL,
530 `a2` int DEFAULT NULL,
531 `a3` int DEFAULT NULL,
532 KEY `tb` (`a2`)
533 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
534 SELECT * from information_schema.INNODB_FOREIGN ORDER BY ID;
535 ID FOR_NAME REF_NAME N_COLS TYPE
536 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
537 ID FOR_COL_NAME REF_COL_NAME POS
538 ALTER TABLE child ADD PRIMARY KEY idx (a1),
539 ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b),
540 ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a),
541 ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a),
542 ALGORITHM = INPLACE;
543 SHOW CREATE TABLE child;
544 Table Create Table
545 child CREATE TABLE `child` (
546 `a1` int NOT NULL,
547 `a2` int DEFAULT NULL,
548 `a3` int DEFAULT NULL,
549 PRIMARY KEY (`a1`),
550 KEY `tb` (`a2`),
551 KEY `fk_new_3` (`a3`),
552 CONSTRAINT `fk_new_1` FOREIGN KEY (`a1`) REFERENCES `parent` (`b`),
553 CONSTRAINT `fk_new_2` FOREIGN KEY (`a2`) REFERENCES `parent` (`a`),
554 CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`)
555 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
556 SELECT * from information_schema.INNODB_FOREIGN ORDER BY ID;
557 ID FOR_NAME REF_NAME N_COLS TYPE
558 test/fk_new_1 test/child test/parent 1 48
559 test/fk_new_2 test/child test/parent 1 48
560 test/fk_new_3 test/child test/parent 1 48
561 SELECT * FROM information_schema.INNODB_FOREIGN_COLS ORDER BY ID, POS;
562 ID FOR_COL_NAME REF_COL_NAME POS
563 test/fk_new_1 a1 b 1
564 test/fk_new_2 a2 a 1
565 test/fk_new_3 a3 a 1
566 SET foreign_key_checks = 1;
567 DROP TABLE child;
568 DROP TABLE parent;
569 CREATE TABLE Parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
570 INSERT INTO Parent VALUES(1,2),(2,3);
571 CREATE INDEX tb ON Parent(b);
572 INSERT INTO Parent VALUES(10,20),(20,30);
573 CREATE TABLE Child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
574 CREATE INDEX tb ON Child(a2);
575 INSERT INTO Child VALUES(10,20);
576 SET foreign_key_checks = 0;
577 ALTER TABLE Child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
578 REFERENCES Parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
579 ALGORITHM = INPLACE;
580 DROP TABLE Child;
581 DROP TABLE Parent;
582 CREATE TABLE `t2`(a int,c int,d int) ENGINE=INNODB;
583 CREATE TABLE `t3`(a int,c int,d int) ENGINE=INNODB;
584 CREATE INDEX idx ON t3(a);
585 ALTER TABLE `t2` ADD CONSTRAINT `fw` FOREIGN KEY (`c`) REFERENCES t3 (a);
586 ALTER TABLE `t2` ADD CONSTRAINT `e` foreign key (`d`) REFERENCES t3(a);
587 ALTER TABLE `t3` ADD CONSTRAINT `e` foreign key (`c`) REFERENCES `t2`(`c`) ON UPDATE SET NULL;
588 ERROR HY000: Duplicate foreign key constraint name 'e'
589 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN ORDER BY ID;
590 ID FOR_NAME REF_NAME N_COLS TYPE
591 test/e test/t2 test/t3 1 48
592 test/fw test/t2 test/t3 1 48
593 SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS ORDER BY ID, POS;
594 ID FOR_COL_NAME REF_COL_NAME POS
595 test/e d a 1
596 test/fw c a 1
597 DROP TABLE t2;
598 DROP TABLE t3;
599 # Bug #17449901 TABLE DISAPPEARS WHEN ALTERING
600 # WITH FOREIGN KEY CHECKS OFF
601 create table t1(f1 int,primary key(f1))engine=innodb;
602 create table t2(f2 int,f3 int,key t(f2,f3),foreign key(f2) references t1(f1))engine=innodb;
603 SET foreign_key_checks=0;
604 drop index t on t2;
605 ERROR HY000: Cannot drop index 't': needed in a foreign key constraint
606 drop table t2;
607 drop table t1;
608 create table t1(f1 int ,primary key(f1))engine=innodb;
609 create table t2(f2 int,f3 int, key t(f2),foreign key(f2) references t1(f1))engine=innodb;
610 SET foreign_key_checks = 0;
611 alter table t2 drop key t,algorithm=inplace;
612 ERROR HY000: Cannot drop index 't': needed in a foreign key constraint
613 show create table t2;
614 Table Create Table
615 t2 CREATE TABLE `t2` (
616 `f2` int DEFAULT NULL,
617 `f3` int DEFAULT NULL,
618 KEY `t` (`f2`),
619 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
620 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
621 drop table t2;
622 drop table t1;
623 create table t1(f1 int ,primary key(f1))engine=innodb;
624 create table t2(f2 int,f3 int, key t(f2),key t1(f2,f3),
625 foreign key(f2) references t1(f1))engine=innodb;
626 SET foreign_key_checks = 0;
627 alter table t2 drop key t,algorithm=inplace;
628 show create table t2;
629 Table Create Table
630 t2 CREATE TABLE `t2` (
631 `f2` int DEFAULT NULL,
632 `f3` int DEFAULT NULL,
633 KEY `t1` (`f2`,`f3`),
634 CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
635 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
636 drop table t2;
637 drop table t1;