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 @saved_debug_dbug = @@SESSION.debug_dbug; 94SET DEBUG_DBUG = '+d,innodb_test_open_ref_fail'; 95ALTER TABLE child ADD CONSTRAINT fk_4 FOREIGN KEY (a1, a2) 96REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, 97ALGORITHM = INPLACE; 98SET DEBUG_DBUG = @saved_debug_dbug; 99SELECT * FROM information_schema.INNODB_SYS_FOREIGN; 100ID FOR_NAME REF_NAME N_COLS TYPE 101test/fk_1 test/child test/parent 1 6 102test/fk_10 test/child test/parent 2 5 103test/fk_2 test/child test/parent 2 5 104test/fk_3 test/child test/parent 2 5 105test/fk_4 test/child test/parent 2 5 106SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; 107ID FOR_COL_NAME REF_COL_NAME POS 108test/fk_1 a2 b 0 109test/fk_10 a1 a 0 110test/fk_10 a2 b 1 111test/fk_2 a1 a 0 112test/fk_2 a2 b 1 113test/fk_3 a1 a 0 114test/fk_3 a2 b 1 115test/fk_4 a1 a 0 116test/fk_4 a2 b 1 117SELECT 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; 118name name 119test/child a1 120test/child a2 121SELECT NAME FROM information_schema.INNODB_SYS_TABLES; 122NAME 123SYS_DATAFILES 124SYS_FOREIGN 125SYS_FOREIGN_COLS 126SYS_TABLESPACES 127SYS_VIRTUAL 128mysql/innodb_index_stats 129mysql/innodb_table_stats 130mysql/transaction_registry 131test/child 132test/parent 133INSERT INTO child VALUES(5,4); 134SET foreign_key_checks = 1; 135INSERT INTO child VALUES(6,5); 136ERROR 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) 137SET foreign_key_checks = 0; 138CREATE TABLE `#parent` (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB; 139CREATE INDEX tb ON `#parent`(a, b); 140CREATE TABLE `#child` (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB; 141CREATE INDEX tb ON `#child`(a1, a2); 142SET DEBUG_DBUG = '+d,innodb_test_no_foreign_idx'; 143ALTER TABLE `#child` ADD CONSTRAINT fk_40 FOREIGN KEY (a1, a2) 144REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE, 145ALGORITHM = INPLACE; 146ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_40' in the foreign table '#child' 147SET DEBUG_DBUG = @saved_debug_dbug; 148SHOW ERRORS; 149Level Code Message 150Error 1821 Failed to add the foreign key constaint. Missing index for constraint 'fk_40' in the foreign table '#child' 151SELECT * FROM information_schema.INNODB_SYS_FOREIGN; 152ID FOR_NAME REF_NAME N_COLS TYPE 153test/fk_1 test/child test/parent 1 6 154test/fk_10 test/child test/parent 2 5 155test/fk_2 test/child test/parent 2 5 156test/fk_3 test/child test/parent 2 5 157test/fk_4 test/child test/parent 2 5 158SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; 159ID FOR_COL_NAME REF_COL_NAME POS 160test/fk_1 a2 b 0 161test/fk_10 a1 a 0 162test/fk_10 a2 b 1 163test/fk_2 a1 a 0 164test/fk_2 a2 b 1 165test/fk_3 a1 a 0 166test/fk_3 a2 b 1 167test/fk_4 a1 a 0 168test/fk_4 a2 b 1 169SET DEBUG_DBUG = '+d,innodb_test_no_reference_idx'; 170ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2) 171REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, 172ALGORITHM = INPLACE; 173ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_42' in the referenced table 'parent' 174SET DEBUG_DBUG = @saved_debug_dbug; 175SHOW ERRORS; 176Level Code Message 177Error 1822 Failed to add the foreign key constaint. Missing index for constraint 'fk_42' in the referenced table 'parent' 178SET DEBUG_DBUG = '+d,innodb_test_wrong_fk_option'; 179ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2) 180REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, 181ALGORITHM = INPLACE; 182ERROR HY000: Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'test/fk_42' 183SET DEBUG_DBUG = @saved_debug_dbug; 184SELECT * FROM information_schema.INNODB_SYS_FOREIGN; 185ID FOR_NAME REF_NAME N_COLS TYPE 186test/fk_1 test/child test/parent 1 6 187test/fk_10 test/child test/parent 2 5 188test/fk_2 test/child test/parent 2 5 189test/fk_3 test/child test/parent 2 5 190test/fk_4 test/child test/parent 2 5 191SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; 192ID FOR_COL_NAME REF_COL_NAME POS 193test/fk_1 a2 b 0 194test/fk_10 a1 a 0 195test/fk_10 a2 b 1 196test/fk_2 a1 a 0 197test/fk_2 a2 b 1 198test/fk_3 a1 a 0 199test/fk_3 a2 b 1 200test/fk_4 a1 a 0 201test/fk_4 a2 b 1 202SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system'; 203ALTER TABLE `#child` ADD CONSTRAINT fk_43 FOREIGN KEY (a1, a2) 204REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE, 205ALGORITHM = INPLACE; 206ERROR HY000: Failed to add the foreign key constraint 'test/fk_43' to system tables 207SET DEBUG_DBUG = @saved_debug_dbug; 208SHOW ERRORS; 209Level Code Message 210Error 1823 Failed to add the foreign key constraint 'test/fk_43' to system tables 211DROP TABLE `#child`; 212DROP TABLE `#parent`; 213SET foreign_key_checks = 0; 214ALTER TABLE child ADD CONSTRAINT fk_5 FOREIGN KEY (a2) REFERENCES parent(b) 215ON DELETE SET NULL ON UPDATE CASCADE, 216ADD CONSTRAINT fk_6 FOREIGN KEY (a1, a2) 217REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, 218ALGORITHM = INPLACE; 219SELECT * FROM information_schema.INNODB_SYS_FOREIGN; 220ID FOR_NAME REF_NAME N_COLS TYPE 221test/fk_1 test/child test/parent 1 6 222test/fk_10 test/child test/parent 2 5 223test/fk_2 test/child test/parent 2 5 224test/fk_3 test/child test/parent 2 5 225test/fk_4 test/child test/parent 2 5 226test/fk_5 test/child test/parent 1 6 227test/fk_6 test/child test/parent 2 5 228SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; 229ID FOR_COL_NAME REF_COL_NAME POS 230test/fk_1 a2 b 0 231test/fk_10 a1 a 0 232test/fk_10 a2 b 1 233test/fk_2 a1 a 0 234test/fk_2 a2 b 1 235test/fk_3 a1 a 0 236test/fk_3 a2 b 1 237test/fk_4 a1 a 0 238test/fk_4 a2 b 1 239test/fk_5 a2 b 0 240test/fk_6 a1 a 0 241test/fk_6 a2 b 1 242DROP TABLE child; 243DROP TABLE parent; 244CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB; 245INSERT INTO parent VALUES(1,2),(2,3); 246CREATE INDEX tb ON parent(b); 247INSERT INTO parent VALUES(10,20),(20,30); 248CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB; 249CREATE INDEX tb ON child(a2); 250INSERT INTO child VALUES(10,20); 251SET foreign_key_checks = 0; 252ALTER TABLE child DROP INDEX tb, ADD CONSTRAINT fk_4 FOREIGN KEY (a2) 253REFERENCES parent(b) ON DELETE CASCADE ON UPDATE CASCADE, 254ALGORITHM = INPLACE; 255SHOW CREATE TABLE child; 256Table Create Table 257child CREATE TABLE `child` ( 258 `a1` int(11) NOT NULL, 259 `a2` int(11) DEFAULT NULL, 260 PRIMARY KEY (`a1`), 261 KEY `fk_4` (`a2`), 262 CONSTRAINT `fk_4` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE CASCADE 263) ENGINE=InnoDB DEFAULT CHARSET=latin1 264SELECT * FROM information_schema.INNODB_SYS_FOREIGN; 265ID FOR_NAME REF_NAME N_COLS TYPE 266test/fk_4 test/child test/parent 1 5 267SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS; 268ID FOR_COL_NAME REF_COL_NAME POS 269test/fk_4 a2 b 0 270SET foreign_key_checks = 1; 271DROP TABLE child; 272DROP TABLE parent; 273CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB; 274INSERT INTO parent VALUES(1,2),(2,3); 275CREATE INDEX tb ON parent(b); 276INSERT INTO parent VALUES(10,20),(20,30); 277CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB; 278CREATE INDEX tb ON child(a2); 279SET foreign_key_checks = 0; 280ALTER TABLE child CHANGE a2 a3 INT, 281ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) 282ON DELETE SET NULL ON UPDATE CASCADE, 283ALGORITHM = INPLACE; 284ERROR 42000: Key column 'a2' doesn't exist in table 285ALTER TABLE child CHANGE a2 a3 INT, 286ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b) 287ON DELETE SET NULL ON UPDATE CASCADE, 288ALGORITHM = INPLACE; 289DROP TABLE child; 290DROP TABLE parent; 291CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB; 292INSERT INTO parent VALUES(1,2),(2,3); 293CREATE INDEX tb ON parent(b); 294INSERT INTO parent VALUES(10,20),(20,30); 295CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB; 296CREATE INDEX tb ON child(a2); 297SET foreign_key_checks = 0; 298SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system'; 299ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT, 300ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) 301ON DELETE SET NULL ON UPDATE CASCADE, 302ALGORITHM = INPLACE; 303ERROR HY000: Failed to add the foreign key constraint 'test/fk_1' to system tables 304SET DEBUG_DBUG = @saved_debug_dbug; 305SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; 306ID FOR_NAME REF_NAME N_COLS TYPE 307SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; 308ID FOR_COL_NAME REF_COL_NAME POS 309SELECT 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; 310name name 311test/child a1 312test/child a2 313SELECT NAME FROM information_schema.INNODB_SYS_TABLES; 314NAME 315SYS_DATAFILES 316SYS_FOREIGN 317SYS_FOREIGN_COLS 318SYS_TABLESPACES 319SYS_VIRTUAL 320mysql/innodb_index_stats 321mysql/innodb_table_stats 322mysql/transaction_registry 323test/child 324test/parent 325ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT, 326ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) 327ON DELETE SET NULL ON UPDATE CASCADE, 328ALGORITHM = INPLACE; 329Warnings: 330Warning 1280 Name 'idx' ignored for PRIMARY key. 331SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; 332ID FOR_NAME REF_NAME N_COLS TYPE 333test/fk_1 test/child test/parent 1 6 334SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; 335ID FOR_COL_NAME REF_COL_NAME POS 336test/fk_1 a2 b 0 337SELECT 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; 338name name 339test/child a2 340test/child a3 341SELECT NAME FROM information_schema.INNODB_SYS_TABLES; 342NAME 343SYS_DATAFILES 344SYS_FOREIGN 345SYS_FOREIGN_COLS 346SYS_TABLESPACES 347SYS_VIRTUAL 348mysql/innodb_index_stats 349mysql/innodb_table_stats 350mysql/transaction_registry 351test/child 352test/parent 353SHOW CREATE TABLE child; 354Table Create Table 355child CREATE TABLE `child` ( 356 `a3` int(11) NOT NULL, 357 `a2` int(11) DEFAULT NULL, 358 PRIMARY KEY (`a3`), 359 KEY `tb` (`a2`), 360 CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE 361) ENGINE=InnoDB DEFAULT CHARSET=latin1 362DROP TABLE child; 363CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB; 364ALTER TABLE child ADD PRIMARY KEY idx (a1), 365ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b) 366ON DELETE SET NULL ON UPDATE CASCADE, 367ALGORITHM = INPLACE; 368Warnings: 369Warning 1280 Name 'idx' ignored for PRIMARY key. 370SELECT * from information_schema.INNODB_SYS_FOREIGN; 371ID FOR_NAME REF_NAME N_COLS TYPE 372test/fk_1 test/child test/parent 1 6 373SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; 374ID FOR_COL_NAME REF_COL_NAME POS 375test/fk_1 a2 b 0 376SELECT 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; 377name name 378test/child a1 379test/child a2 380SELECT NAME FROM information_schema.INNODB_SYS_TABLES; 381NAME 382SYS_DATAFILES 383SYS_FOREIGN 384SYS_FOREIGN_COLS 385SYS_TABLESPACES 386SYS_VIRTUAL 387mysql/innodb_index_stats 388mysql/innodb_table_stats 389mysql/transaction_registry 390test/child 391test/parent 392SHOW CREATE TABLE child; 393Table Create Table 394child CREATE TABLE `child` ( 395 `a1` int(11) NOT NULL, 396 `a2` int(11) DEFAULT NULL, 397 PRIMARY KEY (`a1`), 398 KEY `fk_1` (`a2`), 399 CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE 400) ENGINE=InnoDB DEFAULT CHARSET=latin1 401DROP TABLE child; 402CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB; 403ALTER TABLE child CHANGE a1 a3 INT, 404ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b) 405ON DELETE SET NULL ON UPDATE CASCADE, 406ALGORITHM = INPLACE; 407SELECT * from information_schema.INNODB_SYS_FOREIGN; 408ID FOR_NAME REF_NAME N_COLS TYPE 409test/fk_1 test/child test/parent 1 6 410SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; 411ID FOR_COL_NAME REF_COL_NAME POS 412test/fk_1 a3 b 0 413SELECT 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; 414name name 415test/child a2 416test/child a3 417SELECT NAME FROM information_schema.INNODB_SYS_TABLES; 418NAME 419SYS_DATAFILES 420SYS_FOREIGN 421SYS_FOREIGN_COLS 422SYS_TABLESPACES 423SYS_VIRTUAL 424mysql/innodb_index_stats 425mysql/innodb_table_stats 426mysql/transaction_registry 427test/child 428test/parent 429SHOW CREATE TABLE child; 430Table Create Table 431child CREATE TABLE `child` ( 432 `a3` int(11) DEFAULT NULL, 433 `a2` int(11) DEFAULT NULL, 434 KEY `fk_1` (`a3`), 435 CONSTRAINT `fk_1` FOREIGN KEY (`a3`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE 436) ENGINE=InnoDB DEFAULT CHARSET=latin1 437DROP TABLE child; 438CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB; 439ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT, 440ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b) 441ON DELETE SET NULL ON UPDATE CASCADE, 442ALGORITHM = INPLACE; 443ERROR HY000: Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'test/fk_1' 444DROP TABLE parent; 445DROP TABLE child; 446CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL, c INT) ENGINE = InnoDB; 447INSERT INTO parent VALUES(1,2,3),(2,3,4); 448CREATE INDEX tb ON parent(b); 449CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB; 450CREATE INDEX tb ON child(a2); 451ALTER TABLE child 452ADD CONSTRAINT fk_a FOREIGN KEY (a2) REFERENCES parent(b) 453ON DELETE SET NULL ON UPDATE CASCADE, 454ALGORITHM = INPLACE; 455ALTER TABLE child 456ADD CONSTRAINT fk_b FOREIGN KEY (a1) REFERENCES parent(a), 457ALGORITHM = INPLACE; 458ALTER TABLE child CHANGE a2 a2_new INT, CHANGE a1 a1_new INT; 459SHOW CREATE TABLE child; 460Table Create Table 461child CREATE TABLE `child` ( 462 `a1_new` int(11) DEFAULT NULL, 463 `a2_new` int(11) DEFAULT NULL, 464 `a3` int(11) DEFAULT NULL, 465 KEY `tb` (`a2_new`), 466 KEY `fk_b` (`a1_new`), 467 CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE, 468 CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`) 469) ENGINE=InnoDB DEFAULT CHARSET=latin1 470SELECT * from information_schema.INNODB_SYS_FOREIGN; 471ID FOR_NAME REF_NAME N_COLS TYPE 472test/fk_a test/child test/parent 1 6 473test/fk_b test/child test/parent 1 0 474SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; 475ID FOR_COL_NAME REF_COL_NAME POS 476test/fk_a a2_new b 0 477test/fk_b a1_new a 0 478ALTER TABLE child 479ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b), 480ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a), 481ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c), 482ALGORITHM = INPLACE; 483ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_new_3' in the referenced table 'parent' 484SHOW CREATE TABLE child; 485Table Create Table 486child CREATE TABLE `child` ( 487 `a1_new` int(11) DEFAULT NULL, 488 `a2_new` int(11) DEFAULT NULL, 489 `a3` int(11) DEFAULT NULL, 490 KEY `tb` (`a2_new`), 491 KEY `fk_b` (`a1_new`), 492 CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE, 493 CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`) 494) ENGINE=InnoDB DEFAULT CHARSET=latin1 495SELECT * from information_schema.INNODB_SYS_FOREIGN; 496ID FOR_NAME REF_NAME N_COLS TYPE 497test/fk_a test/child test/parent 1 6 498test/fk_b test/child test/parent 1 0 499SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; 500ID FOR_COL_NAME REF_COL_NAME POS 501test/fk_a a2_new b 0 502test/fk_b a1_new a 0 503ALTER TABLE child 504ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b), 505ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a), 506ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a), 507ALGORITHM = INPLACE; 508SHOW CREATE TABLE child; 509Table Create Table 510child CREATE TABLE `child` ( 511 `a1_new` int(11) DEFAULT NULL, 512 `a2_new` int(11) DEFAULT NULL, 513 `a3` int(11) DEFAULT NULL, 514 KEY `tb` (`a2_new`), 515 KEY `fk_new_1` (`a1_new`), 516 KEY `fk_new_3` (`a3`), 517 CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE, 518 CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`), 519 CONSTRAINT `fk_new_1` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`b`), 520 CONSTRAINT `fk_new_2` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`a`), 521 CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`) 522) ENGINE=InnoDB DEFAULT CHARSET=latin1 523SELECT * from information_schema.INNODB_SYS_FOREIGN; 524ID FOR_NAME REF_NAME N_COLS TYPE 525test/fk_a test/child test/parent 1 6 526test/fk_b test/child test/parent 1 0 527test/fk_new_1 test/child test/parent 1 0 528test/fk_new_2 test/child test/parent 1 0 529test/fk_new_3 test/child test/parent 1 0 530SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; 531ID FOR_COL_NAME REF_COL_NAME POS 532test/fk_a a2_new b 0 533test/fk_b a1_new a 0 534test/fk_new_1 a1_new b 0 535test/fk_new_2 a2_new a 0 536test/fk_new_3 a3 a 0 537DROP TABLE child; 538CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB; 539CREATE INDEX tb ON child(a2); 540ALTER TABLE child ADD PRIMARY KEY idx (a1), 541ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b), 542ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a), 543ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c), 544ALGORITHM = INPLACE; 545ERROR HY000: Failed to add the foreign key constaint. Missing index for constraint 'fk_new_3' in the referenced table 'parent' 546SHOW CREATE TABLE child; 547Table Create Table 548child CREATE TABLE `child` ( 549 `a1` int(11) NOT NULL, 550 `a2` int(11) DEFAULT NULL, 551 `a3` int(11) DEFAULT NULL, 552 KEY `tb` (`a2`) 553) ENGINE=InnoDB DEFAULT CHARSET=latin1 554SELECT * from information_schema.INNODB_SYS_FOREIGN; 555ID FOR_NAME REF_NAME N_COLS TYPE 556SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; 557ID FOR_COL_NAME REF_COL_NAME POS 558ALTER TABLE child ADD PRIMARY KEY idx (a1), 559ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b), 560ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a), 561ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a), 562ALGORITHM = INPLACE; 563Warnings: 564Warning 1280 Name 'idx' ignored for PRIMARY key. 565SHOW CREATE TABLE child; 566Table Create Table 567child CREATE TABLE `child` ( 568 `a1` int(11) NOT NULL, 569 `a2` int(11) DEFAULT NULL, 570 `a3` int(11) DEFAULT NULL, 571 PRIMARY KEY (`a1`), 572 KEY `tb` (`a2`), 573 KEY `fk_new_3` (`a3`), 574 CONSTRAINT `fk_new_1` FOREIGN KEY (`a1`) REFERENCES `parent` (`b`), 575 CONSTRAINT `fk_new_2` FOREIGN KEY (`a2`) REFERENCES `parent` (`a`), 576 CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`) 577) ENGINE=InnoDB DEFAULT CHARSET=latin1 578SELECT * from information_schema.INNODB_SYS_FOREIGN; 579ID FOR_NAME REF_NAME N_COLS TYPE 580test/fk_new_1 test/child test/parent 1 0 581test/fk_new_2 test/child test/parent 1 0 582test/fk_new_3 test/child test/parent 1 0 583SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS; 584ID FOR_COL_NAME REF_COL_NAME POS 585test/fk_new_1 a1 b 0 586test/fk_new_2 a2 a 0 587test/fk_new_3 a3 a 0 588SET foreign_key_checks = 1; 589DROP TABLE child; 590DROP TABLE parent; 591CREATE TABLE Parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB; 592INSERT INTO Parent VALUES(1,2),(2,3); 593CREATE INDEX tb ON Parent(b); 594INSERT INTO Parent VALUES(10,20),(20,30); 595CREATE TABLE Child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB; 596CREATE INDEX tb ON Child(a2); 597INSERT INTO Child VALUES(10,20); 598SET foreign_key_checks = 0; 599ALTER TABLE Child ADD CONSTRAINT fk_1 FOREIGN KEY (a2) 600REFERENCES Parent(b) ON DELETE SET NULL ON UPDATE CASCADE, 601ALGORITHM = INPLACE; 602DROP TABLE Child; 603DROP TABLE Parent; 604CREATE TABLE `t2`(a int,c int,d int) ENGINE=INNODB; 605CREATE TABLE `t3`(a int,c int,d int) ENGINE=INNODB; 606CREATE INDEX idx ON t3(a); 607ALTER TABLE `t2` ADD CONSTRAINT `fw` FOREIGN KEY (`c`) REFERENCES t3 (a); 608ALTER TABLE `t2` ADD CONSTRAINT `e` foreign key (`d`) REFERENCES t3(a); 609ALTER TABLE `t3` ADD CONSTRAINT `e` foreign key (`c`) REFERENCES `t2`(`c`) ON UPDATE SET NULL; 610ERROR HY000: Failed to add the foreign key constraint 'test/e' to system tables 611SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN; 612ID FOR_NAME REF_NAME N_COLS TYPE 613test/e test/t2 test/t3 1 0 614test/fw test/t2 test/t3 1 0 615SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS; 616ID FOR_COL_NAME REF_COL_NAME POS 617test/e d a 0 618test/fw c a 0 619DROP TABLE t2; 620DROP TABLE t3; 621