Debian Codegraph @

mysql-test/suite/ndbcluster/fk.test

raw
1 --source include/have_ndb.inc
2
3 --echo #
4 --echo # This file contains basic tests of the SQL layer handling of
5 --echo # foreign keys in the NDB storage engine.
6 --echo #
7
8 --echo # A non-unique index cannot serve as parent index.
9 --error ER_FK_NO_INDEX_PARENT
10 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
11 INDEX ix(i),
12 FOREIGN KEY (j) REFERENCES self_ref(i)) ENGINE=NDB;
13
14 --echo # But a unique index is fine.
15 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
16 UNIQUE INDEX ix(i),
17 CONSTRAINT fk_j_unique_i FOREIGN KEY (j)
18 REFERENCES self_ref(i)
19 ) ENGINE=NDB;
20 SHOW CREATE TABLE self_ref;
21 DROP TABLE self_ref;
22
23 --echo # A self referencing FK can use the supporting index as parent index
24 --echo # if it is unique.
25 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT,
26 UNIQUE INDEX ix(i),
27 CONSTRAINT fk_i_unique_i FOREIGN KEY (i)
28 REFERENCES self_ref(i)
29 ) ENGINE=NDB;
30 SHOW CREATE TABLE self_ref;
31 DROP TABLE self_ref;
32
33 --echo # But not if it's not unique.
34 --error ER_FK_NO_INDEX_PARENT
35 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT,
36 INDEX ix(i),
37 FOREIGN KEY (i) REFERENCES self_ref(i)) ENGINE=NDB;
38
39 --echo # If there is an index with the same columns as the referenced ones,
40 --echo # but in different order, the index may be used as parent index. This
41 --echo # index may also serve as supporting index.
42 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
43 UNIQUE INDEX ijx(i, j),
44 CONSTRAINT fk_ij_unique_ji FOREIGN KEY (i, j)
45 REFERENCES self_ref(j, i)
46 ) ENGINE=NDB;
47 SHOW CREATE TABLE self_ref;
48
49 CREATE TABLE self_ref_hash(pk INT PRIMARY KEY, i INT, j INT,
50 UNIQUE INDEX ijx(i, j) USING HASH,
51 CONSTRAINT fk_ij_unique_hash_ji FOREIGN KEY (i, j)
52 REFERENCES self_ref_hash(j, i)
53 ) ENGINE=NDB;
54 SHOW CREATE TABLE self_ref_hash;
55
56 CREATE TABLE self_ref_btree(pk INT PRIMARY KEY, i INT, j INT,
57 UNIQUE INDEX ijx(i, j) USING BTREE,
58 CONSTRAINT fk_ij_unique_btree_ji FOREIGN KEY (i, j)
59 REFERENCES self_ref_btree(j, i)
60 ) ENGINE=NDB;
61 SHOW CREATE TABLE self_ref_btree;
62 DROP TABLES self_ref, self_ref_hash, self_ref_btree;
63
64 --echo # If there is an index with the same columns as the FK, but in
65 --echo # different order, a supporting key will be created.
66 CREATE TABLE self_ref(pk INT PRIMARY KEY, i INT, j INT,
67 UNIQUE INDEX ijx(j, i),
68 CONSTRAINT fk_ij_diff_order_ji FOREIGN KEY (i, j)
69 REFERENCES self_ref(j, i)
70 ) ENGINE=NDB;
71 SHOW CREATE TABLE self_ref;
72 DROP TABLE self_ref;
73
74
75 --echo #
76 --echo # Additional coverage for bug#25722927 "NEWDD FK: ALTER TABLE CHANGE
77 --echo # COLUMN TYPE SHOULD CHECK FK CONSTRAINT".
78 --echo #
79 --echo # Test how foreign key column compatibility checks work for NDB SE.
80 --echo #
81
82 SET @save_storage_engine= @@default_storage_engine;
83 SET default_storage_engine=ndbcluster;
84
85 --echo #
86 --echo # 1) Some basic tests that compatibility checks are performed during
87 --echo # various foreign key operations.
88 CREATE TABLE parent (pk INT PRIMARY KEY);
89 --error ER_FK_INCOMPATIBLE_COLUMNS
90 CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
91 SET FOREIGN_KEY_CHECKS=0;
92 --error ER_FK_INCOMPATIBLE_COLUMNS
93 CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
94 SET FOREIGN_KEY_CHECKS=1;
95
96 CREATE TABLE child (fk CHAR(10));
97 --error ER_FK_INCOMPATIBLE_COLUMNS
98 ALTER TABLE child ADD FOREIGN KEY (fk) REFERENCES parent(pk);
99 DROP TABLE child;
100
101 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
102 --error ER_FK_INCOMPATIBLE_COLUMNS
103 ALTER TABLE child MODIFY fk CHAR(10);
104 --error ER_FK_INCOMPATIBLE_COLUMNS
105 ALTER TABLE parent MODIFY pk CHAR(10);
106 DROP TABLES child, parent;
107
108 --echo #
109 --echo # 2) Test how compatibility rules work for various types.
110 --echo # Different engines have different rules, so the below
111 --echo # part of test is specific to NDB.
112 --echo #
113 --echo # We don't check compatibility for BLOB based and BIT types as
114 --echo # they are not supported in foreign keys by NDB. Also we don't
115 --echo # provide coverage for legacy types.
116
117 --echo #
118 --echo # 2.a) For integer types both exact type and signedness should match.
119 CREATE TABLE parent (pk INT PRIMARY KEY);
120 --echo # Foreign keys over integer types are supported.
121 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
122 DROP TABLE child;
123 --error ER_FK_INCOMPATIBLE_COLUMNS
124 CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
125 --error ER_FK_INCOMPATIBLE_COLUMNS
126 CREATE TABLE child (fk BIGINT, FOREIGN KEY (fk) REFERENCES parent(pk));
127 --error ER_FK_INCOMPATIBLE_COLUMNS
128 CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
129 ALTER TABLE parent MODIFY pk INT UNSIGNED;
130 CREATE TABLE child (fk INT UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
131 --error ER_FK_INCOMPATIBLE_COLUMNS
132 ALTER TABLE child MODIFY fk INT;
133 DROP TABLE child, parent;
134 --echo # Integer types are not compatible with other types.
135 CREATE TABLE parent (pk INT PRIMARY KEY);
136 --error ER_FK_INCOMPATIBLE_COLUMNS
137 CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
138 --error ER_FK_INCOMPATIBLE_COLUMNS
139 CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
140 --error ER_FK_INCOMPATIBLE_COLUMNS
141 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
142 --error ER_FK_INCOMPATIBLE_COLUMNS
143 CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
144 --error ER_FK_INCOMPATIBLE_COLUMNS
145 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
146 --error ER_FK_INCOMPATIBLE_COLUMNS
147 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
148 --error ER_FK_INCOMPATIBLE_COLUMNS
149 CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
150 --error ER_FK_INCOMPATIBLE_COLUMNS
151 CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
152 DROP TABLE parent;
153
154 --echo #
155 --echo # 2.b) For floating point types only the exact type matters.
156 CREATE TABLE parent (pk DOUBLE PRIMARY KEY);
157 --echo # Though using such types in foreign key is EXTREMELY bad idea they
158 --echo # are supported.
159 CREATE TABLE child (fk DOUBLE, FOREIGN KEY (fk) REFERENCES parent(pk));
160 DROP TABLE child;
161 --error ER_FK_INCOMPATIBLE_COLUMNS
162 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
163 --echo # Signedness doesn't matter.
164 CREATE TABLE child (fk DOUBLE UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
165 DROP TABLES child, parent;
166 --echo # Floating point types are not compatible with other types.
167 CREATE TABLE parent (pk FLOAT PRIMARY KEY);
168 --error ER_FK_INCOMPATIBLE_COLUMNS
169 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
170 --error ER_FK_INCOMPATIBLE_COLUMNS
171 CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
172 --error ER_FK_INCOMPATIBLE_COLUMNS
173 CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
174 --error ER_FK_INCOMPATIBLE_COLUMNS
175 CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
176 --error ER_FK_INCOMPATIBLE_COLUMNS
177 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
178 --error ER_FK_INCOMPATIBLE_COLUMNS
179 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
180 --error ER_FK_INCOMPATIBLE_COLUMNS
181 CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
182 --error ER_FK_INCOMPATIBLE_COLUMNS
183 CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
184 DROP TABLE parent;
185
186 --echo #
187 --echo # 2.c) DECIMAL type is compatible with other DECIMAL columns with
188 --echo # the same signedness, precision and scale.
189 --echo #
190 CREATE TABLE parent (pk DECIMAL(8,2) PRIMARY KEY);
191 --echo # Foreign keys over DECIMAL columns are supported.
192 CREATE TABLE child (fk DECIMAL(8,2), FOREIGN KEY (fk) REFERENCES parent(pk));
193 DROP TABLE child;
194 --echo # Signedness, precision and scale do matter.
195 --error ER_FK_INCOMPATIBLE_COLUMNS
196 CREATE TABLE child (fk DECIMAL(8,2) UNSIGNED, FOREIGN KEY (fk) REFERENCES parent(pk));
197 --error ER_FK_INCOMPATIBLE_COLUMNS
198 CREATE TABLE child (fk DECIMAL(7,2), FOREIGN KEY (fk) REFERENCES parent(pk));
199 --error ER_FK_INCOMPATIBLE_COLUMNS
200 CREATE TABLE child (fk DECIMAL(8,3), FOREIGN KEY (fk) REFERENCES parent(pk));
201 --error ER_FK_INCOMPATIBLE_COLUMNS
202 CREATE TABLE child (fk DECIMAL(6,1), FOREIGN KEY (fk) REFERENCES parent(pk));
203 --echo # DECIMAL types are not compatible with other types.
204 --error ER_FK_INCOMPATIBLE_COLUMNS
205 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
206 --error ER_FK_INCOMPATIBLE_COLUMNS
207 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
208 --error ER_FK_INCOMPATIBLE_COLUMNS
209 CREATE TABLE child (fk BINARY(4), FOREIGN KEY (fk) REFERENCES parent(pk));
210 --error ER_FK_INCOMPATIBLE_COLUMNS
211 CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
212 --error ER_FK_INCOMPATIBLE_COLUMNS
213 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
214 --error ER_FK_INCOMPATIBLE_COLUMNS
215 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
216 --error ER_FK_INCOMPATIBLE_COLUMNS
217 CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
218 --error ER_FK_INCOMPATIBLE_COLUMNS
219 CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
220 DROP TABLE parent;
221
222 --echo #
223 --echo # 2.d) Unlike InnoDB, NDB considers string types compatible if
224 --echo # exact type, length and charset match.
225 CREATE TABLE parent (pk CHAR(10) PRIMARY KEY);
226 --echo # Foreign keys are supported for both CHAR and VARCHAR.
227 CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
228 DROP TABLES child, parent;
229 CREATE TABLE parent (pk VARCHAR(10) PRIMARY KEY);
230 CREATE TABLE child (fk VARCHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
231 DROP TABLE child;
232 --echo # Difference in size matters (even for VARCHAR!).
233 --error ER_FK_INCOMPATIBLE_COLUMNS
234 CREATE TABLE child (fk VARCHAR(11), FOREIGN KEY (fk) REFERENCES parent(pk));
235 --echo # Difference in exact type matters too.
236 --error ER_FK_INCOMPATIBLE_COLUMNS
237 CREATE TABLE child (fk CHAR(10), FOREIGN KEY (fk) REFERENCES parent(pk));
238 DROP TABLES parent;
239 --echo # Both columns must use the same collation.
240 CREATE TABLE parent (pk VARCHAR(10) CHARACTER SET utf8mb4 PRIMARY KEY);
241 --error ER_FK_INCOMPATIBLE_COLUMNS
242 CREATE TABLE child (fk VARCHAR(10) CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
243 --error ER_FK_INCOMPATIBLE_COLUMNS
244 CREATE TABLE child (fk VARCHAR(10) COLLATE utf8mb4_bin, FOREIGN KEY (fk) REFERENCES parent(pk));
245 --echo # Binary strings are not compatible with non-binary strings.
246 --error ER_FK_INCOMPATIBLE_COLUMNS
247 CREATE TABLE child (fk VARBINARY(40), FOREIGN KEY (fk) REFERENCES parent(pk));
248 DROP TABLE parent;
249 --echo # VARBINARY is not compatible with BINARY
250 CREATE TABLE parent (pk VARBINARY(10) PRIMARY KEY);
251 --error ER_FK_INCOMPATIBLE_COLUMNS
252 CREATE TABLE child (fk BINARY(10), FOREIGN KEY (fk) REFERENCES parent(pk));
253 DROP TABLES parent;
254 --echo # Non-binary string types are incompatible with other types.
255 CREATE TABLE parent (pk CHAR(4) CHARACTER SET latin1 PRIMARY KEY);
256 --error ER_FK_INCOMPATIBLE_COLUMNS
257 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
258 --error ER_FK_INCOMPATIBLE_COLUMNS
259 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
260 --error ER_FK_INCOMPATIBLE_COLUMNS
261 CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
262 --error ER_FK_INCOMPATIBLE_COLUMNS
263 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
264 --error ER_FK_INCOMPATIBLE_COLUMNS
265 CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
266 ALTER TABLE parent MODIFY pk CHAR(1) CHARACTER SET latin1;
267 --error ER_FK_INCOMPATIBLE_COLUMNS
268 CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
269 --error ER_FK_INCOMPATIBLE_COLUMNS
270 CREATE TABLE child (fk ENUM('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
271 --error ER_FK_INCOMPATIBLE_COLUMNS
272 CREATE TABLE child (fk SET('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
273 DROP TABLE parent;
274 --echo # Binary string types are incompatible with many other.
275 CREATE TABLE parent (pk BINARY(4) PRIMARY KEY);
276 --error ER_FK_INCOMPATIBLE_COLUMNS
277 CREATE TABLE child (fk INT, FOREIGN KEY (fk) REFERENCES parent(pk));
278 --error ER_FK_INCOMPATIBLE_COLUMNS
279 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
280 --error ER_FK_INCOMPATIBLE_COLUMNS
281 CREATE TABLE child (fk DECIMAL(8,0), FOREIGN KEY (fk) REFERENCES parent(pk));
282 --error ER_FK_INCOMPATIBLE_COLUMNS
283 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
284 --error ER_FK_INCOMPATIBLE_COLUMNS
285 CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
286 ALTER TABLE parent MODIFY pk CHAR(1) CHARACTER SET latin1;
287 --error ER_FK_INCOMPATIBLE_COLUMNS
288 CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
289 --error ER_FK_INCOMPATIBLE_COLUMNS
290 CREATE TABLE child (fk ENUM('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
291 --error ER_FK_INCOMPATIBLE_COLUMNS
292 CREATE TABLE child (fk SET('a') CHARACTER SET latin1, FOREIGN KEY (fk) REFERENCES parent(pk));
293 DROP TABLE parent;
294
295 --echo #
296 --echo # 2.e) YEAR and DATE are compatible with themselves and nothing else.
297 --echo #
298 CREATE TABLE parent (pk YEAR PRIMARY KEY);
299 CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
300 DROP TABLES child, parent;
301 CREATE TABLE parent (pk DATE PRIMARY KEY);
302 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
303 DROP TABLE child;
304 --echo # DATE and YEAR types are not compatible with other types.
305 --error ER_FK_INCOMPATIBLE_COLUMNS
306 CREATE TABLE child (fk MEDIUMINT, FOREIGN KEY (fk) REFERENCES parent(pk));
307 --error ER_FK_INCOMPATIBLE_COLUMNS
308 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
309 --error ER_FK_INCOMPATIBLE_COLUMNS
310 CREATE TABLE child (fk DECIMAL(6,2), FOREIGN KEY (fk) REFERENCES parent(pk));
311 --error ER_FK_INCOMPATIBLE_COLUMNS
312 CREATE TABLE child (fk BINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
313 --error ER_FK_INCOMPATIBLE_COLUMNS
314 CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
315 --error ER_FK_INCOMPATIBLE_COLUMNS
316 CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
317 --error ER_FK_INCOMPATIBLE_COLUMNS
318 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
319 --error ER_FK_INCOMPATIBLE_COLUMNS
320 CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
321 --error ER_FK_INCOMPATIBLE_COLUMNS
322 CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
323 --error ER_FK_INCOMPATIBLE_COLUMNS
324 CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
325 DROP TABLE parent;
326
327 --echo #
328 --echo # 2.f) Temporals with fractional part are compatible with
329 --echo # themselves, provided that scale stays the same.
330 --echo #
331 CREATE TABLE parent (pk TIMESTAMP(6) PRIMARY KEY);
332 CREATE TABLE child (fk TIMESTAMP(6), FOREIGN KEY (fk) REFERENCES parent(pk));
333 DROP TABLES child, parent;
334 CREATE TABLE parent (pk DATETIME PRIMARY KEY);
335 CREATE TABLE child (fk DATETIME, FOREIGN KEY (fk) REFERENCES parent(pk));
336 DROP TABLE child, parent;
337 CREATE TABLE parent (pk TIME(2) PRIMARY KEY);
338 CREATE TABLE child (fk TIME(2), FOREIGN KEY (fk) REFERENCES parent(pk));
339 DROP TABLE child;
340 --echo # Scale matters.
341 --error ER_FK_INCOMPATIBLE_COLUMNS
342 CREATE TABLE child (fk TIME(0), FOREIGN KEY (fk) REFERENCES parent(pk));
343 --error ER_FK_INCOMPATIBLE_COLUMNS
344 CREATE TABLE child (fk TIME(1), FOREIGN KEY (fk) REFERENCES parent(pk));
345 --echo # Temporals with fractional part are not compatible with other types.
346 --error ER_FK_INCOMPATIBLE_COLUMNS
347 CREATE TABLE child (fk MEDIUMINT, FOREIGN KEY (fk) REFERENCES parent(pk));
348 --error ER_FK_INCOMPATIBLE_COLUMNS
349 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
350 --error ER_FK_INCOMPATIBLE_COLUMNS
351 CREATE TABLE child (fk DECIMAL(6,2), FOREIGN KEY (fk) REFERENCES parent(pk));
352 --error ER_FK_INCOMPATIBLE_COLUMNS
353 CREATE TABLE child (fk BINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
354 --error ER_FK_INCOMPATIBLE_COLUMNS
355 CREATE TABLE child (fk VARBINARY(3), FOREIGN KEY (fk) REFERENCES parent(pk));
356 --error ER_FK_INCOMPATIBLE_COLUMNS
357 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
358 --error ER_FK_INCOMPATIBLE_COLUMNS
359 CREATE TABLE child (fk TIMESTAMP(2), FOREIGN KEY (fk) REFERENCES parent(pk));
360 --error ER_FK_INCOMPATIBLE_COLUMNS
361 CREATE TABLE child (fk ENUM('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
362 --error ER_FK_INCOMPATIBLE_COLUMNS
363 CREATE TABLE child (fk SET('a') CHARACTER SET binary, FOREIGN KEY (fk) REFERENCES parent(pk));
364 DROP TABLE parent;
365
366 --echo #
367 --echo # 2.g) Columns of ENUM type are compatible with themselves.
368 --echo #
369 --echo # They are also compatible with CHAR(1..2) and SET types, but
370 --echo # it is not clear if it is a bug or feature.
371 CREATE TABLE parent(pk ENUM('a') PRIMARY KEY);
372 --echo # Foreign key over ENUMs are supported, element names and count do
373 --echo # not matter provided that storage size is the same.
374 CREATE TABLE child (fk ENUM('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk));
375 DROP TABLE child;
376 --echo # Storage size should match.
377 --let $x100enum =`WITH RECURSIVE gen (num, el) AS (SELECT 1, CAST('\'a1\'' AS CHAR(2048)) UNION SELECT num+1, CONCAT(el,',\'a',num+1,'\'') FROM gen WHERE num <= 256) SELECT el FROM gen WHERE num = 256`
378 --error ER_FK_INCOMPATIBLE_COLUMNS
379 --eval CREATE TABLE child (fk ENUM($x100enum), FOREIGN KEY (fk) REFERENCES parent(pk))
380 --error ER_FK_INCOMPATIBLE_COLUMNS
381 CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
382 --error ER_FK_INCOMPATIBLE_COLUMNS
383 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
384 --error ER_FK_INCOMPATIBLE_COLUMNS
385 CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk));
386 --error ER_FK_INCOMPATIBLE_COLUMNS
387 CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
388 --error ER_FK_INCOMPATIBLE_COLUMNS
389 CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
390 --error ER_FK_INCOMPATIBLE_COLUMNS
391 CREATE TABLE child (fk YEAR, FOREIGN KEY (fk) REFERENCES parent(pk));
392 --error ER_FK_INCOMPATIBLE_COLUMNS
393 CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk));
394 --error ER_FK_INCOMPATIBLE_COLUMNS
395 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
396 DROP TABLE parent;
397
398 --echo #
399 --echo # 2.h) Columns of SET type are compatible if storage size is
400 --echo # the same.
401 --echo #
402 --echo # They are also compatible with CHAR(1..4) and ENUM types, but
403 --echo # it is not clear if it is a bug or feature.
404 CREATE TABLE parent(pk SET('a') PRIMARY KEY);
405 --echo # Foreign key over SETs are supported, element names and count do
406 --echo # not matter provided that storage size is the same.
407 CREATE TABLE child (fk SET('b','c'), FOREIGN KEY (fk) REFERENCES parent(pk));
408 DROP TABLE child;
409 --echo # Storage size should match.
410 --error ER_FK_INCOMPATIBLE_COLUMNS
411 CREATE TABLE child (fk SET('a1','a2','a3','a4','a5','a6','a7','a8','a9'), FOREIGN KEY (fk) REFERENCES parent(pk));
412 --error ER_FK_INCOMPATIBLE_COLUMNS
413 CREATE TABLE child (fk TINYINT, FOREIGN KEY (fk) REFERENCES parent(pk));
414 --error ER_FK_INCOMPATIBLE_COLUMNS
415 CREATE TABLE child (fk FLOAT, FOREIGN KEY (fk) REFERENCES parent(pk));
416 --error ER_FK_INCOMPATIBLE_COLUMNS
417 CREATE TABLE child (fk DECIMAL(2,0), FOREIGN KEY (fk) REFERENCES parent(pk));
418 --error ER_FK_INCOMPATIBLE_COLUMNS
419 CREATE TABLE child (fk BINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
420 --error ER_FK_INCOMPATIBLE_COLUMNS
421 CREATE TABLE child (fk VARBINARY(1), FOREIGN KEY (fk) REFERENCES parent(pk));
422 --error ER_FK_INCOMPATIBLE_COLUMNS
423 CREATE TABLE child (fk DATE, FOREIGN KEY (fk) REFERENCES parent(pk));
424 --error ER_FK_INCOMPATIBLE_COLUMNS
425 CREATE TABLE child (fk TIME, FOREIGN KEY (fk) REFERENCES parent(pk));
426 --error ER_FK_INCOMPATIBLE_COLUMNS
427 CREATE TABLE child (fk TIMESTAMP, FOREIGN KEY (fk) REFERENCES parent(pk));
428 DROP TABLE parent;
429
430 SET default_storage_engine = @save_storage_engine;
431
432
433 --echo #
434 --echo # Additional test for bug#29173134 "FOREIGN KEY CONSTRAINT NAMES TAKING
435 --echo # INDEX NAME".
436 --echo #
437 CREATE TABLE t1 (id INT PRIMARY KEY) ENGINE=NDB;
438 CREATE TABLE t2 (fk1 INT, fk2 INT, fk3 INT, fk4 INT,
439 FOREIGN KEY (fk1) REFERENCES t1 (id),
440 CONSTRAINT c FOREIGN KEY (fk2) REFERENCES t1 (id),
441 FOREIGN KEY d (fk3) REFERENCES t1 (id),
442 CONSTRAINT e FOREIGN KEY f (fk4) REFERENCES t1 (id)
443 ) ENGINE=NDB;
444 SHOW CREATE TABLE t2;
445 SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
446 WHERE TABLE_NAME = 't2' ORDER BY CONSTRAINT_NAME;
447 DROP TABLES t2, t1;
448
449
450 --echo #
451 --echo # Bug#30171959 "INCONSISTENT NAMING OF FOREIGN KEYS OVER NDB TABLES".
452 --echo #
453
454 --echo # Basic check of oreign key name generation by CREATE TABLE.
455 CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=NDB;
456 CREATE TABLE child (fk1 INT, fk2 INT, fk3 INT, fk4 INT, fk5 INT, fk6 INT,
457 fk7 INT, fk8 INT, fk9 INT, fk10 INT,
458 CONSTRAINT c1 FOREIGN KEY (fk1) REFERENCES parent (pk),
459 FOREIGN KEY (fk2) REFERENCES parent (pk),
460 FOREIGN KEY (fk3) REFERENCES parent (pk)) ENGINE=NDB;
461 --let $ndb_table_name=child
462 --source verify_foreign_keys.inc
463
464 --echo # Similar check for ALTER TABLE.
465 ALTER TABLE child ADD FOREIGN KEY (fk4) REFERENCES parent (pk),
466 ADD CONSTRAINT c2 FOREIGN KEY (fk5) REFERENCES parent (pk),
467 ADD FOREIGN KEY (fk6) REFERENCES parent (pk);
468 --let $ndb_table_name=child
469 --source verify_foreign_keys.inc
470
471 --echo # Adding foreign key with explicit name matching pattern of generated
472 --echo # names should be correctly handled by increasing generated name counter.
473 ALTER TABLE child ADD CONSTRAINT child_fk_10 FOREIGN KEY (fk7) REFERENCES parent (pk);
474 ALTER TABLE child ADD FOREIGN KEY (fk8) REFERENCES parent (pk);
475 --let $ndb_table_name=child
476 --source verify_foreign_keys.inc
477
478 --echo # Dropping foreign key with generated name doesn't cause name changes for
479 --echo # other foreign keys with generated names.
480 ALTER TABLE child DROP FOREIGN KEY child_fk_4;
481 --let $ndb_table_name=child
482 --source verify_foreign_keys.inc
483
484 --echo # Generation of new names is not affected by presence of holes
485 --echo # in the sequence of generated names.
486 ALTER TABLE child ADD FOREIGN KEY (fk9) REFERENCES parent (pk);
487 --let $ndb_table_name=child
488 --source verify_foreign_keys.inc
489
490 --echo # However, if foreign key with last generated name is dropped,
491 --echo # name can be reused later.
492 ALTER TABLE child DROP FOREIGN KEY child_fk_12;
493 ALTER TABLE child ADD FOREIGN KEY (fk10) REFERENCES parent (pk);
494 --let $ndb_table_name=child
495 --source verify_foreign_keys.inc
496
497 --echo # Renaming of table updates generated names to preserve the pattern.
498 RENAME TABLE child TO child1;
499 --let $ndb_table_name=child1
500 --source verify_foreign_keys.inc
501
502 --echo # Similar test for ALTER TABLE RENAME.
503 ALTER TABLE child1 RENAME TO child2;
504 --let $ndb_table_name=child2
505 --source verify_foreign_keys.inc
506
507 --echo # Currently changing SE of table with foreign key is not supported.
508 --echo # If it is ever supported we need to handle somehow difference in
509 --echo # suffixes for generated foreign key names.
510 --error ER_FK_CANNOT_CHANGE_ENGINE
511 ALTER TABLE child2 ENGINE=InnoDB;
512 SHOW CREATE TABLE child2;
513 SELECT constraint_name FROM information_schema.referential_constraints
514 WHERE constraint_schema='test' AND table_name='child2' ORDER BY constraint_name ASC;
515 DROP TABLES child2, parent;
516
517
518 --echo #
519 --echo # Additional coverage for bug#30267236 "REPLACE DICT_FOREIGN_PARSE...
520 --echo # WITH SQL-LAYER CHECK."
521 --echo #
522 CREATE TABLE parent (pk INT PRIMARY KEY) ENGINE=NDB;
523 CREATE TABLE child (fk1 INT, b INT, CONSTRAINT c FOREIGN KEY (fk1) REFERENCES parent (pk)) ENGINE=NDB;
524 CREATE TABLE unrelated (a INT) ENGINE=NDB;
525
526 --echo #
527 --echo # Dropping non-existing foreign key should fail with consistent error.
528 --error ER_CANT_DROP_FIELD_OR_KEY
529 ALTER TABLE child DROP FOREIGN KEY no_such_fk;
530 --error ER_CANT_DROP_FIELD_OR_KEY
531 ALTER TABLE child DROP FOREIGN KEY no_such_fk, ALGORITHM=INPLACE;
532 --error ER_CANT_DROP_FIELD_OR_KEY
533 ALTER TABLE child DROP FOREIGN KEY no_such_fk, ALGORITHM=COPY;
534
535 --echo #
536 --echo # Dropping existing foreign key using wrong table name should fail
537 --echo # with consistent error as well.
538 --error ER_CANT_DROP_FIELD_OR_KEY
539 ALTER TABLE unrelated DROP FOREIGN KEY c;
540 --error ER_CANT_DROP_FIELD_OR_KEY
541 ALTER TABLE unrelated DROP FOREIGN KEY c, ALGORITHM=INPLACE;
542 --error ER_CANT_DROP_FIELD_OR_KEY
543 ALTER TABLE unrelated DROP FOREIGN KEY c, ALGORITHM=COPY;
544
545 --echo #
546 --echo # The same should happen if one tries to drop foreign key using
547 --echo # parent table.
548 --error ER_CANT_DROP_FIELD_OR_KEY
549 ALTER TABLE parent DROP FOREIGN KEY c;
550 --error ER_CANT_DROP_FIELD_OR_KEY
551 ALTER TABLE parent DROP FOREIGN KEY c, ALGORITHM=INPLACE;
552 --error ER_CANT_DROP_FIELD_OR_KEY
553 ALTER TABLE parent DROP FOREIGN KEY c, ALGORITHM=COPY;
554 DROP TABLES unrelated, child, parent;