Debian Codegraph @

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

raw
1 -- source include/have_ndb.inc
2
3 create table parent (
4 a int primary key,
5 b int not null,
6 c int not null,
7 unique(b) using hash,
8 index(c)) engine = ndb;
9
10 create table child (
11 a int primary key,
12 b int not null,
13 c int not null,
14 unique(b) using hash,
15 index(c)) engine = ndb;
16
17 ###
18 # Test that you can't create a FK that violates constraint to start off
19 #
20 # NOTE: these tests use ndb_tools to create/drop FK
21 # as doing it by alter table, would currently mean an offline alter
22 # which doesnt test the same code path
23 #
24 insert into parent values (1,2,3);
25 insert into child values (3,4,5);
26
27 --let ukname=b\$unique
28 --let oiname=c
29
30 --error ER_CANNOT_ADD_FOREIGN
31 alter table child algorithm=inplace, add constraint fkname foreign key (a) references parent(a);
32 show warnings;
33
34 --error ER_CANNOT_ADD_FOREIGN
35 alter table child algorithm=inplace, add constraint fkname foreign key(b) references parent(a);
36 show warnings;
37 --error ER_CANNOT_ADD_FOREIGN
38 alter table child algorithm=inplace, add constraint fkname foreign key(c) references parent(a);
39 show warnings;
40
41 --error ER_CANNOT_ADD_FOREIGN
42 alter table child algorithm=inplace, add constraint fkname foreign key(a) references parent(b);
43 show warnings;
44 --error ER_CANNOT_ADD_FOREIGN
45 alter table child algorithm=inplace, add constraint fkname foreign key(b) references parent(b);
46 show warnings;
47 --error ER_CANNOT_ADD_FOREIGN
48 alter table child algorithm=inplace, add constraint fkname foreign key(c) references parent(b);
49 show warnings;
50
51 #
52 # Now it should work...
53 #
54 insert into parent values (2,3,4);
55 insert into parent values (3,4,5);
56 insert into parent values (4,5,6);
57 insert into parent values (5,6,7);
58
59 alter table child algorithm=inplace, add constraint fk1 foreign key(a) references parent(a);
60 alter table child algorithm=inplace, add constraint fk2 foreign key(b) references parent(a);
61 alter table child algorithm=inplace, add constraint fk3 foreign key(c) references parent(a);
62 alter table child algorithm=inplace, add constraint fk4 foreign key(a) references parent(b);
63 alter table child algorithm=inplace, add constraint fk5 foreign key(b) references parent(b);
64 alter table child algorithm=inplace, add constraint fk6 foreign key(c) references parent(b);
65
66 alter table child algorithm=inplace,
67 drop foreign key fk1,
68 drop foreign key fk2,
69 drop foreign key fk3,
70 drop foreign key fk4,
71 drop foreign key fk5,
72 drop foreign key fk6;
73
74 drop table parent, child;
75
76 # Check multi column foreign keys
77
78 eval create table parent (
79 b1 int as (b) stored not null,
80 c1 int as (c) stored not null,
81 a1 int as (a) stored not null,
82 b2 int as (b) stored not null,
83 c2 int as (c) stored not null,
84 a2 int as (a) stored not null,
85 a int default 0,
86 b int default 0,
87 c int default 0,
88 primary key pk1 (a1,b1,c1) using hash,
89 unique key uk2 (a2,b2,c2) using hash
90 ) engine=ndbcluster;
91
92 eval create table child (
93 a int default 0,
94 b int default 0,
95 c int default 0,
96 d int default 0,
97 e int default 0,
98 f int default 0,
99 b1 int as (b) stored not null,
100 c1 int as (c) stored not null,
101 a1 int as (a) stored not null,
102 b2 int as (b) stored not null,
103 c2 int as (c) stored not null,
104 a2 int as (a) stored not null,
105 b3 int as (b) stored not null,
106 c3 int as (c) stored not null,
107 a3 int as (a) stored not null,
108 primary key pk1 (a1,b1,c1) using hash,
109 unique key uk2 (a2,b2,c2) using hash,
110 key ok3 (a3,b3,c3),
111 key ok3def (c3,b3,a3,d,e,f)
112 ) engine=ndbcluster;
113
114 # Add parent row, use different values for the different columns.
115 insert into parent (a, b, c) values (11, 12, 13);
116 # Add valid child row.
117 insert into child (a, b, c) values (11, 12, 13);
118
119 alter table child add
120 constraint fk11 foreign key (c1,b1,a1) references parent (c1,b1,a1);
121 alter table child add
122 constraint fk21 foreign key (c2,b2,a2) references parent (c1,b1,a1);
123 alter table child add
124 constraint fk31 foreign key (c3,b3,a3) references parent (c1,b1,a1);
125 alter table child add
126 constraint fk12 foreign key (c1,b1,a1) references parent (c2,b2,a2);
127 alter table child add
128 constraint fk22 foreign key (c2,b2,a2) references parent (c2,b2,a2);
129 alter table child add
130 constraint fk32 foreign key (c3,b3,a3) references parent (c2,b2,a2);
131
132 drop table child, parent;