Debian Codegraph @

mysql-test/suite/ndb/t/ndb_fk_no_fk_compat.inc

raw
1 #
2 # Test legacy compatibility of no fk foreign key support
3 # - snippets of other tests converted to use different engines
4 # for quick and easy compatibility test
5 #
6
7 if (!$engine)
8 {
9 die Missing parameter $engine;
10 }
11
12 #
13 # Test 1
14 #
15
16 SET foreign_key_checks=0;
17 --replace_result $engine engine
18 eval CREATE TABLE t1 (id int primary key, foreign key (id) references t2(id)) ENGINE=$engine;
19 --replace_result $engine engine
20 eval CREATE TABLE t2 (id int primary key, foreign key (id) references t1(id)) ENGINE=$engine;
21 SET foreign_key_checks=1;
22 SELECT COUNT(*) FROM information_schema.key_column_usage
23 WHERE REFERENCED_TABLE_NAME in ('t1', 't2');
24 SET foreign_key_checks=0;
25 #
26 # Bug#16817928
27 # Check dropping in both order
28 DROP TABLE t1, t2;
29
30 SET foreign_key_checks=0;
31 --replace_result $engine engine
32 eval CREATE TABLE t1 (id int primary key, foreign key (id) references t2(id)) ENGINE=$engine;
33 --replace_result $engine engine
34 eval CREATE TABLE t2 (id int primary key, foreign key (id) references t1(id)) ENGINE=$engine;
35 SET foreign_key_checks=1;
36 SELECT COUNT(*) FROM information_schema.key_column_usage
37 WHERE REFERENCED_TABLE_NAME in ('t1', 't2');
38 SET foreign_key_checks=0;
39 #
40 # Bug#16817928
41 # Check dropping in both order
42 DROP TABLE t2, t1;
43
44 SET foreign_key_checks=1;
45
46 #
47 # Test foreign key referencing table in db which does not exist
48 #
49 SET foreign_key_checks=0;
50 --replace_result $engine engine
51 eval CREATE TABLE t1 (id int primary key, foreign key (id) references no_such_db.t2(id)) ENGINE=$engine;
52 drop table t1;
53 SET foreign_key_checks=1;
54
55 #
56 # Test adding multiple fks with same name
57 #
58 --replace_result $engine engine
59 eval create table t1(pk1 int primary key auto_increment,
60 a int,
61 b int
62 ) engine=$engine;
63 --replace_result $engine engine
64 eval create table t2(pk2 int primary key auto_increment,
65 c int not null,
66 unique(c),
67 d int not null,
68 unique(d)
69 ) engine=$engine;
70 --error ER_DUP_KEYNAME
71 alter table t1
72 add constraint fk3 foreign key (a) references t2(c),
73 add constraint fk3 foreign key (b) references t2(d);
74 # Try same alter without foreign_key_checks
75 SET foreign_key_checks=0;
76 --error ER_DUP_KEYNAME
77 alter table t1
78 add constraint fk3 foreign key (a) references t2(c),
79 add constraint fk3 foreign key (b) references t2(d);
80 SET foreign_key_checks=1;
81 drop table t1, t2;
82
83
84 #
85 # Test TRUNCATE of table referenced by fk
86 #
87 --replace_result $engine engine
88 eval create table t2 (
89 pk2 int not null primary key,
90 c int
91 ) engine=$engine;
92 --replace_result $engine engine
93 eval create table t1 (
94 pk1 int not null primary key,
95 b int,
96 foreign key (b) references t2(pk2)
97 ) engine=$engine;
98
99 set foreign_key_checks=0;
100 truncate table t2;
101
102 set foreign_key_checks=1;
103 --echo Check if foreign keys are intact after truncate
104 insert into t2 values (1,10),(2,20),(3,30);
105 insert into t1 values (100,1),(200,2),(300,3);
106
107 --error ER_NO_REFERENCED_ROW_2
108 insert into t1 values (400,4);
109
110 --replace_regex /\(.*\)/(<fk_def>)/
111 --error ER_TRUNCATE_ILLEGAL_FK
112 truncate table t2;
113 set foreign_key_checks=0;
114
115 drop table t1, t2;
116
117 #
118 # Test TRUNCATE of referencing table in fk
119 #
120 --replace_result $engine engine
121 eval create table t2 (
122 pk2 int not null primary key,
123 c int
124 ) engine=$engine;
125 --replace_result $engine engine
126 eval create table t1 (
127 pk1 int not null primary key,
128 b int,
129 foreign key (b) references t2(pk2)
130 ) engine=$engine;
131
132 truncate table t1;
133
134 set foreign_key_checks=1;
135 truncate table t1;
136 set foreign_key_checks=0;
137
138 drop table t1, t2;
139
140 #
141 # Test fk referencing columns in reverse order to existing index
142 # - should return an error saying "missing index for constraint .."
143 #
144 # NOTE! column order does not matter if parent index is unique
145 #
146 --replace_result $engine engine
147 eval create table t1 (
148 a1 int primary key,
149 b1 int not null,
150 c1 int not null,
151 key (b1,c1)
152 ) engine $engine;
153
154 --replace_result $engine engine
155 eval create table t2 (
156 a2 int primary key auto_increment,
157 b2 int not null,
158 c2 int not null
159 ) engine $engine;
160
161 --error ER_FK_NO_INDEX_PARENT
162 alter table t2
163 add constraint fk1 foreign key (b2,c2) references t1 (c1,b1);
164
165 insert into t1 (a1,b1,c1) values
166 (1,11,12),(2,21,22),(3,31,32);
167
168 insert into t2 (b2,c2)
169 select c1, b1 from t1;
170
171 drop table t2,t1;
172
173 #
174 # Test n
175 #
176
177