|
In the foolowing test there are two ALTER TABLES. After the first one, a foreign key constraint
is gone missing, but it is returned after the second ALTER TABLE.
--source include/have_innodb.inc
|
call mtr.add_suppression(
|
"has or is referenced in foreign key constraints");
|
|
CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, a INT, KEY(a))
|
ENGINE=InnoDB;
|
CREATE TABLE t2 (pk INT PRIMARY KEY, b INT,
|
FOREIGN KEY(b) REFERENCES t1(a)) ENGINE=InnoDB;
|
|
SET FOREIGN_KEY_CHECKS= OFF;
|
ALTER TABLE t2 MODIFY COLUMN b YEAR;
|
SHOW CREATE TABLE t2;
|
--connect (con1,localhost,root,,test)
|
INSERT INTO t2 values (1, 1);
|
|
ALTER TABLE t2 ADD INDEX ind (b);
|
SHOW CREATE TABLE t2;
|
--connection con1
|
INSERT INTO t2 values (2, 2);
|
|
#Cleanup
|
DROP TABLE t2, t1;
|
SHOW CREATE outputs are according, as well as the DML behavior in the concurrent connection:
|
10.6 2ac1edb1
|
SET FOREIGN_KEY_CHECKS= OFF;
|
ALTER TABLE t2 MODIFY COLUMN b YEAR;
|
SHOW CREATE TABLE t2;
|
Table Create Table
|
t2 CREATE TABLE `t2` (
|
`pk` int(11) NOT NULL,
|
`b` year(4) DEFAULT NULL,
|
PRIMARY KEY (`pk`),
|
KEY `b` (`b`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
connect con1,localhost,root,,test;
|
INSERT INTO t2 values (1, 1);
|
ALTER TABLE t2 ADD INDEX ind (b);
|
SHOW CREATE TABLE t2;
|
Table Create Table
|
t2 CREATE TABLE `t2` (
|
`pk` int(11) NOT NULL,
|
`b` year(4) DEFAULT NULL,
|
PRIMARY KEY (`pk`),
|
KEY `ind` (`b`),
|
CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`)
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
connection con1;
|
INSERT INTO t2 values (2, 2);
|
|
mysqltest: At line 21: query 'INSERT INTO t2 values (2, 2)' failed: ER_NO_REFERENCED_ROW_2 (1452): Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`b`) REFERENCES `t1` (`a`))
|
|