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