Debian Codegraph @

mysql-test/suite/innodb/t/foreign_null.test

raw
1 --source include/have_innodb.inc
2 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.");
3
4 let $MYSQLD_DATADIR= `select @@datadir`;
5 let $algorithm=`select regexp_replace('$MTR_COMBINATIONS', 'innodb,\|,innodb', '')`;
6
7 --echo # modify child column NOT NULL on UPDATE CASCADE..parent column NULL
8 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
9 CREATE TABLE t2(f1 INT, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
10 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
11 --error ER_FK_COLUMN_NOT_NULL
12 eval ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL,ALGORITHM=$algorithm;
13 DROP TABLE t2, t1;
14
15 --echo # modify child column NOT NULL ON DELETE CASCADE..parent column NULL
16 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
17 CREATE TABLE t2(f1 INT, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE CASCADE)ENGINE=InnoDB;
18 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
19 eval ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL,ALGORITHM=$algorithm;
20 DROP TABLE t2, t1;
21
22 --echo # modify child column NOT NULL ON UPDATE SET NULL
23 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
24 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY(f1) REFERENCES t1(f1) ON UPDATE SET NULL)ENGINE=InnoDB;
25 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
26 --error ER_FK_COLUMN_NOT_NULL
27 eval ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL,ALGORITHM=$algorithm;
28 DROP TABLE t2, t1;
29
30 --echo # modify child column NOT NULL ON DELETE SET 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 SET NULL)ENGINE=InnoDB;
33 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
34 --error ER_FK_COLUMN_NOT_NULL
35 eval ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL,ALGORITHM=$algorithm;
36 DROP TABLE t2, t1;
37
38 --echo # modify child column NOT NULL ON UPDATE RESTRICT..parent column NULL
39 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
40 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON UPDATE RESTRICT)ENGINE=InnoDB;
41 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
42 eval ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL,ALGORITHM=$algorithm;
43 DROP TABLE t2, t1;
44
45 --echo # modify child column NOT NULL ON DELETE RESTRICT..parent column NULL
46 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
47 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE RESTRICT)ENGINE=InnoDB;
48 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
49 eval ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL,ALGORITHM=$algorithm;
50 DROP TABLE t2, t1;
51
52 --echo # modify child column NOT NULL ON UPDATE NO ACTION..PARENT COLUMN NULL
53 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
54 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON UPDATE NO ACTION)ENGINE=InnoDB;
55 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
56 eval ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL,ALGORITHM=$algorithm;
57 DROP TABLE t2, t1;
58
59 --echo # modify child column NOT NULL ON DELETE NO ACTION..PARENT COLUMN NULL
60 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
61 CREATE TABLE t2(f1 INT, f2 INT, FOREIGN KEY (f2) REFERENCES t1(f2) ON DELETE NO ACTION)ENGINE=InnoDB;
62 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
63 eval ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL,ALGORITHM=$algorithm;
64 DROP TABLE t2, t1;
65
66 --echo # modify parent column NULL ON UPDATE CASCADE child column NOT NULL
67 CREATE TABLE `t#1`(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
68 CREATE TABLE `t#2`(f1 INT NOT NULL,
69 FOREIGN KEY(f1) REFERENCES `t#1`(f2)
70 ON UPDATE CASCADE)ENGINE=InnoDB;
71 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
72 --error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
73 eval ALTER TABLE `t#1` MODIFY COLUMN f2 INT,ALGORITHM=$algorithm;
74 DROP TABLE `t#2`, `t#1`;
75
76 --echo # modify parent column NULL ON DELETE CASCADE child column NOT NULL
77 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
78 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE CASCADE)ENGINE=InnoDB;
79 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
80 eval ALTER TABLE t1 MODIFY COLUMN f2 INT,ALGORITHM=$algorithm;
81 DROP TABLE t2, t1;
82
83 --echo # modify parent column NULL ON UPDATE SET NULL child column NOT NULL
84 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
85 --error ER_CANT_CREATE_TABLE
86 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE SET NULL)ENGINE=InnoDB;
87 DROP TABLE t1;
88
89 --echo # modify parent column NULL ON DELETE SET NULL child NOT NULL
90 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
91 --error ER_CANT_CREATE_TABLE
92 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE SET NULL)ENGINE=InnoDB;
93 DROP TABLE t1;
94
95 --echo # modify parent column NULL ON UPDATE RESTRICT child column NOT NULL
96 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
97 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE RESTRICT)ENGINE=InnoDB;
98 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
99 eval ALTER TABLE t1 MODIFY COLUMN f2 INT,ALGORITHM=$algorithm;
100 DROP TABLE t2, t1;
101
102 --echo # modify parent column NULL ON DELETE RESTRICT child column NOT NULL
103 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
104 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE RESTRICT)ENGINE=InnoDB;
105 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
106 eval ALTER TABLE t1 MODIFY COLUMN f2 INT,ALGORITHM=$algorithm;
107 DROP TABLE t2, t1;
108
109 --echo # modify parent column NULL ON UPDATE NO ACTION child column NOT NULL
110 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
111 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE NO ACTION)ENGINE=InnoDB;
112 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
113 eval ALTER TABLE t1 MODIFY COLUMN f2 INT,ALGORITHM=$algorithm;
114 DROP TABLE t2, t1;
115
116 --echo # modify parent column NULL ON DELETE NO ACTION child column NOT NULL
117 CREATE TABLE t1(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
118 CREATE TABLE t2(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t1(f2) ON DELETE NO ACTION)ENGINE=InnoDB;
119 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
120 eval ALTER TABLE t1 MODIFY COLUMN f2 INT,ALGORITHM=$algorithm;
121 DROP TABLE t2, t1;
122
123 --echo # foreign key constraint for multiple columns
124 --echo # modify parent column NULL ON UPDATE CASCADE child column NOT NULL
125 CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL,
126 INDEX(f1, f2))ENGINE=InnoDB;
127 CREATE TABLE t2(f1 INT NOT NULL, f2 INT NOT NULL,
128 INDEX(f1, f2),
129 FOREIGN KEY(f1, f2) REFERENCES t1(f1, f2) ON
130 UPDATE CASCADE)ENGINE=InnoDB;
131 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
132 --error ER_FK_COLUMN_CANNOT_CHANGE_CHILD
133 eval ALTER TABLE t1 MODIFY COLUMN f1 INT,ALGORITHM=$algorithm;
134 DROP TABLE t2, t1;
135
136 --echo # modify child column NOT NULL ON UPDATE CASCADE parent column NULL
137 CREATE TABLE t1(f1 INT, f2 INT, INDEX(f1, f2))ENGINE=InnoDB;
138 CREATE TABLE t2(f1 INT, f2 INT, INDEX(f1, f2),
139 FOREIGN KEY(f1, f2) REFERENCES t1(f1, f2) ON
140 UPDATE CASCADE)ENGINE=InnoDB;
141 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
142 --error ER_FK_COLUMN_NOT_NULL
143 eval ALTER TABLE t2 MODIFY COLUMN f2 INT NOT NULL,ALGORITHM=$algorithm;
144 DROP TABLE t2, t1;
145
146 --echo # allow foreign key constraints when parent table created later
147 SET FOREIGN_KEY_CHECKS=0;
148 CREATE TABLE t2(f1 INT, FOREIGN KEY(f1) REFERENCES t1(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
149 SET FOREIGN_KEY_CHECKS=1;
150 ALTER TABLE t2 MODIFY COLUMN f1 INT NOT NULL;
151 CREATE TABLE t1(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
152 INSERT INTO t1 VALUES(1, 1);
153 INSERT INTO t2 VALUES(1);
154 --error ER_ROW_IS_REFERENCED_2
155 UPDATE t1 SET f2= NULL;
156 SELECT * FROM t2;
157 SET FOREIGN_KEY_CHECKS=0;
158 UPDATE t1 SET f2= NULL;
159 SELECT * FROM t2;
160 DROP TABLE t2, t1;
161
162 --echo # Modify column + Drop column & Drop foreign key constraint
163 CREATE TABLE t1(f1 INT, f2 INT, KEY(f1), KEY(f2))ENGINE=InnoDB;
164 CREATE TABLE t2(f1 INT, f2 INT, f3 INT,
165 FOREIGN KEY fdx(f2) REFERENCES t1(f1),
166 FOREIGN KEY fdx2(f3) REFERENCES t1(f2))ENGINE=InnoDB;
167
168 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
169 eval ALTER TABLE t2 MODIFY f2 INT NOT NULL, DROP FOREIGN KEY fdx,ALGORITHM=$algorithm;
170
171 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
172 eval ALTER TABLE t2 ADD FOREIGN KEY fdx (f2) REFERENCES t1(f1),ALGORITHM=$algorithm;
173
174 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
175 eval ALTER TABLE t2 DROP COLUMN f2, DROP FOREIGN KEY fdx,ALGORITHM=$algorithm;
176 DROP TABLE t2, t1;
177
178 --echo # Drop foreign index & modify column
179 CREATE TABLE `t#1`(f1 INT, f2 INT NOT NULL, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
180 CREATE TABLE `t#2`(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES `t#1`(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
181
182 SET FOREIGN_KEY_CHECKS=0;
183 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
184 eval ALTER TABLE `t#2` DROP INDEX f1,ALGORITHM=$algorithm;
185 SET FOREIGN_KEY_CHECKS=1;
186
187 let $error_code=0;
188 if ($algorithm == "COPY")
189 {
190 let $error_code= ER_ERROR_ON_RENAME;
191 }
192
193 --replace_regex /#sql-alter-[0-9a-f_\-]*/#sql-alter/
194 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '' $MYSQLD_DATADIR ./;
195 --error $error_code
196 eval ALTER TABLE `t#1` MODIFY COLUMN f2 INT,ALGORITHM=$algorithm;
197 DROP TABLE `t#2`, `t#1`;
198
199 --echo # Drop referenced index and modify column
200 CREATE TABLE `t#1`(f1 INT, f2 INT, PRIMARY KEY(f1), KEY(f2))ENGINE=InnoDB;
201 CREATE TABLE `t#2`(f1 INT, FOREIGN KEY(f1) REFERENCES `t#1`(f2) ON UPDATE CASCADE)ENGINE=InnoDB;
202 SET FOREIGN_KEY_CHECKS=0;
203
204 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
205 eval ALTER TABLE `t#1` DROP INDEX f2,ALGORITHM=$algorithm;
206 SET FOREIGN_KEY_CHECKS=1;
207
208 --replace_regex /#sql-alter-[0-9a-f_\-]*/#sql-alter/
209 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '' $MYSQLD_DATADIR ./;
210 --error $error_code
211 eval ALTER TABLE `t#2` MODIFY COLUMN f1 INT NOT NULL,ALGORITHM=$algorithm;
212 DROP TABLE `t#2`, `t#1`;
213
214 --echo # Self referential modifying column
215 CREATE TABLE t1(f1 INT, f2 INT, index(f2), foreign key(f1) references t1(f2) ON UPDATE CASCADE)engine=innodb;
216
217 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
218 eval ALTER TABLE t1 MODIFY COLUMN f2 INT NOT NULL,ALGORITHM=$algorithm;
219
220 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
221 eval ALTER TABLE t1 MODIFY COLUMN f1 INT NOT NULL,ALGORITHM=$algorithm;
222
223 replace_result ,ALGORITHM=COPY '' ,ALGORITHM=INPLACE '';
224 eval ALTER TABLE t1 MODIFY COLUMN f1 INT,ALGORITHM=$algorithm;
225 DROP TABLE t1;