Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2
3 ###
4 ### insert PK vs PK
5 ###
6 create table t1 (
7 a int not null,
8 b int not null,
9 c int not null,
10 d int not null,
11 e int not null,
12 f int not null,
13 primary key (a),
14 unique(c) using hash,
15 index(e)) engine = ndb;
16
17 alter table t1 add constraint fkname
18 foreign key (a) references t1(a) on delete restrict on update restrict;
19
20 insert into t1 values (1,1,1,1,1,1);
21
22 alter table t1 drop foreign key fkname;
23 delete from t1;
24
25 ###
26 ### insert PK vs UK
27 ###
28 alter table t1 add constraint fkname
29 foreign key (c) references t1(a) on delete restrict on update restrict;
30
31 insert into t1 values (1,1,1,1,1,1);
32
33 --error 1452
34 insert into t1 values (2,2,3,3,2,2);
35
36 alter table t1 drop foreign key fkname;
37 delete from t1;
38
39 ###
40 ### insert PK vs OI
41 ###
42 alter table t1 add constraint fkname
43 foreign key (e) references t1(a) on delete restrict on update restrict;
44
45 insert into t1 values (1,1,1,1,1,1);
46
47 --error 1452
48 insert into t1 values (2,2,2,2,3,3);
49
50 alter table t1 drop foreign key fkname;
51 delete from t1;
52
53 ###
54 ### insert UK vs PK
55 ###
56 alter table t1 add constraint fkname
57 foreign key (a) references t1(c) on delete restrict on update restrict;
58
59 #
60 # Bug#27930382 Foreign key constraint violations, false positive
61 # (Manual tests 2018-06-13 indicates that innodb still has the same bug)
62 #
63 insert into t1 values (1,1,1,1,1,1);
64
65 --error 1452
66 insert into t1 values (2,2,3,3,2,2);
67
68 alter table t1 drop foreign key fkname;
69 delete from t1;
70
71 ###
72 ### insert UK vs UK
73 ###
74 alter table t1 add constraint fkname
75 foreign key (c) references t1(c) on delete restrict on update restrict;
76
77 #
78 # Bug#27930382 Foreign key constraint violations, false positive
79 # (Manual tests 2018-06-13 indicates that innodb still has the same bug)
80 #
81 insert into t1 values (1,1,1,1,1,1);
82
83 alter table t1 drop foreign key fkname;
84 delete from t1;
85
86 ###
87 ### insert UK vs OI
88 ###
89 alter table t1 add constraint fkname
90 foreign key (e) references t1(c) on delete restrict on update restrict;
91
92 #
93 # Bug#27930382 Foreign key constraint violations, false positive
94 # (Manual tests 2018-06-13 indicates that innodb still has the same bug)
95 #
96 insert into t1 values (1,1,1,1,1,1);
97
98 --error 1452
99 insert into t1 values (2,2,2,2,3,3);
100
101 alter table t1 drop foreign key fkname;
102 delete from t1;
103
104 drop table t1;
105
106 ###
107 ### insert PK vs PK
108 ###
109 create table t1 (
110 a int not null,
111 b int not null,
112 c int not null,
113 d int not null,
114 e int not null,
115 f int not null,
116 primary key (a,b),
117 unique(c,d) using hash,
118 index(e,f)) engine = ndb;
119
120 alter table t1 add constraint fkname
121 foreign key (a,b) references t1(a,b) on delete no action on update no action;
122
123 insert into t1 values (1,1,1,1,1,1);
124
125 alter table t1 drop foreign key fkname;
126 delete from t1;
127
128 ###
129 ### insert PK vs UK
130 ###
131 alter table t1 add constraint fkname
132 foreign key (c,d) references t1(a,b) on delete no action on update no action;
133
134 insert into t1 values (1,1,1,1,1,1);
135
136 --error 1452
137 insert into t1 values (2,2,3,3,2,2);
138
139 alter table t1 drop foreign key fkname;
140 delete from t1;
141
142 ###
143 ### insert PK vs OI
144 ###
145 alter table t1 add constraint fkname
146 foreign key (e,f) references t1(a,b) on delete no action on update no action;
147
148 insert into t1 values (1,1,1,1,1,1);
149
150 --error 1452
151 insert into t1 values (2,2,2,2,3,3);
152
153 alter table t1 drop foreign key fkname;
154 delete from t1;
155
156 ###
157 ### insert UK vs PK
158 ###
159 alter table t1 add constraint fkname
160 foreign key (a,b) references t1(c,d) on delete no action on update no action;
161
162 insert into t1 values (1,1,1,1,1,1);
163
164 --error 1452
165 insert into t1 values (2,2,3,3,2,2);
166
167 alter table t1 drop foreign key fkname;
168 delete from t1;
169
170 ###
171 ### insert UK vs UK
172 ###
173 alter table t1 add constraint fkname
174 foreign key (c,d) references t1(c,d) on delete no action on update no action;
175
176 insert into t1 values (1,1,1,1,1,1);
177
178 alter table t1 drop foreign key fkname;
179 delete from t1;
180
181 ###
182 ### insert UK vs OI
183 ###
184 alter table t1 add constraint fkname
185 foreign key (e,f) references t1(c,d) on delete no action on update no action;
186
187 insert into t1 values (1,1,1,1,1,1);
188
189 --error 1452
190 insert into t1 values (2,2,2,2,3,3);
191
192 alter table t1 drop foreign key fkname;
193 delete from t1;
194
195 drop table t1;
196
197 --echo #
198 --echo # Truncating table with self referencing Foreign key
199 --echo #
200 create table t2 (
201 pk2 int not null primary key,
202 c int,
203 foreign key (c) references t2(pk2)
204 ) engine=NDB;
205
206 --let $ndb_table_name=t2
207 --let $ndb_self_ref=1
208 --source print_foreign_keys.inc
209
210 truncate table t2;
211
212 --echo Verify all the foreign key are re-created after truncate
213 --let $ndb_table_name=t2
214 --let $ndb_self_ref=1
215 --source print_foreign_keys.inc
216
217 --disable_warnings
218 show create table t2;
219 --enable_warnings
220
221 drop table t2;