Debian Codegraph @

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

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