Debian Codegraph @

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

raw
1 create table parent (
2 a int primary key,
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,
9 b int not null,
10 c int not null,
11 unique(b) using hash,
12 index(c)) engine = ndb;
13 alter table child add constraint fk1 foreign key (a) references parent(a);
14 alter table child add constraint fk2 foreign key (b) references parent(a);
15 alter table child 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,
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 add constraint fk4 foreign key (a) references parent(b);
30 alter table child add constraint fk5 foreign key (b) references parent(b);
31 alter table child add constraint fk6 foreign key (c) references parent(b);
32 show create table child;
33 Table Create Table
34 child CREATE TABLE `child` (
35 `a` int NOT NULL,
36 `b` int NOT NULL,
37 `c` int NOT NULL,
38 PRIMARY KEY (`a`),
39 UNIQUE KEY `b` (`b`) USING HASH,
40 KEY `c` (`c`),
41 CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`),
42 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `parent` (`a`),
43 CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `parent` (`a`),
44 CONSTRAINT `fk4` FOREIGN KEY (`a`) REFERENCES `parent` (`b`),
45 CONSTRAINT `fk5` FOREIGN KEY (`b`) REFERENCES `parent` (`b`),
46 CONSTRAINT `fk6` FOREIGN KEY (`c`) REFERENCES `parent` (`b`)
47 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
48 alter table child algorithm=copy, add constraint fk7 foreign key (a) references parent(c);
49 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk7' in the referenced table 'parent'
50 alter table child algorithm=copy, add constraint fk8 foreign key (a) references parent(c);
51 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk8' in the referenced table 'parent'
52 alter table child algorithm=copy, add constraint fk9 foreign key (a) references parent(c);
53 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk9' in the referenced table 'parent'
54 alter table child algorithm=inplace, add constraint fk7 foreign key (a) references parent(c);
55 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk7' in the referenced table 'parent'
56 alter table child algorithm=inplace, add constraint fk8 foreign key (a) references parent(c);
57 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk8' in the referenced table 'parent'
58 alter table child algorithm=inplace, add constraint fk9 foreign key (a) references parent(c);
59 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'fk9' in the referenced table 'parent'
60 drop table child, parent;
61 create table parent (
62 a int primary key,
63 b int not null,
64 c int not null,
65 unique(b) using hash,
66 index(c)) engine = ndb;
67 create table child (
68 a int not null,
69 b int not null,
70 c int not null,
71 primary key (a),
72 unique key (b) using hash,
73 key (c),
74 constraint fk1 foreign key(a) references parent (a),
75 constraint fk2 foreign key(b) references parent (a),
76 constraint fk3 foreign key(c) references parent (a),
77 constraint fk4 foreign key(a) references parent (b),
78 constraint fk5 foreign key(b) references parent (b),
79 constraint fk6 foreign key(c) references parent (b)
80 ) engine=ndbcluster;
81 show create table child;
82 Table Create Table
83 child CREATE TABLE `child` (
84 `a` int NOT NULL,
85 `b` int NOT NULL,
86 `c` int NOT NULL,
87 PRIMARY KEY (`a`),
88 UNIQUE KEY `b` (`b`) USING HASH,
89 KEY `c` (`c`),
90 CONSTRAINT `fk1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`),
91 CONSTRAINT `fk2` FOREIGN KEY (`b`) REFERENCES `parent` (`a`),
92 CONSTRAINT `fk3` FOREIGN KEY (`c`) REFERENCES `parent` (`a`),
93 CONSTRAINT `fk4` FOREIGN KEY (`a`) REFERENCES `parent` (`b`),
94 CONSTRAINT `fk5` FOREIGN KEY (`b`) REFERENCES `parent` (`b`),
95 CONSTRAINT `fk6` FOREIGN KEY (`c`) REFERENCES `parent` (`b`)
96 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
97 drop table child, parent;
98 #
99 # Bug#24666177 : PARENT TABLE'S HANDLER NOT UPDATED AFTER ADDING A FK DEPENDENCY
100 #
101 #Setup tables
102 create table parent(
103 id int unique key
104 ) engine ndb;
105 create table child(
106 ref int
107 ) engine ndb;
108 insert into parent values(1), (2), (3);
109 insert into child values(1), (2), (3);
110 #Show create table on parent.
111 show create table parent;
112 Table Create Table
113 parent CREATE TABLE `parent` (
114 `id` int DEFAULT NULL,
115 UNIQUE KEY `id` (`id`)
116 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
117 #Now create a foreign key dependency
118 alter table child add constraint fk1 foreign key (ref) references parent(id);
119 #Now run a alter engine query.
120 alter table parent engine = innodb;
121 ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
122 show create table parent;
123 Table Create Table
124 parent CREATE TABLE `parent` (
125 `id` int DEFAULT NULL,
126 UNIQUE KEY `id` (`id`)
127 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
128 #Repeat same with truncate
129 truncate table child;
130 alter table parent engine = innodb;
131 ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
132 show create table parent;
133 Table Create Table
134 parent CREATE TABLE `parent` (
135 `id` int DEFAULT NULL,
136 UNIQUE KEY `id` (`id`)
137 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
138 #Repeat same test for dropping FK
139 alter table child drop foreign key fk1;
140 # Now this should succeed
141 alter table parent engine = innodb;
142 show create table parent;
143 Table Create Table
144 parent CREATE TABLE `parent` (
145 `id` int DEFAULT NULL,
146 UNIQUE KEY `id` (`id`)
147 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
148 #Cleanup
149 drop table parent, child;
150 #
151 # Bug#25859977 : WRONG ERROR MESSAGE SHOWN AFTER ALTER ADD FK FAILURE
152 #
153 # Setup
154 create table parent (
155 a int unique,
156 b int,
157 c int,
158 key b_key(b),
159 key c_key(c),
160 constraint fk_p1 foreign key (b) references parent(a)
161 ) engine ndb;
162 create table child (
163 a int,
164 b int,
165 key a_key(a),
166 key b_key(b),
167 constraint fk_c1 foreign key (a) references parent(a)
168 ) engine ndb;
169 # Try creating fk with already exisiting names. Should fail.
170 alter table parent add constraint fk_p1 foreign key (c) references parent(a);
171 ERROR HY000: Duplicate foreign key constraint name 'fk_p1'
172 show warnings;
173 Level Code Message
174 Error 1826 Duplicate foreign key constraint name 'fk_p1'
175 alter table child add constraint fk_c1 foreign key (b) references parent(a);
176 ERROR HY000: Duplicate foreign key constraint name 'fk_c1'
177 show warnings;
178 Level Code Message
179 Error 1826 Duplicate foreign key constraint name 'fk_c1'
180 # Check if error is thrown when creating table
181 create table test_fail (
182 a int,
183 b int,
184 key a_key(a),
185 key b_key(b),
186 constraint fk_t1 foreign key (a) references parent(a),
187 constraint fk_t1 foreign key (b) references parent(a)
188 ) engine ndb;
189 ERROR HY000: Duplicate foreign key constraint name 'fk_t1'
190 show warnings;
191 Level Code Message
192 Error 1826 Duplicate foreign key constraint name 'fk_t1'
193 # Using distinct names should work
194 alter table parent add constraint fk_p2 foreign key (b) references parent(a);
195 alter table parent add constraint fk_p3 foreign key (c) references parent(a);
196 alter table child add constraint fk_c2 foreign key (b) references parent(a);
197 #Show create tables
198 show create table parent;
199 Table Create Table
200 parent CREATE TABLE `parent` (
201 `a` int DEFAULT NULL,
202 `b` int DEFAULT NULL,
203 `c` int DEFAULT NULL,
204 UNIQUE KEY `a` (`a`),
205 KEY `b_key` (`b`),
206 KEY `c_key` (`c`),
207 CONSTRAINT `fk_p1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`),
208 CONSTRAINT `fk_p2` FOREIGN KEY (`b`) REFERENCES `parent` (`a`),
209 CONSTRAINT `fk_p3` FOREIGN KEY (`c`) REFERENCES `parent` (`a`)
210 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
211 show create table child;
212 Table Create Table
213 child CREATE TABLE `child` (
214 `a` int DEFAULT NULL,
215 `b` int DEFAULT NULL,
216 KEY `a_key` (`a`),
217 KEY `b_key` (`b`),
218 CONSTRAINT `fk_c1` FOREIGN KEY (`a`) REFERENCES `parent` (`a`),
219 CONSTRAINT `fk_c2` FOREIGN KEY (`b`) REFERENCES `parent` (`a`)
220 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
221 # Cleanup
222 drop table child, parent;
223 #
224 # Bug#25882950 : CACHED INDEX IN NDBAPI'S DICT CACHE NOT CLEARED AFTER `CREATE TABLE` FAILURE
225 #
226 # Create a table with bad fk reference
227 create table test(
228 a int,
229 foreign key fk1(a) references bogus_table(a)
230 ) engine ndb;
231 ERROR HY000: Failed to open the referenced table 'bogus_table'
232 # Now create a table with proper reference - it should pass
233 create table test (
234 a int unique,
235 b int,
236 foreign key fk1(b) references test(a)
237 ) engine ndb;
238 #Cleanup
239 drop table test;
240 #
241 # Bug#38534367 : Dropping foreign key (with drop constraint) causes assertion failure (bad dereference)
242 #
243 create table parent (
244 a int primary key,
245 b int
246 ) engine ndb;
247 create table child (
248 a int primary key,
249 b int,
250 c int
251 ) engine = ndb;
252 alter table child add foreign key(c) references parent(a);
253 alter table child drop constraint child_fk_1;
254 drop table parent, child;