Debian Codegraph @

mysql-test/suite/innodb/t/innodb-index-online-fk.test

raw
1 --source include/have_innodb.inc
2 --source include/have_debug.inc
3
4 CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
5 INSERT INTO parent VALUES(1,2),(2,3);
6
7 CREATE INDEX tb ON parent(b);
8
9 INSERT INTO parent VALUES(10,20),(20,30);
10
11 CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
12
13 CREATE INDEX tb ON child(a2);
14
15 INSERT INTO child VALUES(10,20);
16
17 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
18 ALTER TABLE child ADD FOREIGN KEY(a2) REFERENCES parent(b),
19 ALGORITHM = INPLACE;
20
21 SET foreign_key_checks = 0;
22
23 ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
24 REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
25 ALGORITHM = INPLACE;
26
27 SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
28
29 SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
30
31 # duplicated foreign key name
32 --error ER_DUP_CONSTRAINT_NAME
33 ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
34 REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
35 ALGORITHM = INPLACE;
36
37 SET foreign_key_checks = 1;
38
39 INSERT INTO child VALUES(1,2),(2,3);
40
41 --error ER_NO_REFERENCED_ROW_2
42 INSERT INTO child VALUES(4,4);
43
44 SELECT * FROM parent;
45
46 SET foreign_key_checks = 0;
47
48 # This would fail. No corresponding index
49 --error ER_FK_NO_INDEX_PARENT
50 ALTER TABLE child ADD CONSTRAINT fk_20 FOREIGN KEY (a1, a2)
51 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
52 ALGORITHM = INPLACE;
53
54 SHOW WARNINGS;
55
56 SHOW ERRORS;
57
58 CREATE INDEX idx1 on parent(a, b);
59
60 ALTER TABLE child ADD CONSTRAINT fk_10 FOREIGN KEY (a1, a2)
61 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
62 ALGORITHM = INPLACE;
63
64 # This should be successful, as we added the index
65 ALTER TABLE child ADD CONSTRAINT fk_2 FOREIGN KEY (a1, a2)
66 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ADD INDEX idx1(a1,a2),
67 ALGORITHM = INPLACE;
68
69 ALTER TABLE child ADD CONSTRAINT fk_3 FOREIGN KEY (a1, a2)
70 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE;
71
72 SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
73
74 SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
75
76 SET foreign_key_checks = 1;
77
78 --error ER_NO_REFERENCED_ROW_2
79 INSERT INTO child VALUES(5,4);
80
81 SHOW CREATE TABLE child;
82
83 DELETE FROM parent where a = 1;
84
85 SELECT * FROM child;
86
87 # Now test referenced table cannot be opened. This should work fine
88 # when foreign_key_checks is set to 0
89
90 SET foreign_key_checks = 0;
91
92 # This is to test the scenario we cannot open the referenced table.
93 # Since foreign_key_checks is set to 0, the foreign key should still
94 # be added.
95 SET @saved_debug_dbug = @@SESSION.debug_dbug;
96 SET DEBUG_DBUG = '+d,innodb_test_open_ref_fail';
97 ALTER TABLE child ADD CONSTRAINT fk_4 FOREIGN KEY (a1, a2)
98 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
99 ALGORITHM = INPLACE;
100 SET DEBUG_DBUG = @saved_debug_dbug;
101
102 SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
103
104 SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
105
106 SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
107
108 SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
109
110 # this should succeed, since we disabled the foreign key check
111 INSERT INTO child VALUES(5,4);
112
113 SET foreign_key_checks = 1;
114
115 --error ER_NO_REFERENCED_ROW_2
116 INSERT INTO child VALUES(6,5);
117
118 SET foreign_key_checks = 0;
119
120 # Create some table with 'funny' characters, for testing the
121 # error message
122 CREATE TABLE `#parent` (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
123
124 CREATE INDEX tb ON `#parent`(a, b);
125
126 CREATE TABLE `#child` (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
127
128 CREATE INDEX tb ON `#child`(a1, a2);
129
130 # This is to test the scenario no foreign index, alter table should fail
131 SET DEBUG_DBUG = '+d,innodb_test_no_foreign_idx';
132 --error ER_FK_NO_INDEX_CHILD,
133 ALTER TABLE `#child` ADD CONSTRAINT fk_40 FOREIGN KEY (a1, a2)
134 REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
135 ALGORITHM = INPLACE;
136 SET DEBUG_DBUG = @saved_debug_dbug;
137
138 SHOW ERRORS;
139
140 SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
141
142 SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
143
144 # This is to test the scenario no index on referenced table,
145 # alter table should fail
146 SET DEBUG_DBUG = '+d,innodb_test_no_reference_idx';
147 --error ER_FK_NO_INDEX_PARENT,
148 ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2)
149 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
150 ALGORITHM = INPLACE;
151 SET DEBUG_DBUG = @saved_debug_dbug;
152
153 SHOW ERRORS;
154
155 # This is to test the scenario no index on referenced table,
156 # alter table should fail
157 SET DEBUG_DBUG = '+d,innodb_test_wrong_fk_option';
158 --error ER_FK_INCORRECT_OPTION
159 ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2)
160 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
161 ALGORITHM = INPLACE;
162 SET DEBUG_DBUG = @saved_debug_dbug;
163
164 SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
165
166 SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
167
168 # This is to test the scenario cannot add fk to the system table,
169 # alter table should fail
170 SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system';
171 --error ER_FK_FAIL_ADD_SYSTEM
172 ALTER TABLE `#child` ADD CONSTRAINT fk_43 FOREIGN KEY (a1, a2)
173 REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
174 ALGORITHM = INPLACE;
175 SET DEBUG_DBUG = @saved_debug_dbug;
176
177 SHOW ERRORS;
178
179 DROP TABLE `#child`;
180 DROP TABLE `#parent`;
181
182 # Now test add multiple foreign key constrain in a single clause
183 SET foreign_key_checks = 0;
184
185 ALTER TABLE child ADD CONSTRAINT fk_5 FOREIGN KEY (a2) REFERENCES parent(b)
186 ON DELETE SET NULL ON UPDATE CASCADE,
187 ADD CONSTRAINT fk_6 FOREIGN KEY (a1, a2)
188 REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
189 ALGORITHM = INPLACE;
190
191 SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
192
193 SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
194
195 DROP TABLE child;
196 DROP TABLE parent;
197
198 # Test a case where child's foreign key index is being dropped in the
199 # same clause of adding the foreign key. In theory, MySQL will
200 # automatically create a new index to meet the index requirement
201 CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
202
203 INSERT INTO parent VALUES(1,2),(2,3);
204
205 CREATE INDEX tb ON parent(b);
206
207 INSERT INTO parent VALUES(10,20),(20,30);
208
209 CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
210
211 CREATE INDEX tb ON child(a2);
212
213 INSERT INTO child VALUES(10,20);
214
215 SET foreign_key_checks = 0;
216
217 ALTER TABLE child DROP INDEX tb, ADD CONSTRAINT fk_4 FOREIGN KEY (a2)
218 REFERENCES parent(b) ON DELETE CASCADE ON UPDATE CASCADE,
219 ALGORITHM = INPLACE;
220
221 SHOW CREATE TABLE child;
222
223 SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
224
225 SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
226
227 SET foreign_key_checks = 1;
228
229 DROP TABLE child;
230
231 DROP TABLE parent;
232
233 # Test ADD FOREIGN KEY together with renaming columns.
234 CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
235
236 INSERT INTO parent VALUES(1,2),(2,3);
237
238 CREATE INDEX tb ON parent(b);
239
240 INSERT INTO parent VALUES(10,20),(20,30);
241
242 CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
243
244 CREATE INDEX tb ON child(a2);
245
246 SET foreign_key_checks = 0;
247
248 --error ER_KEY_COLUMN_DOES_NOT_EXIST
249 ALTER TABLE child CHANGE a2 a3 INT,
250 ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
251 ON DELETE SET NULL ON UPDATE CASCADE,
252 ALGORITHM = INPLACE;
253
254 ALTER TABLE child CHANGE a2 a3 INT,
255 ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
256 ON DELETE SET NULL ON UPDATE CASCADE,
257 ALGORITHM = INPLACE;
258
259 DROP TABLE child;
260
261 DROP TABLE parent;
262
263 # Add test for add Primary key and FK on changing columns
264 CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
265 INSERT INTO parent VALUES(1,2),(2,3);
266
267 CREATE INDEX tb ON parent(b);
268
269 INSERT INTO parent VALUES(10,20),(20,30);
270
271 CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
272
273 CREATE INDEX tb ON child(a2);
274
275 SET foreign_key_checks = 0;
276
277 # Let's rebuild the table and add the FK, make the add FK failed.
278
279 SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system';
280 --error ER_FK_FAIL_ADD_SYSTEM
281 ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
282 ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
283 ON DELETE SET NULL ON UPDATE CASCADE,
284 ALGORITHM = INPLACE;
285 SET DEBUG_DBUG = @saved_debug_dbug;
286
287 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
288 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
289 SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
290 SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
291
292 # This should be successful. It will also check any left over
293 # from previous failed operation (if dictionary entries not cleaned,
294 # it will have dup key error.
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
300 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
301 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
302 SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
303 SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
304
305 SHOW CREATE TABLE child;
306
307 DROP TABLE child;
308
309 CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
310
311 # Now try primary index and FK
312 ALTER TABLE child ADD PRIMARY KEY idx (a1),
313 ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
314 ON DELETE SET NULL ON UPDATE CASCADE,
315 ALGORITHM = INPLACE;
316
317 SELECT * from information_schema.INNODB_SYS_FOREIGN;
318 SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
319 SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
320 SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
321
322 SHOW CREATE TABLE child;
323
324 DROP TABLE child;
325
326 CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
327
328 ALTER TABLE child CHANGE a1 a3 INT,
329 ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
330 ON DELETE SET NULL ON UPDATE CASCADE,
331 ALGORITHM = INPLACE;
332
333 SELECT * from information_schema.INNODB_SYS_FOREIGN;
334 SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
335 SELECT t2.name, t1.name FROM information_schema.innodb_sys_columns t1, information_schema.innodb_sys_tables t2 WHERE t1.table_id = t2.table_id AND t2.name LIKE "%child" ORDER BY t1.name;
336 SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
337
338 SHOW CREATE TABLE child;
339
340 DROP TABLE child;
341
342 CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
343 # Now try all three
344 --error ER_FK_INCORRECT_OPTION
345 ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
346 ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
347 ON DELETE SET NULL ON UPDATE CASCADE,
348 ALGORITHM = INPLACE;
349
350 DROP TABLE parent;
351 DROP TABLE child;
352
353 CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL, c INT) ENGINE = InnoDB;
354 INSERT INTO parent VALUES(1,2,3),(2,3,4);
355
356 CREATE INDEX tb ON parent(b);
357
358 CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB;
359 CREATE INDEX tb ON child(a2);
360
361 ALTER TABLE child
362 ADD CONSTRAINT fk_a FOREIGN KEY (a2) REFERENCES parent(b)
363 ON DELETE SET NULL ON UPDATE CASCADE,
364 ALGORITHM = INPLACE;
365
366 ALTER TABLE child
367 ADD CONSTRAINT fk_b FOREIGN KEY (a1) REFERENCES parent(a),
368 ALGORITHM = INPLACE;
369
370 ALTER TABLE child CHANGE a2 a2_new INT, CHANGE a1 a1_new INT;
371
372 SHOW CREATE TABLE child;
373
374 SELECT * from information_schema.INNODB_SYS_FOREIGN;
375 SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
376
377 # The third add FK will fail
378 --error ER_FK_NO_INDEX_PARENT
379 ALTER TABLE child
380 ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b),
381 ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a),
382 ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c),
383 ALGORITHM = INPLACE;
384
385 # It should still have only 2 FKs
386 SHOW CREATE TABLE child;
387
388 SELECT * from information_schema.INNODB_SYS_FOREIGN;
389
390 SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
391
392 #Now let's make it successful
393 ALTER TABLE child
394 ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b),
395 ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a),
396 ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a),
397 ALGORITHM = INPLACE;
398
399 # It should still have 5 FKs
400 SHOW CREATE TABLE child;
401
402 SELECT * from information_schema.INNODB_SYS_FOREIGN;
403
404 SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
405
406 DROP TABLE child;
407 CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB;
408 CREATE INDEX tb ON child(a2);
409
410 # Let's try this 3rd fk failure with add primary index
411 --error ER_FK_NO_INDEX_PARENT
412 ALTER TABLE child ADD PRIMARY KEY idx (a1),
413 ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b),
414 ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a),
415 ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c),
416 ALGORITHM = INPLACE;
417
418 # It should still have no FKs, no PRIMARY
419 SHOW CREATE TABLE child;
420
421 SELECT * from information_schema.INNODB_SYS_FOREIGN;
422
423 SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
424
425 # make it successful
426 ALTER TABLE child ADD PRIMARY KEY idx (a1),
427 ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b),
428 ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a),
429 ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a),
430 ALGORITHM = INPLACE;
431
432 # It should have 3 FKs, a new PRIMARY
433 SHOW CREATE TABLE child;
434
435 SELECT * from information_schema.INNODB_SYS_FOREIGN;
436
437 SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
438
439 SET foreign_key_checks = 1;
440
441 DROP TABLE child;
442 DROP TABLE parent;
443
444 CREATE TABLE Parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
445 INSERT INTO Parent VALUES(1,2),(2,3);
446
447 CREATE INDEX tb ON Parent(b);
448
449 INSERT INTO Parent VALUES(10,20),(20,30);
450
451 CREATE TABLE Child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
452
453 CREATE INDEX tb ON Child(a2);
454
455 INSERT INTO Child VALUES(10,20);
456
457 SET foreign_key_checks = 0;
458
459 ALTER TABLE Child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
460 REFERENCES Parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
461 ALGORITHM = INPLACE;
462
463 DROP TABLE Child;
464 DROP TABLE Parent;
465
466 # This is the test for bug 14594526 - FK: ASSERTION IN
467 # DICT_TABLE_CHECK_FOR_DUP_INDEXES
468 CREATE TABLE `t2`(a int,c int,d int) ENGINE=INNODB;
469 CREATE TABLE `t3`(a int,c int,d int) ENGINE=INNODB;
470 CREATE INDEX idx ON t3(a);
471
472 ALTER TABLE `t2` ADD CONSTRAINT `fw` FOREIGN KEY (`c`) REFERENCES t3 (a);
473
474 ALTER TABLE `t2` ADD CONSTRAINT `e` foreign key (`d`) REFERENCES t3(a);
475
476 --error ER_FK_FAIL_ADD_SYSTEM
477 ALTER TABLE `t3` ADD CONSTRAINT `e` foreign key (`c`) REFERENCES `t2`(`c`) ON UPDATE SET NULL;
478
479 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
480 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
481
482 DROP TABLE t2;
483
484 DROP TABLE t3;
485
486 --echo # Bug #17449901 TABLE DISAPPEARS WHEN ALTERING
487 --echo # WITH FOREIGN KEY CHECKS OFF
488
489 # Drop index via inplace algorithm
490 create table t1(f1 int,primary key(f1))engine=innodb;
491 create table t2(f2 int,f3 int,key t(f2,f3),foreign key(f2) references t1(f1))engine=innodb;
492 SET foreign_key_checks=0;
493 drop index t on t2;
494 drop table t2;
495 drop table t1;
496
497 # Drop index using alter statement via inplace
498 create table t1(f1 int ,primary key(f1))engine=innodb;
499 create table t2(f2 int,f3 int, key t(f2),foreign key(f2) references t1(f1))engine=innodb;
500 SET foreign_key_checks = 0;
501 alter table t2 drop key t,algorithm=inplace;
502 show create table t2;
503 drop table t2;
504 drop table t1;
505
506 create table t1(f1 int ,primary key(f1))engine=innodb;
507 create table t2(f2 int,f3 int, key t(f2),key t1(f2,f3),
508 foreign key(f2) references t1(f1))engine=innodb;
509 SET foreign_key_checks = 0;
510 alter table t2 drop key t,algorithm=inplace;
511 show create table t2;
512 drop table t2;
513 drop table t1;
514
515 --echo #
516 --echo # MDEV-29092 FOREIGN_KEY_CHECKS does not prevent non-copy
517 --echo # alter from creating invalid FK structures
518 --echo #
519 CREATE TABLE t1(f1 INT, KEY(f1),
520 FOREIGN KEY(f1) references t1(f1))ENGINE=InnoDB;
521 SHOW CREATE TABLE t1;
522 DROP TABLE t1;
523
524 CREATE TABLE t1(f1 INT, KEY(f1),
525 FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=InnoDB;
526 SHOW CREATE TABLE t1;
527 ALTER TABLE t1 DROP KEY f1;
528 SHOW CREATE TABLE t1;
529 DROP TABLE t1;