Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_create_drop.test

raw
1 -- source include/have_ndb.inc
2
3 ###
4 ### PK vs PK
5 ###
6 create table parent (
7 a int primary key,
8 b int not null,
9 c int not null,
10 unique(b) using hash,
11 index(c)) engine = ndb;
12
13 create table child (
14 a int primary key,
15 b int not null,
16 c int not null,
17 unique(b) using hash,
18 index(c)) engine = ndb;
19
20 # Parent pk
21 # child pk, uk, oi
22 alter table child add constraint fk1 foreign key (a) references parent(a);
23 alter table child add constraint fk2 foreign key (b) references parent(a);
24 alter table child add constraint fk3 foreign key (c) references parent(a);
25
26 --disable_warnings
27 show create table child;
28 --enable_warnings
29
30 # Parent uk
31 # child pk, uk, oi
32 alter table child add constraint fk4 foreign key (a) references parent(b);
33 alter table child add constraint fk5 foreign key (b) references parent(b);
34 alter table child add constraint fk6 foreign key (c) references parent(b);
35
36 --disable_warnings
37 show create table child;
38 --enable_warnings
39
40 # Parent oi
41 # child pk, uk, oi
42 # not supported
43
44 --error ER_FK_NO_INDEX_PARENT
45 alter table child algorithm=copy, add constraint fk7 foreign key (a) references parent(c);
46 --error ER_FK_NO_INDEX_PARENT
47 alter table child algorithm=copy, add constraint fk8 foreign key (a) references parent(c);
48 --error ER_FK_NO_INDEX_PARENT
49 alter table child algorithm=copy, add constraint fk9 foreign key (a) references parent(c);
50
51 --error ER_FK_NO_INDEX_PARENT
52 alter table child algorithm=inplace, add constraint fk7 foreign key (a) references parent(c);
53 --error ER_FK_NO_INDEX_PARENT
54 alter table child algorithm=inplace, add constraint fk8 foreign key (a) references parent(c);
55 --error ER_FK_NO_INDEX_PARENT
56 alter table child algorithm=inplace, add constraint fk9 foreign key (a) references parent(c);
57
58 drop table child, parent;
59
60 #
61 # Test using SQL
62 #
63 create table parent (
64 a int primary key,
65 b int not null,
66 c int not null,
67 unique(b) using hash,
68 index(c)) engine = ndb;
69
70 create table child (
71 a int not null,
72 b int not null,
73 c int not null,
74 primary key (a),
75 unique key (b) using hash,
76 key (c),
77 constraint fk1 foreign key(a) references parent (a),
78 constraint fk2 foreign key(b) references parent (a),
79 constraint fk3 foreign key(c) references parent (a),
80 constraint fk4 foreign key(a) references parent (b),
81 constraint fk5 foreign key(b) references parent (b),
82 constraint fk6 foreign key(c) references parent (b)
83 ) engine=ndbcluster;
84
85 --disable_warnings
86 show create table child;
87 --enable_warnings
88 drop table child, parent;
89
90 --echo #
91 --echo # Bug#24666177 : PARENT TABLE'S HANDLER NOT UPDATED AFTER ADDING A FK DEPENDENCY
92 --echo #
93 # Note : This bug and its fix became obsolete after the introduction of MySQL DD.
94 # The fix was removed as a part of fix for Bug#31356801 and the test now exists to
95 # verify that parent tables are updated properly in the DD after an FK create/drop.
96
97 --echo #Setup tables
98 create table parent(
99 id int unique key
100 ) engine ndb;
101
102 create table child(
103 ref int
104 ) engine ndb;
105
106 insert into parent values(1), (2), (3);
107 insert into child values(1), (2), (3);
108
109 --echo #Show create table on parent.
110 show create table parent;
111
112 --echo #Now create a foreign key dependency
113 alter table child add constraint fk1 foreign key (ref) references parent(id);
114
115 --echo #Now run a alter engine query.
116 --error ER_FK_CANNOT_CHANGE_ENGINE
117 alter table parent engine = innodb;
118
119 show create table parent;
120
121 --echo #Repeat same with truncate
122 truncate table child;
123 --error ER_FK_CANNOT_CHANGE_ENGINE
124 alter table parent engine = innodb;
125
126 show create table parent;
127
128 --echo #Repeat same test for dropping FK
129 alter table child drop foreign key fk1;
130
131 --echo # Now this should succeed
132 alter table parent engine = innodb;
133
134 show create table parent;
135
136 --echo #Cleanup
137 drop table parent, child;
138
139 --echo #
140 --echo # Bug#25859977 : WRONG ERROR MESSAGE SHOWN AFTER ALTER ADD FK FAILURE
141 --echo #
142
143 --echo # Setup
144 create table parent (
145 a int unique,
146 b int,
147 c int,
148 key b_key(b),
149 key c_key(c),
150 constraint fk_p1 foreign key (b) references parent(a)
151 ) engine ndb;
152
153 create table child (
154 a int,
155 b int,
156 key a_key(a),
157 key b_key(b),
158 constraint fk_c1 foreign key (a) references parent(a)
159 ) engine ndb;
160
161 --echo # Try creating fk with already exisiting names. Should fail.
162 --error ER_FK_DUP_NAME
163 alter table parent add constraint fk_p1 foreign key (c) references parent(a);
164 show warnings;
165
166 --error ER_FK_DUP_NAME
167 alter table child add constraint fk_c1 foreign key (b) references parent(a);
168 show warnings;
169
170 --echo # Check if error is thrown when creating table
171 --error ER_FK_DUP_NAME
172 create table test_fail (
173 a int,
174 b int,
175 key a_key(a),
176 key b_key(b),
177 constraint fk_t1 foreign key (a) references parent(a),
178 constraint fk_t1 foreign key (b) references parent(a)
179 ) engine ndb;
180 show warnings;
181
182 --echo # Using distinct names should work
183 alter table parent add constraint fk_p2 foreign key (b) references parent(a);
184 alter table parent add constraint fk_p3 foreign key (c) references parent(a);
185 alter table child add constraint fk_c2 foreign key (b) references parent(a);
186
187 --echo #Show create tables
188 show create table parent;
189 show create table child;
190
191 --echo # Cleanup
192 drop table child, parent;
193
194 --echo #
195 --echo # Bug#25882950 : CACHED INDEX IN NDBAPI'S DICT CACHE NOT CLEARED AFTER `CREATE TABLE` FAILURE
196 --echo #
197
198 --echo # Create a table with bad fk reference
199 --error ER_FK_CANNOT_OPEN_PARENT
200 create table test(
201 a int,
202 foreign key fk1(a) references bogus_table(a)
203 ) engine ndb;
204
205 --echo # Now create a table with proper reference - it should pass
206 create table test (
207 a int unique,
208 b int,
209 foreign key fk1(b) references test(a)
210 ) engine ndb;
211
212 --echo #Cleanup
213 drop table test;
214
215 --echo #
216 --echo # Bug#38534367 : Dropping foreign key (with drop constraint) causes assertion failure (bad dereference)
217 --echo #
218
219 create table parent (
220 a int primary key,
221 b int
222 ) engine ndb;
223
224 create table child (
225 a int primary key,
226 b int,
227 c int
228 ) engine = ndb;
229
230 alter table child add foreign key(c) references parent(a);
231 alter table child drop constraint child_fk_1;
232
233 drop table parent, child;