CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
INSERT INTO parent VALUES(1,2),(2,3);
CREATE INDEX tb ON parent(b);
INSERT INTO parent VALUES(10,20),(20,30);
CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
CREATE INDEX tb ON child(a2);
INSERT INTO child VALUES(10,20);
ALTER TABLE child ADD FOREIGN KEY(a2) REFERENCES parent(b),
ALGORITHM = INPLACE;
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
SET foreign_key_checks = 0;
ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_1	test/child	test/parent	1	6
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_1	a2	b	0
ALTER TABLE child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
REFERENCES parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ERROR HY000: Duplicate FOREIGN KEY constraint name 'test/fk_1'
SET foreign_key_checks = 1;
INSERT INTO child VALUES(1,2),(2,3);
INSERT INTO child VALUES(4,4);
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)
SELECT * FROM parent;
a	b
1	2
2	3
10	20
20	30
SET foreign_key_checks = 0;
ALTER TABLE child ADD CONSTRAINT fk_20 FOREIGN KEY (a1, a2)
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
SHOW WARNINGS;
Level	Code	Message
Error	1822	Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
SHOW ERRORS;
Level	Code	Message
Error	1822	Failed to add the foreign key constraint. Missing index for constraint 'fk_20' in the referenced table 'parent'
CREATE INDEX idx1 on parent(a, b);
ALTER TABLE child ADD CONSTRAINT fk_10 FOREIGN KEY (a1, a2)
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ALTER TABLE child ADD CONSTRAINT fk_2 FOREIGN KEY (a1, a2)
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE, ADD INDEX idx1(a1,a2),
ALGORITHM = INPLACE;
ALTER TABLE child ADD CONSTRAINT fk_3 FOREIGN KEY (a1, a2)
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE;
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_1	test/child	test/parent	1	6
test/fk_10	test/child	test/parent	2	5
test/fk_2	test/child	test/parent	2	5
test/fk_3	test/child	test/parent	2	5
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_1	a2	b	0
test/fk_10	a1	a	0
test/fk_10	a2	b	1
test/fk_2	a1	a	0
test/fk_2	a2	b	1
test/fk_3	a1	a	0
test/fk_3	a2	b	1
SET foreign_key_checks = 1;
INSERT INTO child VALUES(5,4);
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)
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a1` int(11) NOT NULL,
  `a2` int(11) DEFAULT NULL,
  PRIMARY KEY (`a1`),
  KEY `tb` (`a2`),
  KEY `idx1` (`a1`,`a2`),
  CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_10` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_2` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_3` FOREIGN KEY (`a1`, `a2`) REFERENCES `parent` (`a`, `b`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DELETE FROM parent where a = 1;
SELECT * FROM child;
a1	a2
1	NULL
2	3
10	20
SET foreign_key_checks = 0;
SET @saved_debug_dbug = @@SESSION.debug_dbug;
SET DEBUG_DBUG = '+d,innodb_test_open_ref_fail';
ALTER TABLE child ADD CONSTRAINT fk_4 FOREIGN KEY (a1, a2)
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
ALGORITHM = INPLACE;
SET DEBUG_DBUG = @saved_debug_dbug;
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_1	test/child	test/parent	1	6
test/fk_10	test/child	test/parent	2	5
test/fk_2	test/child	test/parent	2	5
test/fk_3	test/child	test/parent	2	5
test/fk_4	test/child	test/parent	2	5
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_1	a2	b	0
test/fk_10	a1	a	0
test/fk_10	a2	b	1
test/fk_2	a1	a	0
test/fk_2	a2	b	1
test/fk_3	a1	a	0
test/fk_3	a2	b	1
test/fk_4	a1	a	0
test/fk_4	a2	b	1
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;
name	name
test/child	a1
test/child	a2
SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
NAME
SYS_FOREIGN
SYS_FOREIGN_COLS
SYS_VIRTUAL
mysql/innodb_index_stats
mysql/innodb_table_stats
mysql/transaction_registry
test/child
test/parent
INSERT INTO child VALUES(5,4);
SET foreign_key_checks = 1;
INSERT INTO child VALUES(6,5);
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)
SET foreign_key_checks = 0;
CREATE TABLE `#parent` (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
CREATE INDEX tb ON `#parent`(a, b);
CREATE TABLE `#child` (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
CREATE INDEX tb ON `#child`(a1, a2);
SET DEBUG_DBUG = '+d,innodb_test_no_foreign_idx';
ALTER TABLE `#child` ADD CONSTRAINT fk_40 FOREIGN KEY (a1, a2)
REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_40' in the foreign table '#child'
SET DEBUG_DBUG = @saved_debug_dbug;
SHOW ERRORS;
Level	Code	Message
Error	1821	Failed to add the foreign key constraint. Missing index for constraint 'fk_40' in the foreign table '#child'
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_1	test/child	test/parent	1	6
test/fk_10	test/child	test/parent	2	5
test/fk_2	test/child	test/parent	2	5
test/fk_3	test/child	test/parent	2	5
test/fk_4	test/child	test/parent	2	5
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_1	a2	b	0
test/fk_10	a1	a	0
test/fk_10	a2	b	1
test/fk_2	a1	a	0
test/fk_2	a2	b	1
test/fk_3	a1	a	0
test/fk_3	a2	b	1
test/fk_4	a1	a	0
test/fk_4	a2	b	1
SET DEBUG_DBUG = '+d,innodb_test_no_reference_idx';
ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2)
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_42' in the referenced table 'parent'
SET DEBUG_DBUG = @saved_debug_dbug;
SHOW ERRORS;
Level	Code	Message
Error	1822	Failed to add the foreign key constraint. Missing index for constraint 'fk_42' in the referenced table 'parent'
SET DEBUG_DBUG = '+d,innodb_test_wrong_fk_option';
ALTER TABLE child ADD CONSTRAINT fk_42 FOREIGN KEY (a1, a2)
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ERROR HY000: Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'test/fk_42'
SET DEBUG_DBUG = @saved_debug_dbug;
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_1	test/child	test/parent	1	6
test/fk_10	test/child	test/parent	2	5
test/fk_2	test/child	test/parent	2	5
test/fk_3	test/child	test/parent	2	5
test/fk_4	test/child	test/parent	2	5
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_1	a2	b	0
test/fk_10	a1	a	0
test/fk_10	a2	b	1
test/fk_2	a1	a	0
test/fk_2	a2	b	1
test/fk_3	a1	a	0
test/fk_3	a2	b	1
test/fk_4	a1	a	0
test/fk_4	a2	b	1
SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system';
ALTER TABLE `#child` ADD CONSTRAINT fk_43 FOREIGN KEY (a1, a2)
REFERENCES `#parent`(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ERROR HY000: Failed to add the foreign key constraint 'test/fk_43' to system tables
SET DEBUG_DBUG = @saved_debug_dbug;
SHOW ERRORS;
Level	Code	Message
Error	1823	Failed to add the foreign key constraint 'test/fk_43' to system tables
DROP TABLE `#child`;
DROP TABLE `#parent`;
SET foreign_key_checks = 0;
ALTER TABLE child ADD CONSTRAINT fk_5 FOREIGN KEY (a2) REFERENCES parent(b)
ON DELETE SET NULL ON UPDATE CASCADE,
ADD CONSTRAINT fk_6 FOREIGN KEY (a1, a2)
REFERENCES parent(a, b) ON DELETE CASCADE ON UPDATE CASCADE,
ALGORITHM = INPLACE;
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_1	test/child	test/parent	1	6
test/fk_10	test/child	test/parent	2	5
test/fk_2	test/child	test/parent	2	5
test/fk_3	test/child	test/parent	2	5
test/fk_4	test/child	test/parent	2	5
test/fk_5	test/child	test/parent	1	6
test/fk_6	test/child	test/parent	2	5
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_1	a2	b	0
test/fk_10	a1	a	0
test/fk_10	a2	b	1
test/fk_2	a1	a	0
test/fk_2	a2	b	1
test/fk_3	a1	a	0
test/fk_3	a2	b	1
test/fk_4	a1	a	0
test/fk_4	a2	b	1
test/fk_5	a2	b	0
test/fk_6	a1	a	0
test/fk_6	a2	b	1
DROP TABLE child;
DROP TABLE parent;
CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
INSERT INTO parent VALUES(1,2),(2,3);
CREATE INDEX tb ON parent(b);
INSERT INTO parent VALUES(10,20),(20,30);
CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
CREATE INDEX tb ON child(a2);
INSERT INTO child VALUES(10,20);
SET foreign_key_checks = 0;
ALTER TABLE child DROP INDEX tb, ADD CONSTRAINT fk_4 FOREIGN KEY (a2)
REFERENCES parent(b) ON DELETE CASCADE ON UPDATE CASCADE,
ALGORITHM = INPLACE;
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a1` int(11) NOT NULL,
  `a2` int(11) DEFAULT NULL,
  PRIMARY KEY (`a1`),
  KEY `fk_4` (`a2`),
  CONSTRAINT `fk_4` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * FROM information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_4	test/child	test/parent	1	5
SELECT * FROM information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_4	a2	b	0
SET foreign_key_checks = 1;
DROP TABLE child;
DROP TABLE parent;
CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
INSERT INTO parent VALUES(1,2),(2,3);
CREATE INDEX tb ON parent(b);
INSERT INTO parent VALUES(10,20),(20,30);
CREATE TABLE child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
CREATE INDEX tb ON child(a2);
SET foreign_key_checks = 0;
ALTER TABLE child CHANGE a2 a3 INT,
ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ERROR 42000: Key column 'a2' doesn't exist in table
ALTER TABLE child CHANGE a2 a3 INT,
ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
DROP TABLE child;
DROP TABLE parent;
CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
INSERT INTO parent VALUES(1,2),(2,3);
CREATE INDEX tb ON parent(b);
INSERT INTO parent VALUES(10,20),(20,30);
CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
CREATE INDEX tb ON child(a2);
SET foreign_key_checks = 0;
SET DEBUG_DBUG = '+d,innodb_test_cannot_add_fk_system';
ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ERROR HY000: Failed to add the foreign key constraint 'test/fk_1' to system tables
SET DEBUG_DBUG = @saved_debug_dbug;
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
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;
name	name
test/child	a1
test/child	a2
SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
NAME
SYS_FOREIGN
SYS_FOREIGN_COLS
SYS_VIRTUAL
mysql/innodb_index_stats
mysql/innodb_table_stats
mysql/transaction_registry
test/child
test/parent
ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
Warnings:
Warning	1280	Name 'idx' ignored for PRIMARY key.
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_1	test/child	test/parent	1	6
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_1	a2	b	0
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;
name	name
test/child	a2
test/child	a3
SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
NAME
SYS_FOREIGN
SYS_FOREIGN_COLS
SYS_VIRTUAL
mysql/innodb_index_stats
mysql/innodb_table_stats
mysql/transaction_registry
test/child
test/parent
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a3` int(11) NOT NULL,
  `a2` int(11) DEFAULT NULL,
  PRIMARY KEY (`a3`),
  KEY `tb` (`a2`),
  CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE child;
CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
ALTER TABLE child ADD PRIMARY KEY idx (a1),
ADD CONSTRAINT fk_1 FOREIGN KEY (a2) REFERENCES parent(b)
ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
Warnings:
Warning	1280	Name 'idx' ignored for PRIMARY key.
SELECT * from information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_1	test/child	test/parent	1	6
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_1	a2	b	0
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;
name	name
test/child	a1
test/child	a2
SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
NAME
SYS_FOREIGN
SYS_FOREIGN_COLS
SYS_VIRTUAL
mysql/innodb_index_stats
mysql/innodb_table_stats
mysql/transaction_registry
test/child
test/parent
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a1` int(11) NOT NULL,
  `a2` int(11) DEFAULT NULL,
  PRIMARY KEY (`a1`),
  KEY `fk_1` (`a2`),
  CONSTRAINT `fk_1` FOREIGN KEY (`a2`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE child;
CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
ALTER TABLE child CHANGE a1 a3 INT,
ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
SELECT * from information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_1	test/child	test/parent	1	6
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_1	a3	b	0
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;
name	name
test/child	a2
test/child	a3
SELECT NAME FROM information_schema.INNODB_SYS_TABLES;
NAME
SYS_FOREIGN
SYS_FOREIGN_COLS
SYS_VIRTUAL
mysql/innodb_index_stats
mysql/innodb_table_stats
mysql/transaction_registry
test/child
test/parent
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a3` int(11) DEFAULT NULL,
  `a2` int(11) DEFAULT NULL,
  KEY `fk_1` (`a3`),
  CONSTRAINT `fk_1` FOREIGN KEY (`a3`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE child;
CREATE TABLE child (a1 INT NOT NULL, a2 INT) ENGINE = InnoDB;
ALTER TABLE child ADD PRIMARY KEY idx (a3), CHANGE a1 a3 INT,
ADD CONSTRAINT fk_1 FOREIGN KEY (a3) REFERENCES parent(b)
ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ERROR HY000: Failed to add the foreign key constraint on table 'child'. Incorrect options in FOREIGN KEY constraint 'test/fk_1'
DROP TABLE parent;
DROP TABLE child;
CREATE TABLE parent (a INT PRIMARY KEY, b INT NOT NULL, c INT) ENGINE = InnoDB;
INSERT INTO parent VALUES(1,2,3),(2,3,4);
CREATE INDEX tb ON parent(b);
CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB;
CREATE INDEX tb ON child(a2);
ALTER TABLE child
ADD CONSTRAINT fk_a FOREIGN KEY (a2) REFERENCES parent(b)
ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
ALTER TABLE child
ADD CONSTRAINT fk_b FOREIGN KEY (a1) REFERENCES parent(a),
ALGORITHM = INPLACE;
ALTER TABLE child CHANGE a2 a2_new INT, CHANGE a1 a1_new INT;
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a1_new` int(11) DEFAULT NULL,
  `a2_new` int(11) DEFAULT NULL,
  `a3` int(11) DEFAULT NULL,
  KEY `tb` (`a2_new`),
  KEY `fk_b` (`a1_new`),
  CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * from information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_a	test/child	test/parent	1	6
test/fk_b	test/child	test/parent	1	0
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_a	a2_new	b	0
test/fk_b	a1_new	a	0
ALTER TABLE child
ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b),
ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a),
ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c),
ALGORITHM = INPLACE;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_new_3' in the referenced table 'parent'
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a1_new` int(11) DEFAULT NULL,
  `a2_new` int(11) DEFAULT NULL,
  `a3` int(11) DEFAULT NULL,
  KEY `tb` (`a2_new`),
  KEY `fk_b` (`a1_new`),
  CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * from information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_a	test/child	test/parent	1	6
test/fk_b	test/child	test/parent	1	0
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_a	a2_new	b	0
test/fk_b	a1_new	a	0
ALTER TABLE child
ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1_new) REFERENCES parent(b),
ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2_new) REFERENCES parent(a),
ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a),
ALGORITHM = INPLACE;
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a1_new` int(11) DEFAULT NULL,
  `a2_new` int(11) DEFAULT NULL,
  `a3` int(11) DEFAULT NULL,
  KEY `tb` (`a2_new`),
  KEY `fk_new_1` (`a1_new`),
  KEY `fk_new_3` (`a3`),
  CONSTRAINT `fk_a` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`b`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_b` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`a`),
  CONSTRAINT `fk_new_1` FOREIGN KEY (`a1_new`) REFERENCES `parent` (`b`),
  CONSTRAINT `fk_new_2` FOREIGN KEY (`a2_new`) REFERENCES `parent` (`a`),
  CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * from information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_a	test/child	test/parent	1	6
test/fk_b	test/child	test/parent	1	0
test/fk_new_1	test/child	test/parent	1	0
test/fk_new_2	test/child	test/parent	1	0
test/fk_new_3	test/child	test/parent	1	0
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_a	a2_new	b	0
test/fk_b	a1_new	a	0
test/fk_new_1	a1_new	b	0
test/fk_new_2	a2_new	a	0
test/fk_new_3	a3	a	0
DROP TABLE child;
CREATE TABLE child (a1 INT NOT NULL, a2 INT, a3 INT) ENGINE = InnoDB;
CREATE INDEX tb ON child(a2);
ALTER TABLE child ADD PRIMARY KEY idx (a1),
ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b),
ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a),
ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(c),
ALGORITHM = INPLACE;
ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk_new_3' in the referenced table 'parent'
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a1` int(11) NOT NULL,
  `a2` int(11) DEFAULT NULL,
  `a3` int(11) DEFAULT NULL,
  KEY `tb` (`a2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * from information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
ALTER TABLE child ADD PRIMARY KEY idx (a1),
ADD CONSTRAINT fk_new_1 FOREIGN KEY (a1) REFERENCES parent(b),
ADD CONSTRAINT fk_new_2 FOREIGN KEY (a2) REFERENCES parent(a),
ADD CONSTRAINT fk_new_3 FOREIGN KEY (a3) REFERENCES parent(a),
ALGORITHM = INPLACE;
Warnings:
Warning	1280	Name 'idx' ignored for PRIMARY key.
SHOW CREATE TABLE child;
Table	Create Table
child	CREATE TABLE `child` (
  `a1` int(11) NOT NULL,
  `a2` int(11) DEFAULT NULL,
  `a3` int(11) DEFAULT NULL,
  PRIMARY KEY (`a1`),
  KEY `tb` (`a2`),
  KEY `fk_new_3` (`a3`),
  CONSTRAINT `fk_new_1` FOREIGN KEY (`a1`) REFERENCES `parent` (`b`),
  CONSTRAINT `fk_new_2` FOREIGN KEY (`a2`) REFERENCES `parent` (`a`),
  CONSTRAINT `fk_new_3` FOREIGN KEY (`a3`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
SELECT * from information_schema.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/fk_new_1	test/child	test/parent	1	0
test/fk_new_2	test/child	test/parent	1	0
test/fk_new_3	test/child	test/parent	1	0
SELECT * from information_schema.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/fk_new_1	a1	b	0
test/fk_new_2	a2	a	0
test/fk_new_3	a3	a	0
SET foreign_key_checks = 1;
DROP TABLE child;
DROP TABLE parent;
CREATE TABLE Parent (a INT PRIMARY KEY, b INT NOT NULL) ENGINE = InnoDB;
INSERT INTO Parent VALUES(1,2),(2,3);
CREATE INDEX tb ON Parent(b);
INSERT INTO Parent VALUES(10,20),(20,30);
CREATE TABLE Child (a1 INT PRIMARY KEY, a2 INT) ENGINE = InnoDB;
CREATE INDEX tb ON Child(a2);
INSERT INTO Child VALUES(10,20);
SET foreign_key_checks = 0;
ALTER TABLE Child ADD CONSTRAINT fk_1 FOREIGN KEY (a2)
REFERENCES Parent(b) ON DELETE SET NULL ON UPDATE CASCADE,
ALGORITHM = INPLACE;
DROP TABLE Child;
DROP TABLE Parent;
CREATE TABLE `t2`(a int,c int,d int) ENGINE=INNODB;
CREATE TABLE `t3`(a int,c int,d int) ENGINE=INNODB;
CREATE INDEX idx ON t3(a);
ALTER TABLE `t2` ADD CONSTRAINT `fw` FOREIGN KEY (`c`) REFERENCES t3 (a);
ALTER TABLE `t2` ADD CONSTRAINT `e` foreign key (`d`) REFERENCES t3(a);
ALTER TABLE `t3` ADD CONSTRAINT `e` foreign key (`c`) REFERENCES `t2`(`c`) ON UPDATE SET NULL;
ERROR HY000: Failed to add the foreign key constraint 'test/e' to system tables
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
ID	FOR_NAME	REF_NAME	N_COLS	TYPE
test/e	test/t2	test/t3	1	0
test/fw	test/t2	test/t3	1	0
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
ID	FOR_COL_NAME	REF_COL_NAME	POS
test/e	d	a	0
test/fw	c	a	0
DROP TABLE t2;
DROP TABLE t3;
# Bug #17449901	 TABLE DISAPPEARS WHEN ALTERING
# WITH FOREIGN KEY CHECKS OFF
create table t1(f1 int,primary key(f1))engine=innodb;
create table t2(f2 int,f3 int,key t(f2,f3),foreign key(f2) references t1(f1))engine=innodb;
SET foreign_key_checks=0;
drop index t on t2;
drop table t2;
drop table t1;
create table t1(f1 int ,primary key(f1))engine=innodb;
create table t2(f2 int,f3 int, key t(f2),foreign key(f2) references t1(f1))engine=innodb;
SET foreign_key_checks = 0;
alter table t2 drop key t,algorithm=inplace;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `f2` int(11) DEFAULT NULL,
  `f3` int(11) DEFAULT NULL,
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t2;
drop table t1;
create table t1(f1 int ,primary key(f1))engine=innodb;
create table t2(f2 int,f3 int, key t(f2),key t1(f2,f3),
foreign key(f2) references t1(f1))engine=innodb;
SET foreign_key_checks = 0;
alter table t2 drop key t,algorithm=inplace;
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `f2` int(11) DEFAULT NULL,
  `f3` int(11) DEFAULT NULL,
  KEY `t1` (`f2`,`f3`),
  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f2`) REFERENCES `t1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t2;
drop table t1;
#
#  MDEV-29092  FOREIGN_KEY_CHECKS does not prevent non-copy
#               alter from creating invalid FK structures
#
CREATE TABLE t1(f1 INT, KEY(f1),
FOREIGN KEY(f1) references t1(f1))ENGINE=InnoDB;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  KEY `f1` (`f1`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE t1;
CREATE TABLE t1(f1 INT, KEY(f1),
FOREIGN KEY(f1) REFERENCES t1(f1))ENGINE=InnoDB;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  KEY `f1` (`f1`),
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
ALTER TABLE t1 DROP KEY f1;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `f1` int(11) DEFAULT NULL,
  CONSTRAINT `t1_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
DROP TABLE t1;