Debian Codegraph @

mysql-test/suite/innodb/r/foreign_sql_mode.result

raw
1 call mtr.add_suppression("InnoDB: In ALTER TABLE .* has or is referenced in foreign key constraints which are not compatible with the new table definition.");
2 # modify child column NOT NULL on UPDATE CASCADE..parent column NULL
3 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
4 CREATE TABLE t2(f1 INT, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
5 ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL;
6 ERROR HY000: Column 'f1' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
7 INSERT INTO t1 VALUES(1, 1);
8 INSERT INTO t2 VALUES(1);
9 UPDATE t1 SET f2= NULL;
10 DELETE FROM t2;
11 SELECT * FROM t1;
12 f1 f2
13 1 NULL
14 UPDATE t1 SET f2 = NULL;
15 SELECT * FROM t1;
16 f1 f2
17 1 NULL
18 DROP TABLE t2, t1;
19 # modify child column NOT NULL ON UPDATE SET NULL
20 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
21 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY(f1) REFERENCES t1(f1) ON UPDATE SET NULL)ENGINE=InnoDB;
22 ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL;
23 ERROR HY000: Column 'f1' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
24 INSERT INTO t1 VALUES(1, 1);
25 INSERT INTO t2 VALUES(1, 1);
26 UPDATE t1 SET f1= 2;
27 SELECT * FROM t2;
28 f1 f2
29 NULL 1
30 DROP TABLE t2, t1;
31 # modify child column NOT NULL ON DELETE SET NULL
32 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
33 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE SET NULL)ENGINE=InnoDB;
34 ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
35 ERROR HY000: Column 'f2' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
36 DROP TABLE t2, t1;
37 # modify parent column NULL ON UPDATE CASCADE child column NOT NULL
38 CREATE TABLE `t#1`(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
39 CREATE TABLE `t#2`(f1 INT NOT NULL,
40 FOREIGN KEY(f1) REFERENCES `t#1`(f2)
41 ON UPDATE CASCADE)ENGINE=InnoDB;
42 ALTER TABLE `t#1` MODIFY COLUMN f2 INT;
43 ERROR HY000: Cannot change column 'f2': used in a foreign key constraint 't#2_ibfk_1' of table 'test.t#2'
44 INSERT INTO `t#1` VALUES(1, 1);
45 INSERT INTO `t#2` VALUES(1);
46 UPDATE `t#1` SET f2= NULL;
47 ERROR 23000: Column 'f2' cannot be null
48 DELETE FROM `t#2`;
49 SELECT * FROM `t#1`;
50 f1 f2
51 1 1
52 DROP TABLE `t#2`, `t#1`;
53 CREATE TABLE t1(f1 INT NOT NULL AUTO_INCREMENT,
54 f2 INT DEFAULT NULL,
55 PRIMARY KEY(f1),
56 FOREIGN KEY(f2) REFERENCES t1(f1))ENGINE=InnoDB;
57 CREATE TABLE t2 (f1 INT NOT NULL,
58 f2 INT NOT NULL,
59 f3 INT DEFAULT NULL,
60 PRIMARY KEY(f1, f2),
61 FOREIGN KEY(f2, f3) REFERENCES t1(f2, f1)
62 ON UPDATE CASCADE)ENGINE=InnoDB;
63 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
64 DROP TABLE IF EXISTS t2;
65 DROP TABLE IF EXISTS t1;