Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2
3 connect (con1,localhost,root,,test);
4 connect (con2,localhost,root,,test);
5
6 ###
7 ### PK vs PK
8 ###
9 create table parent (
10 a int not null,
11 b int not null,
12 c int not null,
13 primary key (a,b),
14 unique(b,c) using hash,
15 index(c,a)) engine = ndb;
16
17 create table child (
18 a int not null,
19 b int not null,
20 c int not null,
21 primary key (b,a),
22 unique(c,b) using hash,
23 index(c,a)) engine = ndb;
24
25 insert into parent values (1,2,3);
26
27 alter table child add constraint fkname foreign key (c,a) references parent(a,b) on delete restrict on update restrict;
28
29 --error ER_NO_REFERENCED_ROW_2
30 insert into child values (2,1,2);
31 --error ER_NO_REFERENCED_ROW_2
32 insert into child values (1,1,1);
33
34 insert into child values (2,1,1);
35 --error ER_ROW_IS_REFERENCED_2
36 delete from parent;
37
38 delete from child;
39 alter table child drop foreign key fkname;
40
41 alter table child add constraint fkname foreign key (b,a) references parent(a,b) on delete restrict on update restrict;
42
43 --error ER_NO_REFERENCED_ROW_2
44 insert into child values (1,2,2);
45 --error ER_NO_REFERENCED_ROW_2
46 insert into child values (2,3,1);
47
48 insert into child values (2,1,1);
49 --error ER_ROW_IS_REFERENCED_2
50 delete from parent;
51
52 delete from child;
53 alter table child drop foreign key fkname;
54
55 alter table child add constraint fkname foreign key (c,b) references parent(a,b) on delete restrict on update restrict;
56
57 --error ER_NO_REFERENCED_ROW_2
58 insert into child values (1,2,2);
59 --error ER_NO_REFERENCED_ROW_2
60 insert into child values (2,3,1);
61
62 insert into child values (2,2,1);
63 --error ER_ROW_IS_REFERENCED_2
64 delete from parent;
65
66 drop table child, parent;
67
68 --echo #
69 --echo # Bug#21664899 : FAILURE CREATING COMPOSITE FKS REFERENCING COMPOSITE PKS OF PARENT
70 --echo #
71
72 --echo #create table t1
73 create table t1 (
74 a int not null,
75 b int not null,
76 c int not null,
77 primary key pk1(a,b)
78 ) engine=ndbcluster;
79
80 --echo #create t2 with a unique key on single column
81 create table t2 (
82 a int not null,
83 b int not null,
84 c int not null,
85 unique key uniquet2a(a),
86 constraint fkt2ab foreign key (a,b) references t1(a,b)
87 ) engine=ndbcluster;
88
89 --echo #verify the presence of uniquet2a, fkt2ab & and an extra key for fkt2ab
90 --disable_warnings
91 show create table t2;
92 --enable_warnings
93 alter table t2 drop key uniquet2a;
94
95 --echo #alter add a unique constraint on single key
96 alter table t2 add constraint uniquet2a unique (a);
97 --echo #verify the presence of uniquet2a, fkt2ab & and an extra key for fkt2ab
98 --disable_warnings
99 show create table t2;
100 --enable_warnings
101
102 --echo #alter add a unique constraint on all cols in fk
103 alter table t2 add constraint uniquet2ab unique (a,b);
104 --echo #verify the presence of uniquet2a, uniquet2ab, fkt2ab. no extra key for fkt2ab
105 --disable_warnings
106 show create table t2;
107 --enable_warnings
108
109 --echo #try adding fk(a,b,c) referring t1(a,b,c), it should fail without parent index.
110 --error ER_FK_NO_INDEX_PARENT
111 alter table t2 add constraint fkt2abc foreign key (a,b,c) references t1(a,b,c);
112
113 --echo #now add a unique key but on a different column order and try again
114 alter table t1 add constraint uniquet1abc unique (c,a,b);
115 alter table t2 add constraint fkt2abc foreign key (a,b,c) references t1(a,b,c);
116 --echo #verify the presence of new fk
117 --disable_warnings
118 show create table t2;
119 --enable_warnings
120
121 --echo #cleanup
122 drop table t2, t1;