| blob | 5aa8b1d4b7aa7ecfa1023be9e317ea0ab63e250c |
1 # 2012 December 17
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
9 #
10 #***********************************************************************
11 # This file implements regression tests for SQLite library.
12 #
13 # This file tests the PRAGMA foreign_key_check command.
14 #
15 # EVIDENCE-OF: R-05426-18119 PRAGMA foreign_key_check; PRAGMA
16 # foreign_key_check(table-name);
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
20 set testprefix fkey5
22 ifcapable {!foreignkey} {
23 finish_test
24 return
25 }
27 do_test fkey5-1.1 {
28 db eval {
29 CREATE TABLE p1(a INTEGER PRIMARY KEY); INSERT INTO p1 VALUES(88),(89);
30 CREATE TABLE p2(a INT PRIMARY KEY); INSERT INTO p2 VALUES(77),(78);
31 CREATE TABLE p3(a TEXT PRIMARY KEY);
32 INSERT INTO p3 VALUES(66),(67),('alpha'),('BRAVO');
33 CREATE TABLE p4(a TEXT PRIMARY KEY COLLATE nocase);
34 INSERT INTO p4 VALUES('alpha'),('BRAVO'),('55'),('Delta'),('ECHO');
35 CREATE TABLE p5(a INTEGER PRIMARY KEY, b, c, UNIQUE(b,c));
36 INSERT INTO p5 VALUES(1,'Alpha','abc'),(2,'beta','def');
37 CREATE TABLE p6(a INTEGER PRIMARY KEY, b TEXT COLLATE nocase,
38 c TEXT COLLATE rtrim, UNIQUE(b,c));
39 INSERT INTO p6 VALUES(1,'Alpha','abc '),(2,'bETA','def ');
41 CREATE TABLE c1(x INTEGER PRIMARY KEY references p1);
42 CREATE TABLE c2(x INTEGER PRIMARY KEY references p2);
43 CREATE TABLE c3(x INTEGER PRIMARY KEY references p3);
44 CREATE TABLE c4(x INTEGER PRIMARY KEY references p4);
45 CREATE TABLE c5(x INT references p1);
46 CREATE TABLE c6(x INT references p2);
47 CREATE TABLE c7(x INT references p3);
48 CREATE TABLE c8(x INT references p4);
49 CREATE TABLE c9(x TEXT UNIQUE references p1);
50 CREATE TABLE c10(x TEXT UNIQUE references p2);
51 CREATE TABLE c11(x TEXT UNIQUE references p3);
52 CREATE TABLE c12(x TEXT UNIQUE references p4);
53 CREATE TABLE c13(x TEXT COLLATE nocase references p3);
54 CREATE TABLE c14(x TEXT COLLATE nocase references p4);
55 CREATE TABLE c15(x, y, FOREIGN KEY(x,y) REFERENCES p5(b,c));
56 CREATE TABLE c16(x, y, FOREIGN KEY(x,y) REFERENCES p5(c,b));
57 CREATE TABLE c17(x, y, FOREIGN KEY(x,y) REFERENCES p6(b,c));
58 CREATE TABLE c18(x, y, FOREIGN KEY(x,y) REFERENCES p6(c,b));
59 CREATE TABLE c19(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
60 FOREIGN KEY(x,y) REFERENCES p5(b,c));
61 CREATE TABLE c20(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
62 FOREIGN KEY(x,y) REFERENCES p5(c,b));
63 CREATE TABLE c21(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
64 FOREIGN KEY(x,y) REFERENCES p6(b,c));
65 CREATE TABLE c22(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
66 FOREIGN KEY(x,y) REFERENCES p6(c,b));
68 PRAGMA foreign_key_check;
69 }
70 } {}
71 do_test fkey5-1.2 {
72 db eval {
73 INSERT INTO c1 VALUES(90),(87),(88);
74 PRAGMA foreign_key_check;
75 }
76 } {c1 87 p1 0 c1 90 p1 0}
77 do_test fkey5-1.3 {
78 db eval {
79 PRAGMA foreign_key_check(c1);
80 }
81 } {c1 87 p1 0 c1 90 p1 0}
82 do_test fkey5-1.4 {
83 db eval {
84 PRAGMA foreign_key_check(c2);
85 }
86 } {}
88 # EVIDENCE-OF: R-45728-08709 There are four columns in each result row.
89 #
90 # EVIDENCE-OF: R-55672-01620 The first column is the name of the table
91 # that contains the REFERENCES clause.
92 #
93 # EVIDENCE-OF: R-25219-25618 The second column is the rowid of the row
94 # that contains the invalid REFERENCES clause.
95 #
96 # EVIDENCE-OF: R-40482-20265 The third column is the name of the table
97 # that is referred to.
98 #
99 # EVIDENCE-OF: R-62839-07969 The fourth column is the index of the
100 # specific foreign key constraint that failed.
101 #
102 do_test fkey5-2.0 {
103 db eval {
104 INSERT INTO c5 SELECT x FROM c1;
105 DELETE FROM c1;
106 PRAGMA foreign_key_check;
107 }
108 } {c5 1 p1 0 c5 3 p1 0}
109 do_test fkey5-2.1 {
110 db eval {
111 PRAGMA foreign_key_check(c5);
112 }
113 } {c5 1 p1 0 c5 3 p1 0}
114 do_test fkey5-2.2 {
115 db eval {
116 PRAGMA foreign_key_check(c1);
117 }
118 } {}
119 do_execsql_test fkey5-2.3 {
120 PRAGMA foreign_key_list(c5);
121 } {0 0 p1 x {} {NO ACTION} {NO ACTION} NONE}
123 do_test fkey5-3.0 {
124 db eval {
125 INSERT INTO c9 SELECT x FROM c5;
126 DELETE FROM c5;
127 PRAGMA foreign_key_check;
128 }
129 } {c9 1 p1 0 c9 3 p1 0}
130 do_test fkey5-3.1 {
131 db eval {
132 PRAGMA foreign_key_check(c9);
133 }
134 } {c9 1 p1 0 c9 3 p1 0}
135 do_test fkey5-3.2 {
136 db eval {
137 PRAGMA foreign_key_check(c5);
138 }
139 } {}
141 do_test fkey5-4.0 {
142 db eval {
143 DELETE FROM c9;
144 INSERT INTO c2 VALUES(79),(77),(76);
145 PRAGMA foreign_key_check;
146 }
147 } {c2 76 p2 0 c2 79 p2 0}
148 do_test fkey5-4.1 {
149 db eval {
150 PRAGMA foreign_key_check(c2);
151 }
152 } {c2 76 p2 0 c2 79 p2 0}
153 do_test fkey5-4.2 {
154 db eval {
155 INSERT INTO c6 SELECT x FROM c2;
156 DELETE FROM c2;
157 PRAGMA foreign_key_check;
158 }
159 } {c6 1 p2 0 c6 3 p2 0}
160 do_test fkey5-4.3 {
161 db eval {
162 PRAGMA foreign_key_check(c6);
163 }
164 } {c6 1 p2 0 c6 3 p2 0}
165 do_test fkey5-4.4 {
166 db eval {
167 INSERT INTO c10 SELECT x FROM c6;
168 DELETE FROM c6;
169 PRAGMA foreign_key_check;
170 }
171 } {c10 1 p2 0 c10 3 p2 0}
172 do_test fkey5-4.5 {
173 db eval {
174 PRAGMA foreign_key_check(c10);
175 }
176 } {c10 1 p2 0 c10 3 p2 0}
178 do_test fkey5-5.0 {
179 db eval {
180 DELETE FROM c10;
181 INSERT INTO c3 VALUES(68),(67),(65);
182 PRAGMA foreign_key_check;
183 }
184 } {c3 65 p3 0 c3 68 p3 0}
185 do_test fkey5-5.1 {
186 db eval {
187 PRAGMA foreign_key_check(c3);
188 }
189 } {c3 65 p3 0 c3 68 p3 0}
190 do_test fkey5-5.2 {
191 db eval {
192 INSERT INTO c7 SELECT x FROM c3;
193 INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot');
194 DELETE FROM c3;
195 PRAGMA foreign_key_check;
196 }
197 } {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
198 do_test fkey5-5.3 {
199 db eval {
200 PRAGMA foreign_key_check(c7);
201 }
202 } {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
203 do_test fkey5-5.4 {
204 db eval {
205 INSERT INTO c11 SELECT x FROM c7;
206 DELETE FROM c7;
207 PRAGMA foreign_key_check;
208 }
209 } {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
210 do_test fkey5-5.5 {
211 db eval {
212 PRAGMA foreign_key_check(c11);
213 }
214 } {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
216 do_test fkey5-6.0 {
217 db eval {
218 DELETE FROM c11;
219 INSERT INTO c4 VALUES(54),(55),(56);
220 PRAGMA foreign_key_check;
221 }
222 } {c4 54 p4 0 c4 56 p4 0}
223 do_test fkey5-6.1 {
224 db eval {
225 PRAGMA foreign_key_check(c4);
226 }
227 } {c4 54 p4 0 c4 56 p4 0}
228 do_test fkey5-6.2 {
229 db eval {
230 INSERT INTO c8 SELECT x FROM c4;
231 INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot');
232 DELETE FROM c4;
233 PRAGMA foreign_key_check;
234 }
235 } {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
236 do_test fkey5-6.3 {
237 db eval {
238 PRAGMA foreign_key_check(c8);
239 }
240 } {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
241 do_test fkey5-6.4 {
242 db eval {
243 INSERT INTO c12 SELECT x FROM c8;
244 DELETE FROM c8;
245 PRAGMA foreign_key_check;
246 }
247 } {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
248 do_test fkey5-6.5 {
249 db eval {
250 PRAGMA foreign_key_check(c12);
251 }
252 } {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
254 do_test fkey5-7.1 {
255 db eval {
256 INSERT OR IGNORE INTO c13 SELECT * FROM c12;
257 INSERT OR IGNORE INTO C14 SELECT * FROM c12;
258 DELETE FROM c12;
259 PRAGMA foreign_key_check;
260 }
261 } {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0 c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}
262 do_test fkey5-7.2 {
263 db eval {
264 PRAGMA foreign_key_check(c14);
265 }
266 } {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0}
267 do_test fkey5-7.3 {
268 db eval {
269 PRAGMA foreign_key_check(c13);
270 }
271 } {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}
273 do_test fkey5-8.0 {
274 db eval {
275 DELETE FROM c13;
276 DELETE FROM c14;
277 INSERT INTO c19 VALUES('alpha','abc');
278 PRAGMA foreign_key_check(c19);
279 }
280 } {c19 1 p5 0}
281 do_test fkey5-8.1 {
282 db eval {
283 DELETE FROM c19;
284 INSERT INTO c19 VALUES('Alpha','abc');
285 PRAGMA foreign_key_check(c19);
286 }
287 } {}
288 do_test fkey5-8.2 {
289 db eval {
290 INSERT INTO c20 VALUES('Alpha','abc');
291 PRAGMA foreign_key_check(c20);
292 }
293 } {c20 1 p5 0}
294 do_test fkey5-8.3 {
295 db eval {
296 DELETE FROM c20;
297 INSERT INTO c20 VALUES('abc','Alpha');
298 PRAGMA foreign_key_check(c20);
299 }
300 } {}
301 do_test fkey5-8.4 {
302 db eval {
303 INSERT INTO c21 VALUES('alpha','abc ');
304 PRAGMA foreign_key_check(c21);
305 }
306 } {}
307 do_test fkey5-8.5 {
308 db eval {
309 DELETE FROM c21;
310 INSERT INTO c19 VALUES('Alpha','abc');
311 PRAGMA foreign_key_check(c21);
312 }
313 } {}
314 do_test fkey5-8.6 {
315 db eval {
316 INSERT INTO c22 VALUES('Alpha','abc');
317 PRAGMA foreign_key_check(c22);
318 }
319 } {c22 1 p6 0}
320 do_test fkey5-8.7 {
321 db eval {
322 DELETE FROM c22;
323 INSERT INTO c22 VALUES('abc ','ALPHA');
324 PRAGMA foreign_key_check(c22);
325 }
326 } {}
329 #-------------------------------------------------------------------------
330 # Tests 9.* verify that missing parent tables are handled correctly.
331 #
332 do_execsql_test 9.1.1 {
333 CREATE TABLE k1(x REFERENCES s1);
334 PRAGMA foreign_key_check(k1);
335 } {}
336 do_execsql_test 9.1.2 {
337 INSERT INTO k1 VALUES(NULL);
338 PRAGMA foreign_key_check(k1);
339 } {}
340 do_execsql_test 9.1.3 {
341 INSERT INTO k1 VALUES(1);
342 PRAGMA foreign_key_check(k1);
343 } {k1 2 s1 0}
345 do_execsql_test 9.2.1 {
346 CREATE TABLE k2(x, y, FOREIGN KEY(x, y) REFERENCES s1(a, b));
347 PRAGMA foreign_key_check(k2);
348 } {}
349 do_execsql_test 9.2 {
350 INSERT INTO k2 VALUES(NULL, 'five');
351 PRAGMA foreign_key_check(k2);
352 } {}
353 do_execsql_test 9.3 {
354 INSERT INTO k2 VALUES('one', NULL);
355 PRAGMA foreign_key_check(k2);
356 } {}
357 do_execsql_test 9.4 {
358 INSERT INTO k2 VALUES('six', 'seven');
359 PRAGMA foreign_key_check(k2);
360 } {k2 3 s1 0}
363 finish_test