Debian Codegraph @

mysql-test/suite/innodb/r/foreign_null.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 DROP TABLE t2, t1;
8 # modify child column NOT NULL ON DELETE CASCADE..parent column NULL
9 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
10 CREATE TABLE t2(f1 INT, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE CASCADE)ENGINE=InnoDB;
11 ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL;
12 DROP TABLE t2, t1;
13 # modify child column NOT NULL ON UPDATE SET NULL
14 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
15 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY(f1) REFERENCES t1(f1) ON UPDATE SET NULL)ENGINE=InnoDB;
16 ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL;
17 ERROR HY000: Column 'f1' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
18 DROP TABLE t2, t1;
19 # modify child column NOT NULL ON DELETE 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 (f2) REFERENCES t1(f2) ON DELETE SET NULL)ENGINE=InnoDB;
22 ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
23 ERROR HY000: Column 'f2' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
24 DROP TABLE t2, t1;
25 # modify child column NOT NULL ON UPDATE RESTRICT..parent column NULL
26 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
27 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON UPDATE RESTRICT)ENGINE=InnoDB;
28 ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
29 DROP TABLE t2, t1;
30 # modify child column NOT NULL ON DELETE RESTRICT..parent column NULL
31 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
32 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE RESTRICT)ENGINE=InnoDB;
33 ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
34 DROP TABLE t2, t1;
35 # modify child column NOT NULL ON UPDATE NO ACTION..PARENT COLUMN NULL
36 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
37 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON UPDATE NO ACTION)ENGINE=InnoDB;
38 ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
39 DROP TABLE t2, t1;
40 # modify child column NOT NULL ON DELETE NO ACTION..PARENT COLUMN NULL
41 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
42 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE NO ACTION)ENGINE=InnoDB;
43 ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
44 DROP TABLE t2, t1;
45 # modify parent column NULL ON UPDATE CASCADE child column NOT NULL
46 CREATE TABLE `t#1`(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
47 CREATE TABLE `t#2`(f1 INT NOT NULL,
48 FOREIGN KEY(f1) REFERENCES `t#1`(f2)
49 ON UPDATE CASCADE)ENGINE=InnoDB;
50 ALTER TABLE `t#1` MODIFY COLUMN f2 INT;
51 ERROR HY000: Cannot change column 'f2': used in a foreign key constraint 't#2_ibfk_1' of table 'test.t#2'
52 DROP TABLE `t#2`, `t#1`;
53 # modify parent column NULL ON DELETE CASCADE child column NOT NULL
54 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
55 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE CASCADE)ENGINE=InnoDB;
56 ALTER TABLE t1 MODIFY COLUMN f2 INT;
57 DROP TABLE t2, t1;
58 # modify parent column NULL ON UPDATE SET NULL child column NOT NULL
59 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
60 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE SET NULL)ENGINE=InnoDB;
61 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
62 DROP TABLE t1;
63 # modify parent column NULL ON DELETE SET NULL child NOT NULL
64 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
65 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE SET NULL)ENGINE=InnoDB;
66 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
67 DROP TABLE t1;
68 # modify parent column NULL ON UPDATE RESTRICT child column NOT NULL
69 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
70 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE RESTRICT)ENGINE=InnoDB;
71 ALTER TABLE t1 MODIFY COLUMN f2 INT;
72 DROP TABLE t2, t1;
73 # modify parent column NULL ON DELETE RESTRICT child column NOT NULL
74 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
75 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE RESTRICT)ENGINE=InnoDB;
76 ALTER TABLE t1 MODIFY COLUMN f2 INT;
77 DROP TABLE t2, t1;
78 # modify parent column NULL ON UPDATE NO ACTION child column NOT NULL
79 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
80 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE NO ACTION)ENGINE=InnoDB;
81 ALTER TABLE t1 MODIFY COLUMN f2 INT;
82 DROP TABLE t2, t1;
83 # modify parent column NULL ON DELETE NO ACTION child column NOT NULL
84 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
85 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE NO ACTION)ENGINE=InnoDB;
86 ALTER TABLE t1 MODIFY COLUMN f2 INT;
87 DROP TABLE t2, t1;
88 # foreign key constraint for multiple columns
89 # modify parent column NULL ON UPDATE CASCADE child column NOT NULL
90 CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL,
91 INDEX(f1, f2))ENGINE=InnoDB;
92 CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL,
93 INDEX(f1, f2),
94 FOREIGN KEY(f1, f2) REFERENCES t1(f1, f2) ON
95 UPDATE CASCADE)ENGINE=InnoDB;
96 ALTER TABLE t1 MODIFY COLUMN f1 INT;
97 ERROR HY000: Cannot change column 'f1': used in a foreign key constraint 't2_ibfk_1' of table 'test.t2'
98 DROP TABLE t2, t1;
99 # modify child column NOT NULL ON UPDATE CASCADE parent column NULL
100 CREATE TABLE t1(f1 INT, f2 INT, INDEX(f1, f2))ENGINE=InnoDB;
101 CREATE TABLE t2(f1 INT, f2 INT, INDEX(f1, f2),
102 FOREIGN KEY(f1, f2) REFERENCES t1(f1, f2) ON
103 UPDATE CASCADE)ENGINE=InnoDB;
104 ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL;
105 ERROR HY000: Column 'f2' cannot be NOT NULL: needed in a foreign key constraint 't2_ibfk_1' SET NULL
106 DROP TABLE t2, t1;
107 # allow foreign key constraints when parent table created later
108 SET FOREIGN_KEY_CHECKS=0;
109 CREATE TABLE t2(f1 INT, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
110 SET FOREIGN_KEY_CHECKS=1;
111 ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL;
112 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
113 INSERT INTO t1 VALUES(1, 1);
114 INSERT INTO t2 VALUES(1);
115 UPDATE t1 SET f2= NULL;
116 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`f1`) REFERENCES `t1` (`f2`) ON UPDATE CASCADE)
117 SELECT * FROM t2;
118 f1
119 1
120 SET FOREIGN_KEY_CHECKS=0;
121 UPDATE t1 SET f2= NULL;
122 SELECT * FROM t2;
123 f1
124 1
125 DROP TABLE t2, t1;
126 # Modify column + Drop column & Drop foreign key constraint
127 CREATE TABLE t1(f1 INT, f2 INT, KEY(f1), KEY(f2))ENGINE=InnoDB;
128 CREATE TABLE t2(f1 INT, f2 INT, f3 INT,
129 FOREIGN KEY fdx(f2) REFERENCES t1(f1),
130 FOREIGN KEY fdx2(f3) REFERENCES t1(f2))ENGINE=InnoDB;
131 ALTER TABLE t2 MODIFY f2 INT NOT NULL, DROP FOREIGN KEY fdx;
132 ALTER TABLE t2 ADD FOREIGN KEY fdx (f2) REFERENCES t1(f1);
133 ALTER TABLE t2 DROP COLUMN f2, DROP FOREIGN KEY fdx;
134 DROP TABLE t2, t1;
135 # Drop foreign index & modify column
136 CREATE TABLE `t#1`(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
137 CREATE TABLE `t#2`(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES `t#1`(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
138 SET FOREIGN_KEY_CHECKS=0;
139 ALTER TABLE `t#2` DROP INDEX f1;
140 SET FOREIGN_KEY_CHECKS=1;
141 ALTER TABLE `t#1` MODIFY COLUMN f2 INT;
142 DROP TABLE `t#2`, `t#1`;
143 # Drop referenced index and modify column
144 CREATE TABLE `t#1`(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
145 CREATE TABLE `t#2`(f1 INT, FOREIGN KEY(f1) REFERENCES `t#1`(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
146 SET FOREIGN_KEY_CHECKS=0;
147 ALTER TABLE `t#1` DROP INDEX f2;
148 SET FOREIGN_KEY_CHECKS=1;
149 ALTER TABLE `t#2` MODIFY COLUMN f1 INT NOT NULL;
150 DROP TABLE `t#2`, `t#1`;
151 # Self referential modifying column
152 CREATE TABLE t1(f1 INT, f2 INT, index(f2), foreign key(f1) references t1(f2) ON UPDATE CASCADE)engine=innodb;
153 ALTER TABLE t1 MODIFY COLUMN f2 INT NOT NULL;
154 ALTER TABLE t1 MODIFY COLUMN f1 INT NOT NULL;
155 ALTER TABLE t1 MODIFY COLUMN f1 INT;
156 DROP TABLE t1;