Debian Codegraph @

test/fkey5.test

raw
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-15402-03103 PRAGMA schema.foreign_key_check; PRAGMA
16 # schema.foreign_key_check(table-name);
17 #
18 # EVIDENCE-OF: R-41653-15278 The foreign_key_check pragma checks the
19 # database, or the table called "table-name", for foreign key
20 # constraints that are violated. The foreign_key_check pragma returns
21 # one row output for each foreign key violation.
22
23 set testdir [file dirname $argv0]
24 source $testdir/tester.tcl
25 set testprefix fkey5
26
27 ifcapable {!foreignkey} {
28 finish_test
29 return
30 }
31
32 do_test fkey5-1.1 {
33 db eval {
34 CREATE TABLE p1(a INTEGER PRIMARY KEY); INSERT INTO p1 VALUES(88),(89);
35 CREATE TABLE p2(a INT PRIMARY KEY); INSERT INTO p2 VALUES(77),(78);
36 CREATE TABLE p3(a TEXT PRIMARY KEY);
37 INSERT INTO p3 VALUES(66),(67),('alpha'),('BRAVO');
38 CREATE TABLE p4(a TEXT PRIMARY KEY COLLATE nocase);
39 INSERT INTO p4 VALUES('alpha'),('BRAVO'),('55'),('Delta'),('ECHO');
40 CREATE TABLE p5(a INTEGER PRIMARY KEY, b, c, UNIQUE(b,c));
41 INSERT INTO p5 VALUES(1,'Alpha','abc'),(2,'beta','def');
42 CREATE TABLE p6(a INTEGER PRIMARY KEY, b TEXT COLLATE nocase,
43 c TEXT COLLATE rtrim, UNIQUE(b,c));
44 INSERT INTO p6 VALUES(1,'Alpha','abc '),(2,'bETA','def ');
45
46 CREATE TABLE c1(x INTEGER PRIMARY KEY references p1);
47 CREATE TABLE c2(x INTEGER PRIMARY KEY references p2);
48 CREATE TABLE c3(x INTEGER PRIMARY KEY references p3);
49 CREATE TABLE c4(x INTEGER PRIMARY KEY references p4);
50 CREATE TABLE c5(x INT references p1);
51 CREATE TABLE c6(x INT references p2);
52 CREATE TABLE c7(x INT references p3);
53 CREATE TABLE c8(x INT references p4);
54 CREATE TABLE c9(x TEXT UNIQUE references p1);
55 CREATE TABLE c10(x TEXT UNIQUE references p2);
56 CREATE TABLE c11(x TEXT UNIQUE references p3);
57 CREATE TABLE c12(x TEXT UNIQUE references p4);
58 CREATE TABLE c13(x TEXT COLLATE nocase references p3);
59 CREATE TABLE c14(x TEXT COLLATE nocase references p4);
60 CREATE TABLE c15(x, y, FOREIGN KEY(x,y) REFERENCES p5(b,c));
61 CREATE TABLE c16(x, y, FOREIGN KEY(x,y) REFERENCES p5(c,b));
62 CREATE TABLE c17(x, y, FOREIGN KEY(x,y) REFERENCES p6(b,c));
63 CREATE TABLE c18(x, y, FOREIGN KEY(x,y) REFERENCES p6(c,b));
64 CREATE TABLE c19(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
65 FOREIGN KEY(x,y) REFERENCES p5(b,c));
66 CREATE TABLE c20(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
67 FOREIGN KEY(x,y) REFERENCES p5(c,b));
68 CREATE TABLE c21(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
69 FOREIGN KEY(x,y) REFERENCES p6(b,c));
70 CREATE TABLE c22(x TEXT COLLATE nocase, y TEXT COLLATE rtrim,
71 FOREIGN KEY(x,y) REFERENCES p6(c,b));
72
73 PRAGMA foreign_key_check;
74 }
75 } {}
76 do_test fkey5-1.2 {
77 db eval {
78 INSERT INTO c1 VALUES(90),(87),(88);
79 PRAGMA foreign_key_check;
80 }
81 } {c1 87 p1 0 c1 90 p1 0}
82 do_test fkey5-1.2b {
83 db eval {
84 PRAGMA main.foreign_key_check;
85 }
86 } {c1 87 p1 0 c1 90 p1 0}
87 do_test fkey5-1.2c {
88 db eval {
89 PRAGMA temp.foreign_key_check;
90 }
91 } {}
92 do_test fkey5-1.3 {
93 db eval {
94 PRAGMA foreign_key_check(c1);
95 }
96 } {c1 87 p1 0 c1 90 p1 0}
97 do_test fkey5-1.4 {
98 db eval {
99 PRAGMA foreign_key_check(c2);
100 }
101 } {}
102 do_test fkey5-1.5 {
103 db eval {
104 PRAGMA main.foreign_key_check(c2);
105 }
106 } {}
107 do_test fkey5-1.6 {
108 catchsql {
109 PRAGMA temp.foreign_key_check(c2);
110 }
111 } {1 {no such table: temp.c2}}
112
113 # EVIDENCE-OF: R-45728-08709 There are four columns in each result row.
114 #
115 # EVIDENCE-OF: R-55672-01620 The first column is the name of the table
116 # that contains the REFERENCES clause.
117 #
118 # EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row
119 # that contains the invalid REFERENCES clause, or NULL if the child
120 # table is a WITHOUT ROWID table.
121 #
122 # The second clause in the previous is tested by fkey5-10.3.
123 #
124 # EVIDENCE-OF: R-40482-20265 The third column is the name of the table
125 # that is referred to.
126 #
127 # EVIDENCE-OF: R-62839-07969 The fourth column is the index of the
128 # specific foreign key constraint that failed.
129 #
130 do_test fkey5-2.0 {
131 db eval {
132 INSERT INTO c5 SELECT x FROM c1;
133 DELETE FROM c1;
134 PRAGMA foreign_key_check;
135 }
136 } {c5 1 p1 0 c5 3 p1 0}
137 do_test fkey5-2.1 {
138 db eval {
139 PRAGMA foreign_key_check(c5);
140 }
141 } {c5 1 p1 0 c5 3 p1 0}
142 do_test fkey5-2.2 {
143 db eval {
144 PRAGMA foreign_key_check(c1);
145 }
146 } {}
147 do_execsql_test fkey5-2.3 {
148 PRAGMA foreign_key_list(c5);
149 } {0 0 p1 x {} {NO ACTION} {NO ACTION} NONE}
150
151 do_test fkey5-3.0 {
152 db eval {
153 INSERT INTO c9 SELECT x FROM c5;
154 DELETE FROM c5;
155 PRAGMA foreign_key_check;
156 }
157 } {c9 1 p1 0 c9 3 p1 0}
158 do_test fkey5-3.1 {
159 db eval {
160 PRAGMA foreign_key_check(c9);
161 }
162 } {c9 1 p1 0 c9 3 p1 0}
163 do_test fkey5-3.2 {
164 db eval {
165 PRAGMA foreign_key_check(c5);
166 }
167 } {}
168
169 do_test fkey5-4.0 {
170 db eval {
171 DELETE FROM c9;
172 INSERT INTO c2 VALUES(79),(77),(76);
173 PRAGMA foreign_key_check;
174 }
175 } {c2 76 p2 0 c2 79 p2 0}
176 do_test fkey5-4.1 {
177 db eval {
178 PRAGMA foreign_key_check(c2);
179 }
180 } {c2 76 p2 0 c2 79 p2 0}
181 do_test fkey5-4.2 {
182 db eval {
183 INSERT INTO c6 SELECT x FROM c2;
184 DELETE FROM c2;
185 PRAGMA foreign_key_check;
186 }
187 } {c6 1 p2 0 c6 3 p2 0}
188 do_test fkey5-4.3 {
189 db eval {
190 PRAGMA foreign_key_check(c6);
191 }
192 } {c6 1 p2 0 c6 3 p2 0}
193 do_test fkey5-4.4 {
194 db eval {
195 INSERT INTO c10 SELECT x FROM c6;
196 DELETE FROM c6;
197 PRAGMA foreign_key_check;
198 }
199 } {c10 1 p2 0 c10 3 p2 0}
200 do_test fkey5-4.5 {
201 db eval {
202 PRAGMA foreign_key_check(c10);
203 }
204 } {c10 1 p2 0 c10 3 p2 0}
205
206 do_test fkey5-5.0 {
207 db eval {
208 DELETE FROM c10;
209 INSERT INTO c3 VALUES(68),(67),(65);
210 PRAGMA foreign_key_check;
211 }
212 } {c3 65 p3 0 c3 68 p3 0}
213 do_test fkey5-5.1 {
214 db eval {
215 PRAGMA foreign_key_check(c3);
216 }
217 } {c3 65 p3 0 c3 68 p3 0}
218 do_test fkey5-5.2 {
219 db eval {
220 INSERT INTO c7 SELECT x FROM c3;
221 INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot');
222 DELETE FROM c3;
223 PRAGMA foreign_key_check;
224 }
225 } {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
226 do_test fkey5-5.3 {
227 db eval {
228 PRAGMA foreign_key_check(c7);
229 }
230 } {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0}
231 do_test fkey5-5.4 {
232 db eval {
233 INSERT INTO c11 SELECT x FROM c7;
234 DELETE FROM c7;
235 PRAGMA foreign_key_check;
236 }
237 } {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
238 do_test fkey5-5.5 {
239 db eval {
240 PRAGMA foreign_key_check(c11);
241 }
242 } {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0}
243
244 do_test fkey5-6.0 {
245 db eval {
246 DELETE FROM c11;
247 INSERT INTO c4 VALUES(54),(55),(56);
248 PRAGMA foreign_key_check;
249 }
250 } {c4 54 p4 0 c4 56 p4 0}
251 do_test fkey5-6.1 {
252 db eval {
253 PRAGMA foreign_key_check(c4);
254 }
255 } {c4 54 p4 0 c4 56 p4 0}
256 do_test fkey5-6.2 {
257 db eval {
258 INSERT INTO c8 SELECT x FROM c4;
259 INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot');
260 DELETE FROM c4;
261 PRAGMA foreign_key_check;
262 }
263 } {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
264 do_test fkey5-6.3 {
265 db eval {
266 PRAGMA foreign_key_check(c8);
267 }
268 } {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0}
269 do_test fkey5-6.4 {
270 db eval {
271 INSERT INTO c12 SELECT x FROM c8;
272 DELETE FROM c8;
273 PRAGMA foreign_key_check;
274 }
275 } {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
276 do_test fkey5-6.5 {
277 db eval {
278 PRAGMA foreign_key_check(c12);
279 }
280 } {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0}
281
282 do_test fkey5-7.1 {
283 set res {}
284 db eval {
285 INSERT OR IGNORE INTO c13 SELECT * FROM c12;
286 INSERT OR IGNORE INTO C14 SELECT * FROM c12;
287 DELETE FROM c12;
288 PRAGMA foreign_key_check;
289 } {
290 lappend res [list $table $rowid $fkid $parent]
291 }
292 lsort $res
293 } {{c13 1 0 p3} {c13 2 0 p3} {c13 3 0 p3} {c13 4 0 p3} {c13 5 0 p3} {c13 6 0 p3} {c14 1 0 p4} {c14 3 0 p4} {c14 6 0 p4}}
294 do_test fkey5-7.2 {
295 db eval {
296 PRAGMA foreign_key_check(c14);
297 }
298 } {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0}
299 do_test fkey5-7.3 {
300 db eval {
301 PRAGMA foreign_key_check(c13);
302 }
303 } {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0}
304
305 do_test fkey5-8.0 {
306 db eval {
307 DELETE FROM c13;
308 DELETE FROM c14;
309 INSERT INTO c19 VALUES('alpha','abc');
310 PRAGMA foreign_key_check(c19);
311 }
312 } {c19 1 p5 0}
313 do_test fkey5-8.1 {
314 db eval {
315 DELETE FROM c19;
316 INSERT INTO c19 VALUES('Alpha','abc');
317 PRAGMA foreign_key_check(c19);
318 }
319 } {}
320 do_test fkey5-8.2 {
321 db eval {
322 INSERT INTO c20 VALUES('Alpha','abc');
323 PRAGMA foreign_key_check(c20);
324 }
325 } {c20 1 p5 0}
326 do_test fkey5-8.3 {
327 db eval {
328 DELETE FROM c20;
329 INSERT INTO c20 VALUES('abc','Alpha');
330 PRAGMA foreign_key_check(c20);
331 }
332 } {}
333 do_test fkey5-8.4 {
334 db eval {
335 INSERT INTO c21 VALUES('alpha','abc ');
336 PRAGMA foreign_key_check(c21);
337 }
338 } {}
339 do_test fkey5-8.5 {
340 db eval {
341 DELETE FROM c21;
342 INSERT INTO c19 VALUES('Alpha','abc');
343 PRAGMA foreign_key_check(c21);
344 }
345 } {}
346 do_test fkey5-8.6 {
347 db eval {
348 INSERT INTO c22 VALUES('Alpha','abc');
349 PRAGMA foreign_key_check(c22);
350 }
351 } {c22 1 p6 0}
352 do_test fkey5-8.7 {
353 db eval {
354 DELETE FROM c22;
355 INSERT INTO c22 VALUES('abc ','ALPHA');
356 PRAGMA foreign_key_check(c22);
357 }
358 } {}
359
360
361 #-------------------------------------------------------------------------
362 # Tests 9.* verify that missing parent tables are handled correctly.
363 #
364 do_execsql_test 9.1.1 {
365 CREATE TABLE k1(x REFERENCES s1);
366 PRAGMA foreign_key_check(k1);
367 } {}
368 do_execsql_test 9.1.2 {
369 INSERT INTO k1 VALUES(NULL);
370 PRAGMA foreign_key_check(k1);
371 } {}
372 do_execsql_test 9.1.3 {
373 INSERT INTO k1 VALUES(1);
374 PRAGMA foreign_key_check(k1);
375 } {k1 2 s1 0}
376
377 do_execsql_test 9.2.1 {
378 CREATE TABLE k2(x, y, FOREIGN KEY(x, y) REFERENCES s1(a, b));
379 PRAGMA foreign_key_check(k2);
380 } {}
381 do_execsql_test 9.2 {
382 INSERT INTO k2 VALUES(NULL, 'five');
383 PRAGMA foreign_key_check(k2);
384 } {}
385 do_execsql_test 9.3 {
386 INSERT INTO k2 VALUES('one', NULL);
387 PRAGMA foreign_key_check(k2);
388 } {}
389 do_execsql_test 9.4 {
390 INSERT INTO k2 VALUES('six', 'seven');
391 PRAGMA foreign_key_check(k2);
392 } {k2 3 s1 0}
393
394 #-------------------------------------------------------------------------
395 # Test using a WITHOUT ROWID table as the child table with an INTEGER
396 # PRIMARY KEY as the parent key.
397 #
398 reset_db
399 do_execsql_test 10.1 {
400 CREATE TABLE p30 (id INTEGER PRIMARY KEY);
401 CREATE TABLE IF NOT EXISTS c30 (
402 line INTEGER,
403 master REFERENCES p30(id),
404 PRIMARY KEY(master)
405 ) WITHOUT ROWID;
406
407 INSERT INTO p30 (id) VALUES (1);
408 INSERT INTO c30 (master, line) VALUES (1, 999);
409 }
410 do_execsql_test 10.2 {
411 PRAGMA foreign_key_check;
412 }
413 # EVIDENCE-OF: R-00471-55166 The second column is the rowid of the row
414 # that contains the invalid REFERENCES clause, or NULL if the child
415 # table is a WITHOUT ROWID table.
416 do_execsql_test 10.3 {
417 INSERT INTO c30 VALUES(45, 45);
418 PRAGMA foreign_key_check;
419 } {c30 {} p30 0}
420
421 #-------------------------------------------------------------------------
422 # Test "foreign key mismatch" errors.
423 #
424 reset_db
425 do_execsql_test 11.0 {
426 CREATE TABLE tt(y);
427 CREATE TABLE c11(x REFERENCES tt(y));
428 }
429 do_catchsql_test 11.1 {
430 PRAGMA foreign_key_check;
431 } {1 {foreign key mismatch - "c11" referencing "tt"}}
432
433 # 2020-07-03 Bug in foreign_key_check discovered while working on the
434 # forum reports that pragma_foreign_key_check does not accept an argument:
435 # If two separate schemas seem to reference one another, that causes
436 # problems for foreign_key_check.
437 #
438 reset_db
439 do_execsql_test 12.0 {
440 ATTACH ':memory:' as aux;
441 CREATE TABLE aux.t1(a INTEGER PRIMARY KEY, b TEXT REFERENCES t2);
442 CREATE TABLE main.t2(x TEXT PRIMARY KEY, y INT);
443 INSERT INTO main.t2 VALUES('abc',11),('def',22),('xyz',99);
444 INSERT INTO aux.t1 VALUES(5,'abc'),(7,'xyz'),(9,'oops');
445 PRAGMA foreign_key_check=t1;
446 } {t1 5 t2 0 t1 7 t2 0 t1 9 t2 0}
447 do_execsql_test 12.1 {
448 CREATE TABLE aux.t2(x TEXT PRIMARY KEY, y INT);
449 INSERT INTO aux.t2 VALUES('abc',11),('def',22),('xyz',99);
450 PRAGMA foreign_key_check=t1;
451 } {t1 9 t2 0}
452
453 # 2020-07-03: the pragma_foreign_key_check virtual table should
454 # accept arguments for the table name and/or schema name.
455 #
456 ifcapable vtab {
457 do_execsql_test 13.0 {
458 SELECT *, 'x' FROM pragma_foreign_key_check('t1');
459 } {t1 9 t2 0 x}
460 do_catchsql_test 13.1 {
461 SELECT *, 'x' FROM pragma_foreign_key_check('t1','main');
462 } {1 {no such table: main.t1}}
463 do_execsql_test 13.2 {
464 SELECT *, 'x' FROM pragma_foreign_key_check('t1','aux');
465 } {t1 9 t2 0 x}
466 }
467
468 ifcapable vtab {
469 reset_db
470 do_execsql_test 13.10 {
471 PRAGMA foreign_keys=OFF;
472 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT REFERENCES t2);
473 CREATE TABLE t2(x TEXT PRIMARY KEY, y INT);
474 CREATE TABLE t3(w TEXT, z INT REFERENCES t1);
475 INSERT INTO t2 VALUES('abc',11),('def',22),('xyz',99);
476 INSERT INTO t1 VALUES(5,'abc'),(7,'xyz'),(9,'oops');
477 INSERT INTO t3 VALUES(11,7),(22,19);
478 } {}
479 do_execsql_test 13.11 {
480 SELECT x.*, '|'
481 FROM sqlite_schema, pragma_foreign_key_check(name) AS x
482 WHERE type='table'
483 ORDER BY x."table";
484 } {t1 9 t2 0 | t3 2 t1 0 |}
485 do_execsql_test 13.12 {
486 SELECT *, '|'
487 FROM pragma_foreign_key_check AS x
488 ORDER BY x."table";
489 } {t1 9 t2 0 | t3 2 t1 0 |}
490 }
491
492 finish_test