Debian Codegraph @

mysql-test/suite/ndbcluster/fk.result

raw
1 #
2 # This file contains basic tests of the SQL layer handling of
3 # foreign keys in the NDB storage engine.
4 #
5 # A non-unique index cannot serve as parent index.
6 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
7 INDEX ix(i),
8 FOREIGN KEY (j) REFERENCES self_ref(i)) ENGINE=NDB;
9 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ref_fk_1' in the referenced table 'self_ref'
10 # But a unique index is fine.
11 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
12 UNIQUE INDEX ix(i),
13 CONSTRAINT fk_j_unique_i FOREIGN KEY (j)
14 REFERENCES self_ref(i)
15 ) ENGINE=NDB;
16 SHOW CREATE TABLE self_ref;
17 Table Create Table
18 self_ref CREATE TABLE `self_ref` (
19 `pk` int NOT NULL,
20 `i` int DEFAULT NULL,
21 `j` int DEFAULT NULL,
22 PRIMARY KEY (`pk`),
23 UNIQUE KEY `ix` (`i`),
24 KEY `fk_j_unique_i` (`j`),
25 CONSTRAINT `fk_j_unique_i` FOREIGN KEY (`j`) REFERENCES `self_ref` (`i`)
26 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
27 DROP TABLE self_ref;
28 # A self referencing FK can use the supporting index as parent index
29 # if it is unique.
30 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT,
31 UNIQUE INDEX ix(i),
32 CONSTRAINT fk_i_unique_i FOREIGN KEY (i)
33 REFERENCES self_ref(i)
34 ) ENGINE=NDB;
35 SHOW CREATE TABLE self_ref;
36 Table Create Table
37 self_ref CREATE TABLE `self_ref` (
38 `pk` int NOT NULL,
39 `i` int DEFAULT NULL,
40 PRIMARY KEY (`pk`),
41 UNIQUE KEY `ix` (`i`),
42 CONSTRAINT `fk_i_unique_i` FOREIGN KEY (`i`) REFERENCES `self_ref` (`i`)
43 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
44 DROP TABLE self_ref;
45 # But not if it's not unique.
46 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT,
47 INDEX ix(i),
48 FOREIGN KEY (i) REFERENCES self_ref(i)) ENGINE=NDB;
49 ERROR HY000: Failed to add the foreign key constraint. Missing index for constraint 'self_ref_fk_1' in the referenced table 'self_ref'
50 # If there is an index with the same columns as the referenced ones,
51 # but in different order, the index may be used as parent index. This
52 # index may also serve as supporting index.
53 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
54 UNIQUE INDEX ijx(i, j),
55 CONSTRAINT fk_ij_unique_ji FOREIGN KEY (i, j)
56 REFERENCES self_ref(j, i)
57 ) ENGINE=NDB;
58 SHOW CREATE TABLE self_ref;
59 Table Create Table
60 self_ref CREATE TABLE `self_ref` (
61 `pk` int NOT NULL,
62 `i` int DEFAULT NULL,
63 `j` int DEFAULT NULL,
64 PRIMARY KEY (`pk`),
65 UNIQUE KEY `ijx` (`i`,`j`),
66 CONSTRAINT `fk_ij_unique_ji` FOREIGN KEY (`i`, `j`) REFERENCES `self_ref` (`j`, `i`)
67 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
68 CREATE TABLE self_ref_hash(pk INT PRIMARY KEY, i INT, j INT,
69 UNIQUE INDEX ijx(i, j) USING HASH,
70 CONSTRAINT fk_ij_unique_hash_ji FOREIGN KEY (i, j)
71 REFERENCES self_ref_hash(j, i)
72 ) ENGINE=NDB;
73 Warnings:
74 Warning 1121 Ndb does not support unique index on NULL valued attributes, index access with NULL value will become full table scan
75 SHOW CREATE TABLE self_ref_hash;
76 Table Create Table
77 self_ref_hash CREATE TABLE `self_ref_hash` (
78 `pk` int NOT NULL,
79 `i` int DEFAULT NULL,
80 `j` int DEFAULT NULL,
81 PRIMARY KEY (`pk`),
82 UNIQUE KEY `ijx` (`i`,`j`) USING HASH,
83 CONSTRAINT `fk_ij_unique_hash_ji` FOREIGN KEY (`i`, `j`) REFERENCES `self_ref_hash` (`j`, `i`)
84 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
85 CREATE TABLE self_ref_btree(pk INT PRIMARY KEY, i INT, j INT,
86 UNIQUE INDEX ijx(i, j) USING BTREE,
87 CONSTRAINT fk_ij_unique_btree_ji FOREIGN KEY (i, j)
88 REFERENCES self_ref_btree(j, i)
89 ) ENGINE=NDB;
90 SHOW CREATE TABLE self_ref_btree;
91 Table Create Table
92 self_ref_btree CREATE TABLE `self_ref_btree` (
93 `pk` int NOT NULL,
94 `i` int DEFAULT NULL,
95 `j` int DEFAULT NULL,
96 PRIMARY KEY (`pk`),
97 UNIQUE KEY `ijx` (`i`,`j`) USING BTREE,
98 CONSTRAINT `fk_ij_unique_btree_ji` FOREIGN KEY (`i`, `j`) REFERENCES `self_ref_btree` (`j`, `i`)
99 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
100 DROP TABLES self_ref, self_ref_hash, self_ref_btree;
101 # If there is an index with the same columns as the FK, but in
102 # different order, a supporting key will be created.
103 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
104 UNIQUE INDEX ijx(j, i),
105 CONSTRAINT fk_ij_diff_order_ji FOREIGN KEY (i, j)
106 REFERENCES self_ref(j, i)
107 ) ENGINE=NDB;
108 SHOW CREATE TABLE self_ref;
109 Table Create Table
110 self_ref CREATE TABLE `self_ref` (
111 `pk` int NOT NULL,
112 `i` int DEFAULT NULL,
113 `j` int DEFAULT NULL,
114 PRIMARY KEY (`pk`),
115 UNIQUE KEY `ijx` (`j`,`i`),
116 KEY `fk_ij_diff_order_ji` (`i`,`j`),
117 CONSTRAINT `fk_ij_diff_order_ji` FOREIGN KEY (`i`, `j`) REFERENCES `self_ref` (`j`, `i`)
118 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
119 DROP TABLE self_ref;
120 #
121 # Additional coverage for bug#25722927 "NEWDD FK: ALTER TABLE CHANGE
122 # COLUMN TYPE SHOULD CHECK FK CONSTRAINT".
123 #
124 # Test how foreign key column compatibility checks work for NDB SE.
125 #
126 SET @save_storage_engine= @@default_storage_engine;
127 SET default_storage_engine=ndbcluster;
128 #
129 # 1) Some basic tests that compatibility checks are performed during
130 # various foreign key operations.
131 CREATE TABLE parent (pk INT PRIMARY KEY);
132 CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
133 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
134 SET FOREIGN_KEY_CHECKS=0;
135 CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
136 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
137 SET FOREIGN_KEY_CHECKS=1;
138 CREATE TABLE child (fk CHAR(10));
139 ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
140 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
141 DROP TABLE child;
142 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
143 ALTER TABLE child MODIFY fk CHAR(10);
144 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
145 ALTER TABLE parent MODIFY pk CHAR(10);
146 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
147 DROP TABLES child, parent;
148 #
149 # 2) Test how compatibility rules work for various types.
150 # Different engines have different rules, so the below
151 # part of test is specific to NDB.
152 #
153 # We don't check compatibility for BLOB based and BIT types as
154 # they are not supported in foreign keys by NDB. Also we don't
155 # provide coverage for legacy types.
156 #
157 # 2.a) For integer types both exact type and signedness should match.
158 CREATE TABLE parent (pk INT PRIMARY KEY);
159 # Foreign keys over integer types are supported.
160 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
161 DROP TABLE child;
162 CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
163 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
164 CREATE TABLE child (fk BIGINT, FOREIGN KEY (fk) REFERENCES parent(pk));
165 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
166 CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
167 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
168 ALTER TABLE parent MODIFY pk INT UNSIGNED;
169 CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
170 ALTER TABLE child MODIFY fk INT;
171 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
172 DROP TABLE child, parent;
173 # Integer types are not compatible with other types.
174 CREATE TABLE parent (pk INT PRIMARY KEY);
175 CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
176 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
177 CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
178 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
179 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
180 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
181 CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
182 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
183 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
184 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
185 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
186 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
187 CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
188 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
189 CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
190 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
191 DROP TABLE parent;
192 #
193 # 2.b) For floating point types only the exact type matters.
194 CREATE TABLE parent (pk DOUBLE PRIMARY KEY);
195 # Though using such types in foreign key is EXTREMELY bad idea they
196 # are supported.
197 CREATE TABLE child (fk DOUBLE, FOREIGN KEY (fk) REFERENCES parent(pk));
198 DROP TABLE child;
199 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
200 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
201 # Signedness doesn't matter.
202 CREATE TABLE child (fk DOUBLE UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
203 Warnings:
204 Warning 1681 UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
205 DROP TABLES child, parent;
206 # Floating point types are not compatible with other types.
207 CREATE TABLE parent (pk FLOAT PRIMARY KEY);
208 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
209 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
210 CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
211 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
212 CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
213 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
214 CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
215 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
216 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
217 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
218 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
219 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
220 CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
221 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
222 CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
223 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
224 DROP TABLE parent;
225 #
226 # 2.c) DECIMAL type is compatible with other DECIMAL columns with
227 # the same signedness, precision and scale.
228 #
229 CREATE TABLE parent (pk DECIMAL(8,2) PRIMARY KEY);
230 # Foreign keys over DECIMAL columns are supported.
231 CREATE TABLE child (fk DECIMAL(8,2), FOREIGN KEY (fk) REFERENCES parent(pk));
232 DROP TABLE child;
233 # Signedness, precision and scale do matter.
234 CREATE TABLE child (fk DECIMAL(8,2) UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
235 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
236 CREATE TABLE child (fk DECIMAL(7,2), FOREIGN KEY (fk) REFERENCES parent(pk));
237 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
238 CREATE TABLE child (fk DECIMAL(8,3), FOREIGN KEY (fk) REFERENCES parent(pk));
239 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
240 CREATE TABLE child (fk DECIMAL(6,1), FOREIGN KEY (fk) REFERENCES parent(pk));
241 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
242 # DECIMAL types are not compatible with other types.
243 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
244 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
245 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
246 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
247 CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
248 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
249 CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
250 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
251 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
252 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
253 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
254 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
255 CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
256 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
257 CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
258 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
259 DROP TABLE parent;
260 #
261 # 2.d) Unlike InnoDB, NDB considers string types compatible if
262 # exact type, length and charset match.
263 CREATE TABLE parent (pk CHAR(10) PRIMARY KEY);
264 # Foreign keys are supported for both CHAR and VARCHAR.
265 CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
266 DROP TABLES child, parent;
267 CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY);
268 CREATE TABLE child (fk VARCHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
269 DROP TABLE child;
270 # Difference in size matters (even for VARCHAR!).
271 CREATE TABLE child (fk VARCHAR(11), FOREIGN KEY (fk) REFERENCES parent(pk));
272 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
273 # Difference in exact type matters too.
274 CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
275 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
276 DROP TABLES parent;
277 # Both columns must use the same collation.
278 CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY);
279 CREATE TABLE child (fk VARCHAR(10) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
280 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
281 CREATE TABLE child (fk VARCHAR(10) COLLATE utf8mb4_bin, FOREIGN KEY (fk) REFERENCES parent(pk));
282 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
283 # Binary strings are not compatible with non-binary strings.
284 CREATE TABLE child (fk VARBINARY(40), FOREIGN KEY (fk) REFERENCES parent(pk));
285 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
286 DROP TABLE parent;
287 # VARBINARY is not compatible with BINARY
288 CREATE TABLE parent (pk VARBINARY(10) PRIMARY KEY);
289 CREATE TABLE child (fk BINARY(10), FOREIGN KEY (fk) REFERENCES parent(pk));
290 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
291 DROP TABLES parent;
292 # Non-binary string types are incompatible with other types.
293 CREATE TABLE parent (pk CHAR(4) CHARACTER SET latin1 PRIMARY KEY);
294 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
295 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
296 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
297 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
298 CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
299 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
300 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
301 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
302 CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
303 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
304 ALTER TABLE parent MODIFY pk CHAR(1) CHARACTER SET latin1;
305 CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
306 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
307 CREATE TABLE child (fk ENUM('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
308 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
309 CREATE TABLE child (fk SET('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
310 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
311 DROP TABLE parent;
312 # Binary string types are incompatible with many other.
313 CREATE TABLE parent (pk BINARY(4) PRIMARY KEY);
314 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
315 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
316 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
317 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
318 CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
319 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
320 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
321 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
322 CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
323 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
324 ALTER TABLE parent MODIFY pk CHAR(1) CHARACTER SET latin1;
325 CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
326 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
327 CREATE TABLE child (fk ENUM('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
328 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
329 CREATE TABLE child (fk SET('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
330 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
331 DROP TABLE parent;
332 #
333 # 2.e) YEAR and DATE are compatible with themselves and nothing else.
334 #
335 CREATE TABLE parent (pk YEAR PRIMARY KEY);
336 CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
337 DROP TABLES child, parent;
338 CREATE TABLE parent (pk DATE PRIMARY KEY);
339 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
340 DROP TABLE child;
341 # DATE and YEAR types are not compatible with other types.
342 CREATE TABLE child (fk MEDIUMINT, FOREIGN KEY (fk) REFERENCES parent(pk));
343 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
344 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
345 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
346 CREATE TABLE child (fk DECIMAL(6,2), FOREIGN KEY (fk) REFERENCES parent(pk));
347 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
348 CREATE TABLE child (fk BINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
349 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
350 CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
351 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
352 CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
353 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
354 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
355 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
356 CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
357 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
358 CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
359 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
360 CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
361 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
362 DROP TABLE parent;
363 #
364 # 2.f) Temporals with fractional part are compatible with
365 # themselves, provided that scale stays the same.
366 #
367 CREATE TABLE parent (pk TIMESTAMP(6) PRIMARY KEY);
368 CREATE TABLE child (fk TIMESTAMP(6), FOREIGN KEY (fk) REFERENCES parent(pk));
369 DROP TABLES child, parent;
370 CREATE TABLE parent (pk DATETIME PRIMARY KEY);
371 CREATE TABLE child (fk DATETIME, FOREIGN KEY (fk) REFERENCES parent(pk));
372 DROP TABLE child, parent;
373 CREATE TABLE parent (pk TIME(2) PRIMARY KEY);
374 CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
375 DROP TABLE child;
376 # Scale matters.
377 CREATE TABLE child (fk TIME(0), FOREIGN KEY (fk) REFERENCES parent(pk));
378 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
379 CREATE TABLE child (fk TIME(1), FOREIGN KEY (fk) REFERENCES parent(pk));
380 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
381 # Temporals with fractional part are not compatible with other types.
382 CREATE TABLE child (fk MEDIUMINT, FOREIGN KEY (fk) REFERENCES parent(pk));
383 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
384 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
385 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
386 CREATE TABLE child (fk DECIMAL(6,2), FOREIGN KEY (fk) REFERENCES parent(pk));
387 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
388 CREATE TABLE child (fk BINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
389 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
390 CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
391 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
392 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
393 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
394 CREATE TABLE child (fk TIMESTAMP(2), FOREIGN KEY (fk) REFERENCES parent(pk));
395 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
396 CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
397 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
398 CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
399 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
400 DROP TABLE parent;
401 #
402 # 2.g) Columns of ENUM type are compatible with themselves.
403 #
404 # They are also compatible with CHAR(1..2) and SET types, but
405 # it is not clear if it is a bug or feature.
406 CREATE TABLE parent(pk ENUM('a') PRIMARY KEY);
407 # Foreign key over ENUMs are supported, element names and count do
408 # not matter provided that storage size is the same.
409 CREATE TABLE child (fk ENUM('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk));
410 DROP TABLE child;
411 # Storage size should match.
412 CREATE TABLE child (fk ENUM('a1','a2','a3','a4','a5','a6','a7','a8','a9','a10','a11','a12','a13','a14','a15','a16','a17','a18','a19','a20','a21','a22','a23','a24','a25','a26','a27','a28','a29','a30','a31','a32','a33','a34','a35','a36','a37','a38','a39','a40','a41','a42','a43','a44','a45','a46','a47','a48','a49','a50','a51','a52','a53','a54','a55','a56','a57','a58','a59','a60','a61','a62','a63','a64','a65','a66','a67','a68','a69','a70','a71','a72','a73','a74','a75','a76','a77','a78','a79','a80','a81','a82','a83','a84','a85','a86','a87','a88','a89','a90','a91','a92','a93','a94','a95','a96','a97','a98','a99','a100','a101','a102','a103','a104','a105','a106','a107','a108','a109','a110','a111','a112','a113','a114','a115','a116','a117','a118','a119','a120','a121','a122','a123','a124','a125','a126','a127','a128','a129','a130','a131','a132','a133','a134','a135','a136','a137','a138','a139','a140','a141','a142','a143','a144','a145','a146','a147','a148','a149','a150','a151','a152','a153','a154','a155','a156','a157','a158','a159','a160','a161','a162','a163','a164','a165','a166','a167','a168','a169','a170','a171','a172','a173','a174','a175','a176','a177','a178','a179','a180','a181','a182','a183','a184','a185','a186','a187','a188','a189','a190','a191','a192','a193','a194','a195','a196','a197','a198','a199','a200','a201','a202','a203','a204','a205','a206','a207','a208','a209','a210','a211','a212','a213','a214','a215','a216','a217','a218','a219','a220','a221','a222','a223','a224','a225','a226','a227','a228','a229','a230','a231','a232','a233','a234','a235','a236','a237','a238','a239','a240','a241','a242','a243','a244','a245','a246','a247','a248','a249','a250','a251','a252','a253','a254','a255','a256'), FOREIGN KEY (fk) REFERENCES parent(pk));
413 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
414 CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
415 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
416 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
417 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
418 CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk));
419 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
420 CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
421 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
422 CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
423 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
424 CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
425 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
426 CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk));
427 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
428 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
429 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
430 DROP TABLE parent;
431 #
432 # 2.h) Columns of SET type are compatible if storage size is
433 # the same.
434 #
435 # They are also compatible with CHAR(1..4) and ENUM types, but
436 # it is not clear if it is a bug or feature.
437 CREATE TABLE parent(pk SET('a') PRIMARY KEY);
438 # Foreign key over SETs are supported, element names and count do
439 # not matter provided that storage size is the same.
440 CREATE TABLE child (fk SET('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk));
441 DROP TABLE child;
442 # Storage size should match.
443 CREATE TABLE child (fk SET('a1','a2','a3','a4','a5','a6','a7','a8','a9'), FOREIGN KEY (fk) REFERENCES parent(pk));
444 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
445 CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
446 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
447 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
448 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
449 CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk));
450 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
451 CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
452 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
453 CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
454 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
455 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
456 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
457 CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk));
458 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
459 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
460 ERROR HY000: Referencing column 'fk' and referenced column 'pk' in foreign key constraint 'child_fk_1' are incompatible.
461 DROP TABLE parent;
462 SET default_storage_engine = @save_storage_engine;
463 #
464 # Additional test for bug#29173134 "FOREIGN KEY CONSTRAINT NAMES TAKING
465 # INDEX NAME".
466 #
467 CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=NDB;
468 CREATE TABLE t2 (fk1 INT, fk2 INT, fk3 INT, fk4 INT,
469 FOREIGN KEY (fk1) REFERENCES t1 (id),
470 CONSTRAINT c FOREIGN KEY (fk2) REFERENCES t1 (id),
471 FOREIGN KEY d (fk3) REFERENCES t1 (id),
472 CONSTRAINT e FOREIGN KEY f (fk4) REFERENCES t1 (id)
473 ) ENGINE=NDB;
474 SHOW CREATE TABLE t2;
475 Table Create Table
476 t2 CREATE TABLE `t2` (
477 `fk1` int DEFAULT NULL,
478 `fk2` int DEFAULT NULL,
479 `fk3` int DEFAULT NULL,
480 `fk4` int DEFAULT NULL,
481 KEY `fk1` (`fk1`),
482 KEY `c` (`fk2`),
483 KEY `d` (`fk3`),
484 KEY `e` (`fk4`),
485 CONSTRAINT `c` FOREIGN KEY (`fk2`) REFERENCES `t1` (`id`),
486 CONSTRAINT `e` FOREIGN KEY (`fk4`) REFERENCES `t1` (`id`),
487 CONSTRAINT `t2_fk_1` FOREIGN KEY (`fk1`) REFERENCES `t1` (`id`),
488 CONSTRAINT `t2_fk_2` FOREIGN KEY (`fk3`) REFERENCES `t1` (`id`)
489 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
490 SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
491 WHERE TABLE_NAME = 't2' ORDER BY CONSTRAINT_NAME;
492 CONSTRAINT_NAME
493 c
494 e
495 t2_fk_1
496 t2_fk_2
497 DROP TABLES t2, t1;
498 #
499 # Bug#30171959 "INCONSISTENT NAMING OF FOREIGN KEYS OVER NDB TABLES".
500 #
501 # Basic check of oreign key name generation by CREATE TABLE.
502 CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=NDB;
503 CREATE TABLE child (fk1 INT, fk2 INT, fk3 INT, fk4 INT, fk5 INT, fk6 INT,
504 fk7 INT, fk8 INT, fk9 INT, fk10 INT,
505 CONSTRAINT c1 FOREIGN KEY (fk1) REFERENCES parent (pk),
506 FOREIGN KEY (fk2) REFERENCES parent (pk),
507 FOREIGN KEY (fk3) REFERENCES parent (pk)) ENGINE=NDB;
508 SHOW CREATE TABLE test.child;
509 Table Create Table
510 child CREATE TABLE `child` (
511 `fk1` int DEFAULT NULL,
512 `fk2` int DEFAULT NULL,
513 `fk3` int DEFAULT NULL,
514 `fk4` int DEFAULT NULL,
515 `fk5` int DEFAULT NULL,
516 `fk6` int DEFAULT NULL,
517 `fk7` int DEFAULT NULL,
518 `fk8` int DEFAULT NULL,
519 `fk9` int DEFAULT NULL,
520 `fk10` int DEFAULT NULL,
521 KEY `c1` (`fk1`),
522 KEY `fk2` (`fk2`),
523 KEY `fk3` (`fk3`),
524 CONSTRAINT `c1` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`),
525 CONSTRAINT `child_fk_1` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`),
526 CONSTRAINT `child_fk_2` FOREIGN KEY (`fk3`) REFERENCES `parent` (`pk`)
527 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
528 Foreign keys consistent across NDB and DD
529 # Similar check for ALTER TABLE.
530 ALTER TABLE child ADD FOREIGN KEY (fk4) REFERENCES parent (pk),
531 ADD CONSTRAINT c2 FOREIGN KEY (fk5) REFERENCES parent (pk),
532 ADD FOREIGN KEY (fk6) REFERENCES parent (pk);
533 SHOW CREATE TABLE test.child;
534 Table Create Table
535 child CREATE TABLE `child` (
536 `fk1` int DEFAULT NULL,
537 `fk2` int DEFAULT NULL,
538 `fk3` int DEFAULT NULL,
539 `fk4` int DEFAULT NULL,
540 `fk5` int DEFAULT NULL,
541 `fk6` int DEFAULT NULL,
542 `fk7` int DEFAULT NULL,
543 `fk8` int DEFAULT NULL,
544 `fk9` int DEFAULT NULL,
545 `fk10` int DEFAULT NULL,
546 KEY `c1` (`fk1`),
547 KEY `fk2` (`fk2`),
548 KEY `fk3` (`fk3`),
549 KEY `fk4` (`fk4`),
550 KEY `c2` (`fk5`),
551 KEY `fk6` (`fk6`),
552 CONSTRAINT `c1` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`),
553 CONSTRAINT `c2` FOREIGN KEY (`fk5`) REFERENCES `parent` (`pk`),
554 CONSTRAINT `child_fk_1` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`),
555 CONSTRAINT `child_fk_2` FOREIGN KEY (`fk3`) REFERENCES `parent` (`pk`),
556 CONSTRAINT `child_fk_3` FOREIGN KEY (`fk4`) REFERENCES `parent` (`pk`),
557 CONSTRAINT `child_fk_4` FOREIGN KEY (`fk6`) REFERENCES `parent` (`pk`)
558 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
559 Foreign keys consistent across NDB and DD
560 # Adding foreign key with explicit name matching pattern of generated
561 # names should be correctly handled by increasing generated name counter.
562 ALTER TABLE child ADD CONSTRAINT child_fk_10 FOREIGN KEY (fk7) REFERENCES parent (pk);
563 ALTER TABLE child ADD FOREIGN KEY (fk8) REFERENCES parent (pk);
564 SHOW CREATE TABLE test.child;
565 Table Create Table
566 child CREATE TABLE `child` (
567 `fk1` int DEFAULT NULL,
568 `fk2` int DEFAULT NULL,
569 `fk3` int DEFAULT NULL,
570 `fk4` int DEFAULT NULL,
571 `fk5` int DEFAULT NULL,
572 `fk6` int DEFAULT NULL,
573 `fk7` int DEFAULT NULL,
574 `fk8` int DEFAULT NULL,
575 `fk9` int DEFAULT NULL,
576 `fk10` int DEFAULT NULL,
577 KEY `c1` (`fk1`),
578 KEY `fk2` (`fk2`),
579 KEY `fk3` (`fk3`),
580 KEY `fk4` (`fk4`),
581 KEY `c2` (`fk5`),
582 KEY `fk6` (`fk6`),
583 KEY `child_fk_10` (`fk7`),
584 KEY `fk8` (`fk8`),
585 CONSTRAINT `c1` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`),
586 CONSTRAINT `c2` FOREIGN KEY (`fk5`) REFERENCES `parent` (`pk`),
587 CONSTRAINT `child_fk_1` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`),
588 CONSTRAINT `child_fk_10` FOREIGN KEY (`fk7`) REFERENCES `parent` (`pk`),
589 CONSTRAINT `child_fk_11` FOREIGN KEY (`fk8`) REFERENCES `parent` (`pk`),
590 CONSTRAINT `child_fk_2` FOREIGN KEY (`fk3`) REFERENCES `parent` (`pk`),
591 CONSTRAINT `child_fk_3` FOREIGN KEY (`fk4`) REFERENCES `parent` (`pk`),
592 CONSTRAINT `child_fk_4` FOREIGN KEY (`fk6`) REFERENCES `parent` (`pk`)
593 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
594 Foreign keys consistent across NDB and DD
595 # Dropping foreign key with generated name doesn't cause name changes for
596 # other foreign keys with generated names.
597 ALTER TABLE child DROP FOREIGN KEY child_fk_4;
598 SHOW CREATE TABLE test.child;
599 Table Create Table
600 child CREATE TABLE `child` (
601 `fk1` int DEFAULT NULL,
602 `fk2` int DEFAULT NULL,
603 `fk3` int DEFAULT NULL,
604 `fk4` int DEFAULT NULL,
605 `fk5` int DEFAULT NULL,
606 `fk6` int DEFAULT NULL,
607 `fk7` int DEFAULT NULL,
608 `fk8` int DEFAULT NULL,
609 `fk9` int DEFAULT NULL,
610 `fk10` int DEFAULT NULL,
611 KEY `c1` (`fk1`),
612 KEY `fk2` (`fk2`),
613 KEY `fk3` (`fk3`),
614 KEY `fk4` (`fk4`),
615 KEY `c2` (`fk5`),
616 KEY `fk6` (`fk6`),
617 KEY `child_fk_10` (`fk7`),
618 KEY `fk8` (`fk8`),
619 CONSTRAINT `c1` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`),
620 CONSTRAINT `c2` FOREIGN KEY (`fk5`) REFERENCES `parent` (`pk`),
621 CONSTRAINT `child_fk_1` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`),
622 CONSTRAINT `child_fk_10` FOREIGN KEY (`fk7`) REFERENCES `parent` (`pk`),
623 CONSTRAINT `child_fk_11` FOREIGN KEY (`fk8`) REFERENCES `parent` (`pk`),
624 CONSTRAINT `child_fk_2` FOREIGN KEY (`fk3`) REFERENCES `parent` (`pk`),
625 CONSTRAINT `child_fk_3` FOREIGN KEY (`fk4`) REFERENCES `parent` (`pk`)
626 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
627 Foreign keys consistent across NDB and DD
628 # Generation of new names is not affected by presence of holes
629 # in the sequence of generated names.
630 ALTER TABLE child ADD FOREIGN KEY (fk9) REFERENCES parent (pk);
631 SHOW CREATE TABLE test.child;
632 Table Create Table
633 child CREATE TABLE `child` (
634 `fk1` int DEFAULT NULL,
635 `fk2` int DEFAULT NULL,
636 `fk3` int DEFAULT NULL,
637 `fk4` int DEFAULT NULL,
638 `fk5` int DEFAULT NULL,
639 `fk6` int DEFAULT NULL,
640 `fk7` int DEFAULT NULL,
641 `fk8` int DEFAULT NULL,
642 `fk9` int DEFAULT NULL,
643 `fk10` int DEFAULT NULL,
644 KEY `c1` (`fk1`),
645 KEY `fk2` (`fk2`),
646 KEY `fk3` (`fk3`),
647 KEY `fk4` (`fk4`),
648 KEY `c2` (`fk5`),
649 KEY `fk6` (`fk6`),
650 KEY `child_fk_10` (`fk7`),
651 KEY `fk8` (`fk8`),
652 KEY `fk9` (`fk9`),
653 CONSTRAINT `c1` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`),
654 CONSTRAINT `c2` FOREIGN KEY (`fk5`) REFERENCES `parent` (`pk`),
655 CONSTRAINT `child_fk_1` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`),
656 CONSTRAINT `child_fk_10` FOREIGN KEY (`fk7`) REFERENCES `parent` (`pk`),
657 CONSTRAINT `child_fk_11` FOREIGN KEY (`fk8`) REFERENCES `parent` (`pk`),
658 CONSTRAINT `child_fk_12` FOREIGN KEY (`fk9`) REFERENCES `parent` (`pk`),
659 CONSTRAINT `child_fk_2` FOREIGN KEY (`fk3`) REFERENCES `parent` (`pk`),
660 CONSTRAINT `child_fk_3` FOREIGN KEY (`fk4`) REFERENCES `parent` (`pk`)
661 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
662 Foreign keys consistent across NDB and DD
663 # However, if foreign key with last generated name is dropped,
664 # name can be reused later.
665 ALTER TABLE child DROP FOREIGN KEY child_fk_12;
666 ALTER TABLE child ADD FOREIGN KEY (fk10) REFERENCES parent (pk);
667 SHOW CREATE TABLE test.child;
668 Table Create Table
669 child CREATE TABLE `child` (
670 `fk1` int DEFAULT NULL,
671 `fk2` int DEFAULT NULL,
672 `fk3` int DEFAULT NULL,
673 `fk4` int DEFAULT NULL,
674 `fk5` int DEFAULT NULL,
675 `fk6` int DEFAULT NULL,
676 `fk7` int DEFAULT NULL,
677 `fk8` int DEFAULT NULL,
678 `fk9` int DEFAULT NULL,
679 `fk10` int DEFAULT NULL,
680 KEY `c1` (`fk1`),
681 KEY `fk2` (`fk2`),
682 KEY `fk3` (`fk3`),
683 KEY `fk4` (`fk4`),
684 KEY `c2` (`fk5`),
685 KEY `fk6` (`fk6`),
686 KEY `child_fk_10` (`fk7`),
687 KEY `fk8` (`fk8`),
688 KEY `fk9` (`fk9`),
689 KEY `fk10` (`fk10`),
690 CONSTRAINT `c1` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`),
691 CONSTRAINT `c2` FOREIGN KEY (`fk5`) REFERENCES `parent` (`pk`),
692 CONSTRAINT `child_fk_1` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`),
693 CONSTRAINT `child_fk_10` FOREIGN KEY (`fk7`) REFERENCES `parent` (`pk`),
694 CONSTRAINT `child_fk_11` FOREIGN KEY (`fk8`) REFERENCES `parent` (`pk`),
695 CONSTRAINT `child_fk_12` FOREIGN KEY (`fk10`) REFERENCES `parent` (`pk`),
696 CONSTRAINT `child_fk_2` FOREIGN KEY (`fk3`) REFERENCES `parent` (`pk`),
697 CONSTRAINT `child_fk_3` FOREIGN KEY (`fk4`) REFERENCES `parent` (`pk`)
698 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
699 Foreign keys consistent across NDB and DD
700 # Renaming of table updates generated names to preserve the pattern.
701 RENAME TABLE child TO child1;
702 SHOW CREATE TABLE test.child1;
703 Table Create Table
704 child1 CREATE TABLE `child1` (
705 `fk1` int DEFAULT NULL,
706 `fk2` int DEFAULT NULL,
707 `fk3` int DEFAULT NULL,
708 `fk4` int DEFAULT NULL,
709 `fk5` int DEFAULT NULL,
710 `fk6` int DEFAULT NULL,
711 `fk7` int DEFAULT NULL,
712 `fk8` int DEFAULT NULL,
713 `fk9` int DEFAULT NULL,
714 `fk10` int DEFAULT NULL,
715 KEY `c1` (`fk1`),
716 KEY `fk2` (`fk2`),
717 KEY `fk3` (`fk3`),
718 KEY `fk4` (`fk4`),
719 KEY `c2` (`fk5`),
720 KEY `fk6` (`fk6`),
721 KEY `child_fk_10` (`fk7`),
722 KEY `fk8` (`fk8`),
723 KEY `fk9` (`fk9`),
724 KEY `fk10` (`fk10`),
725 CONSTRAINT `c1` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`),
726 CONSTRAINT `c2` FOREIGN KEY (`fk5`) REFERENCES `parent` (`pk`),
727 CONSTRAINT `child1_fk_1` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`),
728 CONSTRAINT `child1_fk_10` FOREIGN KEY (`fk7`) REFERENCES `parent` (`pk`),
729 CONSTRAINT `child1_fk_11` FOREIGN KEY (`fk8`) REFERENCES `parent` (`pk`),
730 CONSTRAINT `child1_fk_12` FOREIGN KEY (`fk10`) REFERENCES `parent` (`pk`),
731 CONSTRAINT `child1_fk_2` FOREIGN KEY (`fk3`) REFERENCES `parent` (`pk`),
732 CONSTRAINT `child1_fk_3` FOREIGN KEY (`fk4`) REFERENCES `parent` (`pk`)
733 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
734 Foreign keys consistent across NDB and DD
735 # Similar test for ALTER TABLE RENAME.
736 ALTER TABLE child1 RENAME TO child2;
737 SHOW CREATE TABLE test.child2;
738 Table Create Table
739 child2 CREATE TABLE `child2` (
740 `fk1` int DEFAULT NULL,
741 `fk2` int DEFAULT NULL,
742 `fk3` int DEFAULT NULL,
743 `fk4` int DEFAULT NULL,
744 `fk5` int DEFAULT NULL,
745 `fk6` int DEFAULT NULL,
746 `fk7` int DEFAULT NULL,
747 `fk8` int DEFAULT NULL,
748 `fk9` int DEFAULT NULL,
749 `fk10` int DEFAULT NULL,
750 KEY `c1` (`fk1`),
751 KEY `fk2` (`fk2`),
752 KEY `fk3` (`fk3`),
753 KEY `fk4` (`fk4`),
754 KEY `c2` (`fk5`),
755 KEY `fk6` (`fk6`),
756 KEY `child_fk_10` (`fk7`),
757 KEY `fk8` (`fk8`),
758 KEY `fk9` (`fk9`),
759 KEY `fk10` (`fk10`),
760 CONSTRAINT `c1` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`),
761 CONSTRAINT `c2` FOREIGN KEY (`fk5`) REFERENCES `parent` (`pk`),
762 CONSTRAINT `child2_fk_1` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`),
763 CONSTRAINT `child2_fk_10` FOREIGN KEY (`fk7`) REFERENCES `parent` (`pk`),
764 CONSTRAINT `child2_fk_11` FOREIGN KEY (`fk8`) REFERENCES `parent` (`pk`),
765 CONSTRAINT `child2_fk_12` FOREIGN KEY (`fk10`) REFERENCES `parent` (`pk`),
766 CONSTRAINT `child2_fk_2` FOREIGN KEY (`fk3`) REFERENCES `parent` (`pk`),
767 CONSTRAINT `child2_fk_3` FOREIGN KEY (`fk4`) REFERENCES `parent` (`pk`)
768 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
769 Foreign keys consistent across NDB and DD
770 # Currently changing SE of table with foreign key is not supported.
771 # If it is ever supported we need to handle somehow difference in
772 # suffixes for generated foreign key names.
773 ALTER TABLE child2 ENGINE=InnoDB;
774 ERROR HY000: Cannot change table's storage engine because the table participates in a foreign key constraint.
775 SHOW CREATE TABLE child2;
776 Table Create Table
777 child2 CREATE TABLE `child2` (
778 `fk1` int DEFAULT NULL,
779 `fk2` int DEFAULT NULL,
780 `fk3` int DEFAULT NULL,
781 `fk4` int DEFAULT NULL,
782 `fk5` int DEFAULT NULL,
783 `fk6` int DEFAULT NULL,
784 `fk7` int DEFAULT NULL,
785 `fk8` int DEFAULT NULL,
786 `fk9` int DEFAULT NULL,
787 `fk10` int DEFAULT NULL,
788 KEY `c1` (`fk1`),
789 KEY `fk2` (`fk2`),
790 KEY `fk3` (`fk3`),
791 KEY `fk4` (`fk4`),
792 KEY `c2` (`fk5`),
793 KEY `fk6` (`fk6`),
794 KEY `child_fk_10` (`fk7`),
795 KEY `fk8` (`fk8`),
796 KEY `fk9` (`fk9`),
797 KEY `fk10` (`fk10`),
798 CONSTRAINT `c1` FOREIGN KEY (`fk1`) REFERENCES `parent` (`pk`),
799 CONSTRAINT `c2` FOREIGN KEY (`fk5`) REFERENCES `parent` (`pk`),
800 CONSTRAINT `child2_fk_1` FOREIGN KEY (`fk2`) REFERENCES `parent` (`pk`),
801 CONSTRAINT `child2_fk_10` FOREIGN KEY (`fk7`) REFERENCES `parent` (`pk`),
802 CONSTRAINT `child2_fk_11` FOREIGN KEY (`fk8`) REFERENCES `parent` (`pk`),
803 CONSTRAINT `child2_fk_12` FOREIGN KEY (`fk10`) REFERENCES `parent` (`pk`),
804 CONSTRAINT `child2_fk_2` FOREIGN KEY (`fk3`) REFERENCES `parent` (`pk`),
805 CONSTRAINT `child2_fk_3` FOREIGN KEY (`fk4`) REFERENCES `parent` (`pk`)
806 ) ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
807 SELECT constraint_name FROM information_schema.referential_constraints
808 WHERE constraint_schema='test' AND table_name='child2' ORDER BY constraint_name ASC;
809 CONSTRAINT_NAME
810 c1
811 c2
812 child2_fk_1
813 child2_fk_10
814 child2_fk_11
815 child2_fk_12
816 child2_fk_2
817 child2_fk_3
818 DROP TABLES child2, parent;
819 #
820 # Additional coverage for bug#30267236 "REPLACE DICT_FOREIGN_PARSE...
821 # WITH SQL-LAYER CHECK."
822 #
823 CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=NDB;
824 CREATE TABLE child (fk1 INT, b INT, CONSTRAINT c FOREIGN KEY (fk1) REFERENCES parent (pk)) ENGINE=NDB;
825 CREATE TABLE unrelated (a INT) ENGINE=NDB;
826 #
827 # Dropping non-existing foreign key should fail with consistent error.
828 ALTER TABLE child DROP FOREIGN KEY no_such_fk;
829 ERROR 42000: Can't DROP 'no_such_fk'; check that column/key exists
830 ALTER TABLE child DROP FOREIGN KEY no_such_fk, ALGORITHM=INPLACE;
831 ERROR 42000: Can't DROP 'no_such_fk'; check that column/key exists
832 ALTER TABLE child DROP FOREIGN KEY no_such_fk, ALGORITHM=COPY;
833 ERROR 42000: Can't DROP 'no_such_fk'; check that column/key exists
834 #
835 # Dropping existing foreign key using wrong table name should fail
836 # with consistent error as well.
837 ALTER TABLE unrelated DROP FOREIGN KEY c;
838 ERROR 42000: Can't DROP 'c'; check that column/key exists
839 ALTER TABLE unrelated DROP FOREIGN KEY c, ALGORITHM=INPLACE;
840 ERROR 42000: Can't DROP 'c'; check that column/key exists
841 ALTER TABLE unrelated DROP FOREIGN KEY c, ALGORITHM=COPY;
842 ERROR 42000: Can't DROP 'c'; check that column/key exists
843 #
844 # The same should happen if one tries to drop foreign key using
845 # parent table.
846 ALTER TABLE parent DROP FOREIGN KEY c;
847 ERROR 42000: Can't DROP 'c'; check that column/key exists
848 ALTER TABLE parent DROP FOREIGN KEY c, ALGORITHM=INPLACE;
849 ERROR 42000: Can't DROP 'c'; check that column/key exists
850 ALTER TABLE parent DROP FOREIGN KEY c, ALGORITHM=COPY;
851 ERROR 42000: Can't DROP 'c'; check that column/key exists
852 DROP TABLES unrelated, child, parent;