Debian Codegraph @

mysql-test/suite/ndb/r/ndb_fk_alter.result

raw
1 create table parent (
2 a int primary key auto_increment,
3 b int not null,
4 c int not null,
5 unique(b) using hash,
6 index(c)) engine = ndb;
7 create table child (
8 a int primary key auto_increment,
9 b int not null,
10 c int not null,
11 unique(b) using hash,
12 index(c)) engine = ndb;
13 alter table child algorithm=inplace, add constraint fk1 foreign key (a) references parent(a);
14 alter table child algorithm=inplace, add constraint fk2 foreign key (b) references parent(a);
15 alter table child algorithm=inplace, add constraint fk3 foreign key (c) references parent(a);
16 show create table child;
17 Table Create Table
18 child CREATE TABLE `child` (
19 `a` int NOT NULL AUTO_INCREMENT,
20 `b` int NOT NULL,
21 `c` int NOT NULL,
22 PRIMARY KEY (`a`),
23 UNIQUE KEY `b` (`b`) USING HASH,
24 KEY `c` (`c`),
25 CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`),
26 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `parent` (`a`),
27 CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `parent` (`a`)
28 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
29 alter table child algorithm=inplace, drop foreign key fk1;
30 show create table child;
31 Table Create Table
32 child CREATE TABLE `child` (
33 `a` int NOT NULL AUTO_INCREMENT,
34 `b` int NOT NULL,
35 `c` int NOT NULL,
36 PRIMARY KEY (`a`),
37 UNIQUE KEY `b` (`b`) USING HASH,
38 KEY `c` (`c`),
39 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `parent` (`a`),
40 CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `parent` (`a`)
41 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
42 alter table child algorithm=inplace, drop foreign key fk2;
43 show create table child;
44 Table Create Table
45 child CREATE TABLE `child` (
46 `a` int NOT NULL AUTO_INCREMENT,
47 `b` int NOT NULL,
48 `c` int NOT NULL,
49 PRIMARY KEY (`a`),
50 UNIQUE KEY `b` (`b`) USING HASH,
51 KEY `c` (`c`),
52 CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `parent` (`a`)
53 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
54 alter table child algorithm=inplace, drop foreign key fk3;
55 show create table child;
56 Table Create Table
57 child CREATE TABLE `child` (
58 `a` int NOT NULL AUTO_INCREMENT,
59 `b` int NOT NULL,
60 `c` int NOT NULL,
61 PRIMARY KEY (`a`),
62 UNIQUE KEY `b` (`b`) USING HASH,
63 KEY `c` (`c`)
64 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
65 drop table parent, child;
66 CREATE TABLE bug46599a (
67 b VARCHAR(2), PRIMARY KEY(b)) ENGINE=ndb;
68 CREATE TABLE bug46599b (b VARCHAR(2),
69 CONSTRAINT fk1 FOREIGN KEY (b) REFERENCES bug46599a (b)) ENGINE=Ndb;
70 INSERT INTO bug46599a VALUES ('b');
71 INSERT INTO bug46599b VALUES ('b'), (NULL);
72 INSERT INTO bug46599b VALUES ('');
73 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`bug46599b`, CONSTRAINT `fk1` FOREIGN KEY (`b`) REFERENCES `bug46599a` (`b`) ON DELETE NO ACTION ON UPDATE NO ACTION)
74 SELECT * FROM bug46599a;
75 b
76 b
77 SELECT * FROM bug46599b;
78 b
79 NULL
80 b
81 ALTER TABLE bug46599b MODIFY COLUMN b VARCHAR(2) NOT NULL DEFAULT '';
82 ERROR HY000: Cannot change column 'b': used in a foreign key constraint 'fk1'
83 SELECT * FROM bug46599b;
84 b
85 NULL
86 b
87 DROP TABLE bug46599b,bug46599a;
88 create table t1(
89 col1 int not null primary key,
90 col2 int not null,
91 col3 varchar(35) not null,
92 unique name0 (col2),
93 unique name1 (col3)
94 ) engine=ndb;
95 create table t2(
96 col1 int not null unique,
97 col2 int not null,
98 col3 varchar(35),
99 constraint fk1 foreign key (col3) references t1(col3)
100 ) engine=ndb;
101 ALTER TABLE t1 DROP COLUMN col2;
102 drop table t2, t1;
103 set @i=2;
104 create table t1(
105 col1 int not null primary key,
106 col2 int not null unique,
107 col3 varchar(35)
108 ) engine=innodb;
109 create table t2(
110 col1 int not null unique,
111 col2 int not null,
112 col3 varchar(35),
113 constraint fk1 foreign key (col2) references t1(col1)
114 ) engine=innodb;
115 insert into t1(col1,col2,col3) values (2, 3, 'abcdefghijkl');
116 insert into t2(col1,col2,col3) values (1, 2, 'abcdefghijkl');
117 # Attempt to drop col1, should give error
118 ALTER TABLE t1 DROP COLUMN col1;
119 ERROR HY000: Cannot drop column 'col1': needed in a foreign key constraint 'fk1' of table 't2'
120 # Attempt to modify col1, should give error
121 ALTER TABLE t1 MODIFY COLUMN col1 bigint;
122 ERROR HY000: Referencing column 'col2' and referenced column 'col1' in foreign key constraint 'fk1' are incompatible.
123 # Drop the column t1.col3
124 ALTER TABLE t1 DROP COLUMN col3;
125 # Drop t2.col2
126 ALTER TABLE t2 DROP COLUMN col2;
127 Got one of the listed errors
128 drop table t2, t1;
129 set @i=1;
130 create table t1(
131 col1 int not null primary key,
132 col2 int not null unique,
133 col3 varchar(35)
134 ) engine=ndb;
135 create table t2(
136 col1 int not null unique,
137 col2 int not null,
138 col3 varchar(35),
139 constraint fk1 foreign key (col2) references t1(col1)
140 ) engine=ndb;
141 insert into t1(col1,col2,col3) values (2, 3, 'abcdefghijkl');
142 insert into t2(col1,col2,col3) values (1, 2, 'abcdefghijkl');
143 # Attempt to drop col1, should give error
144 ALTER TABLE t1 DROP COLUMN col1;
145 ERROR HY000: Cannot drop column 'col1': needed in a foreign key constraint 'fk1' of table 't2'
146 # Attempt to modify col1, should give error
147 ALTER TABLE t1 MODIFY COLUMN col1 bigint;
148 ERROR HY000: Referencing column 'col2' and referenced column 'col1' in foreign key constraint 'fk1' are incompatible.
149 # Drop the column t1.col3
150 ALTER TABLE t1 DROP COLUMN col3;
151 # Drop t2.col2
152 ALTER TABLE t2 DROP COLUMN col2;
153 Got one of the listed errors
154 drop table t2, t1;
155 #
156 # Test non-auto partitioning
157 #
158 CREATE TABLE t1
159 (col1 int NOT NULL PRIMARY KEY,
160 col2 int NOT NULL,
161 col3 varchar(35) NOT NULL,
162 UNIQUE KEY name0 (col2),
163 UNIQUE KEY name1 (col3)
164 )
165 ENGINE = NDB;
166 CREATE TABLE t2
167 (col1 int NOT NULL UNIQUE,
168 col2 int NOT NULL,
169 col3 varchar(35),
170 CONSTRAINT fk1 FOREIGN KEY (col3) REFERENCES t1 (col3)
171 )
172 ENGINE = NDB;
173 SHOW CREATE TABLE t1;
174 Table Create Table
175 t1 CREATE TABLE `t1` (
176 `col1` int NOT NULL,
177 `col2` int NOT NULL,
178 `col3` varchar(35) NOT NULL,
179 PRIMARY KEY (`col1`),
180 UNIQUE KEY `name0` (`col2`),
181 UNIQUE KEY `name1` (`col3`)
182 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
183 SHOW CREATE TABLE t2;
184 Table Create Table
185 t2 CREATE TABLE `t2` (
186 `col1` int NOT NULL,
187 `col2` int NOT NULL,
188 `col3` varchar(35) DEFAULT NULL,
189 UNIQUE KEY `col1` (`col1`),
190 KEY `fk1` (`col3`),
191 CONSTRAINT `fk1` FOREIGN KEY (`col3`) REFERENCES `t1` (`col3`)
192 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
193 ALTER TABLE t1 PARTITION BY KEY ();
194 SHOW CREATE TABLE t1;
195 Table Create Table
196 t1 CREATE TABLE `t1` (
197 `col1` int NOT NULL,
198 `col2` int NOT NULL,
199 `col3` varchar(35) NOT NULL,
200 PRIMARY KEY (`col1`),
201 UNIQUE KEY `name0` (`col2`),
202 UNIQUE KEY `name1` (`col3`)
203 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
204 /*!50100 PARTITION BY KEY () */
205 ALTER TABLE t1 PARTITION BY KEY (col1);
206 SHOW CREATE TABLE t1;
207 Table Create Table
208 t1 CREATE TABLE `t1` (
209 `col1` int NOT NULL,
210 `col2` int NOT NULL,
211 `col3` varchar(35) NOT NULL,
212 PRIMARY KEY (`col1`),
213 UNIQUE KEY `name0` (`col2`),
214 UNIQUE KEY `name1` (`col3`)
215 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
216 /*!50100 PARTITION BY KEY (col1) */
217 ALTER TABLE t1 REMOVE PARTITIONING;
218 SHOW CREATE TABLE t1;
219 Table Create Table
220 t1 CREATE TABLE `t1` (
221 `col1` int NOT NULL,
222 `col2` int NOT NULL,
223 `col3` varchar(35) NOT NULL,
224 PRIMARY KEY (`col1`),
225 UNIQUE KEY `name0` (`col2`),
226 UNIQUE KEY `name1` (`col3`)
227 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
228 ALTER TABLE t2 PARTITION BY KEY ();
229 SHOW CREATE TABLE t2;
230 Table Create Table
231 t2 CREATE TABLE `t2` (
232 `col1` int NOT NULL,
233 `col2` int NOT NULL,
234 `col3` varchar(35) DEFAULT NULL,
235 UNIQUE KEY `col1` (`col1`),
236 KEY `fk1` (`col3`),
237 CONSTRAINT `fk1` FOREIGN KEY (`col3`) REFERENCES `t1` (`col3`)
238 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
239 /*!50100 PARTITION BY KEY () */
240 ALTER TABLE t2 PARTITION BY KEY (col1);
241 SHOW CREATE TABLE t2;
242 Table Create Table
243 t2 CREATE TABLE `t2` (
244 `col1` int NOT NULL,
245 `col2` int NOT NULL,
246 `col3` varchar(35) DEFAULT NULL,
247 UNIQUE KEY `col1` (`col1`),
248 KEY `fk1` (`col3`),
249 CONSTRAINT `fk1` FOREIGN KEY (`col3`) REFERENCES `t1` (`col3`)
250 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
251 /*!50100 PARTITION BY KEY (col1) */
252 ALTER TABLE t2 REMOVE PARTITIONING;
253 SHOW CREATE TABLE t2;
254 Table Create Table
255 t2 CREATE TABLE `t2` (
256 `col1` int NOT NULL,
257 `col2` int NOT NULL,
258 `col3` varchar(35) DEFAULT NULL,
259 UNIQUE KEY `col1` (`col1`),
260 KEY `fk1` (`col3`),
261 CONSTRAINT `fk1` FOREIGN KEY (`col3`) REFERENCES `t1` (`col3`)
262 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
263 ALTER TABLE t1 RENAME COLUMN col3 TO col3_new;
264 ERROR 0A000: ALTER TABLE is not supported. Reason: Altering name of a field being referenced from a foreign key is not supported. Try dropping foreign key first.
265 ALTER TABLE t1 ALGORITHM=inplace, RENAME COLUMN col3 TO col3_new;
266 ERROR 0A000: ALTER TABLE is not supported. Reason: Altering name of a field being referenced from a foreign key is not supported. Try dropping foreign key first.
267 ALTER TABLE t2 RENAME COLUMN col3 TO col3_new;
268 ERROR 0A000: ALTER TABLE is not supported. Reason: Altering name of a field being referenced from a foreign key is not supported. Try dropping foreign key first.
269 ALTER TABLE t2 ALGORITHM=inplace, RENAME COLUMN col3 TO col3_new;
270 ERROR 0A000: ALTER TABLE is not supported. Reason: Altering name of a field being referenced from a foreign key is not supported. Try dropping foreign key first.
271 SHOW CREATE TABLE t1;
272 Table Create Table
273 t1 CREATE TABLE `t1` (
274 `col1` int NOT NULL,
275 `col2` int NOT NULL,
276 `col3` varchar(35) NOT NULL,
277 PRIMARY KEY (`col1`),
278 UNIQUE KEY `name0` (`col2`),
279 UNIQUE KEY `name1` (`col3`)
280 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
281 SHOW CREATE TABLE t2;
282 Table Create Table
283 t2 CREATE TABLE `t2` (
284 `col1` int NOT NULL,
285 `col2` int NOT NULL,
286 `col3` varchar(35) DEFAULT NULL,
287 UNIQUE KEY `col1` (`col1`),
288 KEY `fk1` (`col3`),
289 CONSTRAINT `fk1` FOREIGN KEY (`col3`) REFERENCES `t1` (`col3`)
290 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
291 DROP TABLE t2, t1;
292 #
293 # Bug#30233405 SELF REFERENTIAL FKS ARE LOST WHEN THE TABLE IS ALTERED USING COPY ALGORITHM
294 #
295 # Setup
296 CREATE TABLE self_ref (
297 pk INT PRIMARY KEY,
298 parent_col INT UNIQUE,
299 child_col INT,
300 FOREIGN KEY fk1(child_col) REFERENCES self_ref(parent_col)
301 ) ENGINE NDB;
302 SHOW CREATE TABLE self_ref;
303 Table Create Table
304 self_ref CREATE TABLE `self_ref` (
305 `pk` int NOT NULL,
306 `parent_col` int DEFAULT NULL,
307 `child_col` int DEFAULT NULL,
308 PRIMARY KEY (`pk`),
309 UNIQUE KEY `parent_col` (`parent_col`),
310 KEY `fk1` (`child_col`),
311 CONSTRAINT `self_ref_fk_1` FOREIGN KEY (`child_col`) REFERENCES `self_ref` (`parent_col`)
312 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
313 INSERT INTO self_ref VALUES(1, 1, NULL);
314 INSERT INTO self_ref VALUES(2, 2, 1);
315 INSERT INTO self_ref VALUES(3, 3, 6);
316 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`self_ref`, CONSTRAINT `self_ref_fk_1` FOREIGN KEY (`child_col`) REFERENCES `self_ref` (`parent_col`) ON DELETE NO ACTION ON UPDATE NO ACTION)
317 DELETE FROM self_ref WHERE parent_col = 1;
318 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`self_ref`, CONSTRAINT `self_ref_fk_1` FOREIGN KEY (`child_col`) REFERENCES `self_ref` (`parent_col`) ON DELETE NO ACTION ON UPDATE NO ACTION)
319 # Perform a COPY ALTER
320 SET foreign_key_checks= 0;
321 ALTER TABLE self_ref
322 ADD COLUMN dummy INT,
323 ALGORITHM=COPY;
324 SET foreign_key_checks= 1;
325 # Verify the foreign keys
326 SHOW CREATE TABLE self_ref;
327 Table Create Table
328 self_ref CREATE TABLE `self_ref` (
329 `pk` int NOT NULL,
330 `parent_col` int DEFAULT NULL,
331 `child_col` int DEFAULT NULL,
332 `dummy` int DEFAULT NULL,
333 PRIMARY KEY (`pk`),
334 UNIQUE KEY `parent_col` (`parent_col`),
335 KEY `fk1` (`child_col`),
336 CONSTRAINT `self_ref_fk_1` FOREIGN KEY (`child_col`) REFERENCES `self_ref` (`parent_col`)
337 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
338 INSERT INTO self_ref VALUES(3, 3, 1, 0);
339 INSERT INTO self_ref VALUES(4, 4, 8, 0);
340 ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`self_ref`, CONSTRAINT `self_ref_fk_1` FOREIGN KEY (`child_col`) REFERENCES `self_ref` (`parent_col`) ON DELETE NO ACTION ON UPDATE NO ACTION)
341 DELETE FROM self_ref WHERE parent_col = 1;
342 ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`self_ref`, CONSTRAINT `self_ref_fk_1` FOREIGN KEY (`child_col`) REFERENCES `self_ref` (`parent_col`) ON DELETE NO ACTION ON UPDATE NO ACTION)
343 # Cleanup
344 DROP TABLE self_ref;
345 #
346 # Bug#30512043 IGNORE CASE WHEN COMPARING FOREIGN KEY NAMES FROM DD WITH NDB
347 #
348 # Setup
349 create table parent(col1 int primary key) engine=ndbcluster;
350 create table child(col int) engine=ndbcluster;
351 # Create the foreign key with name in lowercase.
352 alter table child add constraint fk1 foreign key (col) references parent(col1) on delete set null;
353 # Check if the foreign key is added.
354 Show create table child;
355 Table Create Table
356 child CREATE TABLE `child` (
357 `col` int DEFAULT NULL,
358 KEY `fk1` (`col`),
359 CONSTRAINT `fk1` FOREIGN KEY (`col`) REFERENCES `parent` (`col1`) ON DELETE SET NULL
360 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
361 # Drop the foreign key using an uppercase identifier and check if the drop is successful.
362 alter table child drop foreign key FK1;
363 alter table child drop index FK1;
364 # Check if the foreign key is dropped successfully.
365 Show create table child;
366 Table Create Table
367 child CREATE TABLE `child` (
368 `col` int DEFAULT NULL
369 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
370 # Cleanup
371 drop table child, parent;