| blob | 53b90dc91c21131f78025e0b978d1cd4e713b66e |
1 # 2009 September 15
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 implements tests for foreign keys.
14 #
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 ifcapable {!foreignkey||!trigger} {
20 finish_test
21 return
22 }
24 #-------------------------------------------------------------------------
25 # Test structure:
26 #
27 # fkey2-1.*: Simple tests to check that immediate and deferred foreign key
28 # constraints work when not inside a transaction.
29 #
30 # fkey2-2.*: Tests to verify that deferred foreign keys work inside
31 # explicit transactions (i.e that processing really is deferred).
32 #
33 # fkey2-3.*: Tests that a statement transaction is rolled back if an
34 # immediate foreign key constraint is violated.
35 #
36 # fkey2-4.*: Test that FK actions may recurse even when recursive triggers
37 # are disabled.
38 #
39 # fkey2-5.*: Check that if foreign-keys are enabled, it is not possible
40 # to write to an FK column using the incremental blob API.
41 #
42 # fkey2-6.*: Test that FK processing is automatically disabled when
43 # running VACUUM.
44 #
45 # fkey2-7.*: Test using an IPK as the key in the child (referencing) table.
46 #
47 # fkey2-8.*: Test that enabling/disabling foreign key support while a
48 # transaction is active is not possible.
49 #
50 # fkey2-9.*: Test SET DEFAULT actions.
51 #
52 # fkey2-10.*: Test errors.
53 #
54 # fkey2-11.*: Test CASCADE actions.
55 #
56 # fkey2-12.*: Test RESTRICT actions.
57 #
58 # fkey2-13.*: Test that FK processing is performed when a row is REPLACED by
59 # an UPDATE or INSERT statement.
60 #
61 # fkey2-14.*: Test the ALTER TABLE and DROP TABLE commands.
62 #
63 # fkey2-15.*: Test that if there are no (known) outstanding foreign key
64 # constraint violations in the database, inserting into a parent
65 # table or deleting from a child table does not cause SQLite
66 # to check if this has repaired an outstanding violation.
67 #
68 # fkey2-16.*: Test that rows that refer to themselves may be inserted,
69 # updated and deleted.
70 #
71 # fkey2-17.*: Test that the "count_changes" pragma does not interfere with
72 # FK constraint processing.
73 #
74 # fkey2-18.*: Test that the authorization callback is invoked when processing
75 # FK constraints.
76 #
77 # fkey2-20.*: Test that ON CONFLICT clauses specified as part of statements
78 # do not affect the operation of FK constraints.
79 #
80 # fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
81 # command. Recycled to test the built-in implementation.
82 #
83 # fkey2-dd08e5.*: Tests to verify that ticket dd08e5a988d00decc4a543daa8d
84 # has been fixed.
85 #
88 execsql { PRAGMA foreign_keys = on }
90 set FkeySimpleSchema {
91 PRAGMA foreign_keys = on;
92 CREATE TABLE t1(a PRIMARY KEY, b);
93 CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);
95 CREATE TABLE t3(a PRIMARY KEY, b);
96 CREATE TABLE t4(c REFERENCES t3 /D/, d);
98 CREATE TABLE t7(a, b INTEGER PRIMARY KEY);
99 CREATE TABLE t8(c REFERENCES t7 /D/, d);
101 CREATE TABLE t9(a REFERENCES nosuchtable, b);
102 CREATE TABLE t10(a REFERENCES t9(c) /D/, b);
103 }
106 set FkeySimpleTests {
107 1.1 "INSERT INTO t2 VALUES(1, 3)" {1 {FOREIGN KEY constraint failed}}
108 1.2 "INSERT INTO t1 VALUES(1, 2)" {0 {}}
109 1.3 "INSERT INTO t2 VALUES(1, 3)" {0 {}}
110 1.4 "INSERT INTO t2 VALUES(2, 4)" {1 {FOREIGN KEY constraint failed}}
111 1.5 "INSERT INTO t2 VALUES(NULL, 4)" {0 {}}
112 1.6 "UPDATE t2 SET c=2 WHERE d=4" {1 {FOREIGN KEY constraint failed}}
113 1.7 "UPDATE t2 SET c=1 WHERE d=4" {0 {}}
114 1.9 "UPDATE t2 SET c=1 WHERE d=4" {0 {}}
115 1.10 "UPDATE t2 SET c=NULL WHERE d=4" {0 {}}
116 1.11 "DELETE FROM t1 WHERE a=1" {1 {FOREIGN KEY constraint failed}}
117 1.12 "UPDATE t1 SET a = 2" {1 {FOREIGN KEY constraint failed}}
118 1.13 "UPDATE t1 SET a = 1" {0 {}}
120 2.1 "INSERT INTO t4 VALUES(1, 3)" {1 {FOREIGN KEY constraint failed}}
121 2.2 "INSERT INTO t3 VALUES(1, 2)" {0 {}}
122 2.3 "INSERT INTO t4 VALUES(1, 3)" {0 {}}
124 4.1 "INSERT INTO t8 VALUES(1, 3)" {1 {FOREIGN KEY constraint failed}}
125 4.2 "INSERT INTO t7 VALUES(2, 1)" {0 {}}
126 4.3 "INSERT INTO t8 VALUES(1, 3)" {0 {}}
127 4.4 "INSERT INTO t8 VALUES(2, 4)" {1 {FOREIGN KEY constraint failed}}
128 4.5 "INSERT INTO t8 VALUES(NULL, 4)" {0 {}}
129 4.6 "UPDATE t8 SET c=2 WHERE d=4" {1 {FOREIGN KEY constraint failed}}
130 4.7 "UPDATE t8 SET c=1 WHERE d=4" {0 {}}
131 4.9 "UPDATE t8 SET c=1 WHERE d=4" {0 {}}
132 4.10 "UPDATE t8 SET c=NULL WHERE d=4" {0 {}}
133 4.11 "DELETE FROM t7 WHERE b=1" {1 {FOREIGN KEY constraint failed}}
134 4.12 "UPDATE t7 SET b = 2" {1 {FOREIGN KEY constraint failed}}
135 4.13 "UPDATE t7 SET b = 1" {0 {}}
136 4.14 "INSERT INTO t8 VALUES('a', 'b')" {1 {FOREIGN KEY constraint failed}}
137 4.15 "UPDATE t7 SET b = 5" {1 {FOREIGN KEY constraint failed}}
138 4.16 "UPDATE t7 SET rowid = 5" {1 {FOREIGN KEY constraint failed}}
139 4.17 "UPDATE t7 SET a = 10" {0 {}}
141 5.1 "INSERT INTO t9 VALUES(1, 3)" {1 {no such table: main.nosuchtable}}
142 5.2 "INSERT INTO t10 VALUES(1, 3)"
143 {1 {foreign key mismatch - "t10" referencing "t9"}}
144 }
146 do_test fkey2-1.1.0 {
147 execsql [string map {/D/ {}} $FkeySimpleSchema]
148 } {}
149 foreach {tn zSql res} $FkeySimpleTests {
150 do_test fkey2-1.1.$tn.1 { catchsql $zSql } $res
151 do_test fkey2-1.1.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
152 do_test fkey2-1.1.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
153 do_test fkey2-1.1.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
154 do_test fkey2-1.1.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
155 do_test fkey2-1.1.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
156 do_test fkey2-1.1.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
157 }
158 drop_all_tables
160 do_test fkey2-1.2.0 {
161 execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
162 } {}
163 foreach {tn zSql res} $FkeySimpleTests {
164 do_test fkey2-1.2.$tn { catchsql $zSql } $res
165 do_test fkey2-1.2.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
166 do_test fkey2-1.2.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
167 do_test fkey2-1.2.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
168 do_test fkey2-1.2.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
169 do_test fkey2-1.2.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
170 do_test fkey2-1.2.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
171 }
172 drop_all_tables
174 do_test fkey2-1.3.0 {
175 execsql [string map {/D/ {}} $FkeySimpleSchema]
176 execsql { PRAGMA count_changes = 1 }
177 } {}
178 foreach {tn zSql res} $FkeySimpleTests {
179 if {$res == "0 {}"} { set res {0 1} }
180 do_test fkey2-1.3.$tn { catchsql $zSql } $res
181 do_test fkey2-1.3.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
182 do_test fkey2-1.3.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
183 do_test fkey2-1.3.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
184 do_test fkey2-1.3.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
185 do_test fkey2-1.3.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
186 do_test fkey2-1.3.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
187 }
188 execsql { PRAGMA count_changes = 0 }
189 drop_all_tables
191 do_test fkey2-1.4.0 {
192 execsql [string map {/D/ {}} $FkeySimpleSchema]
193 execsql { PRAGMA count_changes = 1 }
194 } {}
195 foreach {tn zSql res} $FkeySimpleTests {
196 if {$res == "0 {}"} { set res {0 1} }
197 execsql BEGIN
198 do_test fkey2-1.4.$tn { catchsql $zSql } $res
199 execsql COMMIT
200 }
201 execsql { PRAGMA count_changes = 0 }
202 drop_all_tables
204 # Special test: When the parent key is an IPK, make sure the affinity of
205 # the IPK is not applied to the child key value before it is inserted
206 # into the child table.
207 do_test fkey2-1.5.1 {
208 execsql {
209 CREATE TABLE i(i INTEGER PRIMARY KEY);
210 CREATE TABLE j(j REFERENCES i);
211 INSERT INTO i VALUES(35);
212 INSERT INTO j VALUES('35.0');
213 SELECT j, typeof(j) FROM j;
214 }
215 } {35.0 text}
216 do_test fkey2-1.5.2 {
217 catchsql { DELETE FROM i }
218 } {1 {FOREIGN KEY constraint failed}}
220 # Same test using a regular primary key with integer affinity.
221 drop_all_tables
222 do_test fkey2-1.6.1 {
223 execsql {
224 CREATE TABLE i(i INT UNIQUE);
225 CREATE TABLE j(j REFERENCES i(i));
226 INSERT INTO i VALUES('35.0');
227 INSERT INTO j VALUES('35.0');
228 SELECT j, typeof(j) FROM j;
229 SELECT i, typeof(i) FROM i;
230 }
231 } {35.0 text 35 integer}
232 do_test fkey2-1.6.2 {
233 catchsql { DELETE FROM i }
234 } {1 {FOREIGN KEY constraint failed}}
236 # Use a collation sequence on the parent key.
237 drop_all_tables
238 do_test fkey2-1.7.1 {
239 execsql {
240 CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
241 CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
242 INSERT INTO i VALUES('SQLite');
243 INSERT INTO j VALUES('sqlite');
244 }
245 catchsql { DELETE FROM i }
246 } {1 {FOREIGN KEY constraint failed}}
248 # Use the parent key collation even if it is default and the child key
249 # has an explicit value.
250 drop_all_tables
251 do_test fkey2-1.7.2 {
252 execsql {
253 CREATE TABLE i(i TEXT PRIMARY KEY); -- Colseq is "BINARY"
254 CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
255 INSERT INTO i VALUES('SQLite');
256 }
257 catchsql { INSERT INTO j VALUES('sqlite') }
258 } {1 {FOREIGN KEY constraint failed}}
259 do_test fkey2-1.7.3 {
260 execsql {
261 INSERT INTO i VALUES('sqlite');
262 INSERT INTO j VALUES('sqlite');
263 DELETE FROM i WHERE i = 'SQLite';
264 }
265 catchsql { DELETE FROM i WHERE i = 'sqlite' }
266 } {1 {FOREIGN KEY constraint failed}}
268 #-------------------------------------------------------------------------
269 # This section (test cases fkey2-2.*) contains tests to check that the
270 # deferred foreign key constraint logic works.
271 #
272 proc fkey2-2-test {tn nocommit sql {res {}}} {
273 if {$res eq "FKV"} {
274 set expected {1 {FOREIGN KEY constraint failed}}
275 } else {
276 set expected [list 0 $res]
277 }
278 do_test fkey2-2.$tn [list catchsql $sql] $expected
279 if {$nocommit} {
280 do_test fkey2-2.${tn}c {
281 catchsql COMMIT
282 } {1 {FOREIGN KEY constraint failed}}
283 }
284 }
286 fkey2-2-test 1 0 {
287 CREATE TABLE node(
288 nodeid PRIMARY KEY,
289 parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
290 );
291 CREATE TABLE leaf(
292 cellid PRIMARY KEY,
293 parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
294 );
295 }
297 fkey2-2-test 1 0 "INSERT INTO node VALUES(1, 0)" FKV
298 fkey2-2-test 2 0 "BEGIN"
299 fkey2-2-test 3 1 "INSERT INTO node VALUES(1, 0)"
300 fkey2-2-test 4 0 "UPDATE node SET parent = NULL"
301 fkey2-2-test 5 0 "COMMIT"
302 fkey2-2-test 6 0 "SELECT * FROM node" {1 {}}
304 fkey2-2-test 7 0 "BEGIN"
305 fkey2-2-test 8 1 "INSERT INTO leaf VALUES('a', 2)"
306 fkey2-2-test 9 1 "INSERT INTO node VALUES(2, 0)"
307 fkey2-2-test 10 0 "UPDATE node SET parent = 1 WHERE nodeid = 2"
308 fkey2-2-test 11 0 "COMMIT"
309 fkey2-2-test 12 0 "SELECT * FROM node" {1 {} 2 1}
310 fkey2-2-test 13 0 "SELECT * FROM leaf" {a 2}
312 fkey2-2-test 14 0 "BEGIN"
313 fkey2-2-test 15 1 "DELETE FROM node WHERE nodeid = 2"
314 fkey2-2-test 16 0 "INSERT INTO node VALUES(2, NULL)"
315 fkey2-2-test 17 0 "COMMIT"
316 fkey2-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}}
317 fkey2-2-test 19 0 "SELECT * FROM leaf" {a 2}
319 fkey2-2-test 20 0 "BEGIN"
320 fkey2-2-test 21 0 "INSERT INTO leaf VALUES('b', 1)"
321 fkey2-2-test 22 0 "SAVEPOINT save"
322 fkey2-2-test 23 0 "DELETE FROM node WHERE nodeid = 1"
323 fkey2-2-test 24 0 "ROLLBACK TO save"
324 fkey2-2-test 25 0 "COMMIT"
325 fkey2-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}}
326 fkey2-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1}
328 fkey2-2-test 28 0 "BEGIN"
329 fkey2-2-test 29 0 "INSERT INTO leaf VALUES('c', 1)"
330 fkey2-2-test 30 0 "SAVEPOINT save"
331 fkey2-2-test 31 0 "DELETE FROM node WHERE nodeid = 1"
332 fkey2-2-test 32 1 "RELEASE save"
333 fkey2-2-test 33 1 "DELETE FROM leaf WHERE cellid = 'b'"
334 fkey2-2-test 34 0 "DELETE FROM leaf WHERE cellid = 'c'"
335 fkey2-2-test 35 0 "COMMIT"
336 fkey2-2-test 36 0 "SELECT * FROM node" {2 {}}
337 fkey2-2-test 37 0 "SELECT * FROM leaf" {a 2}
339 fkey2-2-test 38 0 "SAVEPOINT outer"
340 fkey2-2-test 39 1 "INSERT INTO leaf VALUES('d', 3)"
341 fkey2-2-test 40 1 "RELEASE outer" FKV
342 fkey2-2-test 41 1 "INSERT INTO leaf VALUES('e', 3)"
343 fkey2-2-test 42 0 "INSERT INTO node VALUES(3, 2)"
344 fkey2-2-test 43 0 "RELEASE outer"
346 fkey2-2-test 44 0 "SAVEPOINT outer"
347 fkey2-2-test 45 1 "DELETE FROM node WHERE nodeid=3"
348 fkey2-2-test 47 0 "INSERT INTO node VALUES(3, 2)"
349 fkey2-2-test 48 0 "ROLLBACK TO outer"
350 fkey2-2-test 49 0 "RELEASE outer"
352 fkey2-2-test 50 0 "SAVEPOINT outer"
353 fkey2-2-test 51 1 "INSERT INTO leaf VALUES('f', 4)"
354 fkey2-2-test 52 1 "SAVEPOINT inner"
355 fkey2-2-test 53 1 "INSERT INTO leaf VALUES('g', 4)"
356 fkey2-2-test 54 1 "RELEASE outer" FKV
357 fkey2-2-test 55 1 "ROLLBACK TO inner"
358 fkey2-2-test 56 0 "COMMIT" FKV
359 fkey2-2-test 57 0 "INSERT INTO node VALUES(4, NULL)"
360 fkey2-2-test 58 0 "RELEASE outer"
361 fkey2-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}}
362 fkey2-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4}
364 # The following set of tests check that if a statement that affects
365 # multiple rows violates some foreign key constraints, then strikes a
366 # constraint that causes the statement-transaction to be rolled back,
367 # the deferred constraint counter is correctly reset to the value it
368 # had before the statement-transaction was opened.
369 #
370 fkey2-2-test 61 0 "BEGIN"
371 fkey2-2-test 62 0 "DELETE FROM leaf"
372 fkey2-2-test 63 0 "DELETE FROM node"
373 fkey2-2-test 64 1 "INSERT INTO leaf VALUES('a', 1)"
374 fkey2-2-test 65 1 "INSERT INTO leaf VALUES('b', 2)"
375 fkey2-2-test 66 1 "INSERT INTO leaf VALUES('c', 1)"
376 do_test fkey2-2-test-67 {
377 catchsql "INSERT INTO node SELECT parent, 3 FROM leaf"
378 } {1 {UNIQUE constraint failed: node.nodeid}}
379 fkey2-2-test 68 0 "COMMIT" FKV
380 fkey2-2-test 69 1 "INSERT INTO node VALUES(1, NULL)"
381 fkey2-2-test 70 0 "INSERT INTO node VALUES(2, NULL)"
382 fkey2-2-test 71 0 "COMMIT"
384 fkey2-2-test 72 0 "BEGIN"
385 fkey2-2-test 73 1 "DELETE FROM node"
386 fkey2-2-test 74 0 "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf"
387 fkey2-2-test 75 0 "COMMIT"
389 #-------------------------------------------------------------------------
390 # Test cases fkey2-3.* test that a program that executes foreign key
391 # actions (CASCADE, SET DEFAULT, SET NULL etc.) or tests FK constraints
392 # opens a statement transaction if required.
393 #
394 # fkey2-3.1.*: Test UPDATE statements.
395 # fkey2-3.2.*: Test DELETE statements.
396 #
397 drop_all_tables
398 do_test fkey2-3.1.1 {
399 execsql {
400 CREATE TABLE ab(a PRIMARY KEY, b);
401 CREATE TABLE cd(
402 c PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE,
403 d
404 );
405 CREATE TABLE ef(
406 e REFERENCES cd ON UPDATE CASCADE,
407 f, CHECK (e!=5)
408 );
409 }
410 } {}
411 do_test fkey2-3.1.2 {
412 execsql {
413 INSERT INTO ab VALUES(1, 'b');
414 INSERT INTO cd VALUES(1, 'd');
415 INSERT INTO ef VALUES(1, 'e');
416 }
417 } {}
418 do_test fkey2-3.1.3 {
419 catchsql { UPDATE ab SET a = 5 }
420 } {1 {CHECK constraint failed: ef}}
421 do_test fkey2-3.1.4 {
422 execsql { SELECT * FROM ab }
423 } {1 b}
424 do_test fkey2-3.1.4 {
425 execsql BEGIN;
426 catchsql { UPDATE ab SET a = 5 }
427 } {1 {CHECK constraint failed: ef}}
428 do_test fkey2-3.1.5 {
429 execsql COMMIT;
430 execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
431 } {1 b 1 d 1 e}
433 do_test fkey2-3.2.1 {
434 execsql BEGIN;
435 catchsql { DELETE FROM ab }
436 } {1 {FOREIGN KEY constraint failed}}
437 do_test fkey2-3.2.2 {
438 execsql COMMIT
439 execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
440 } {1 b 1 d 1 e}
442 #-------------------------------------------------------------------------
443 # Test cases fkey2-4.* test that recursive foreign key actions
444 # (i.e. CASCADE) are allowed even if recursive triggers are disabled.
445 #
446 drop_all_tables
447 do_test fkey2-4.1 {
448 execsql {
449 CREATE TABLE t1(
450 node PRIMARY KEY,
451 parent REFERENCES t1 ON DELETE CASCADE
452 );
453 CREATE TABLE t2(node PRIMARY KEY, parent);
454 CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN
455 DELETE FROM t2 WHERE parent = old.node;
456 END;
457 INSERT INTO t1 VALUES(1, NULL);
458 INSERT INTO t1 VALUES(2, 1);
459 INSERT INTO t1 VALUES(3, 1);
460 INSERT INTO t1 VALUES(4, 2);
461 INSERT INTO t1 VALUES(5, 2);
462 INSERT INTO t1 VALUES(6, 3);
463 INSERT INTO t1 VALUES(7, 3);
464 INSERT INTO t2 SELECT * FROM t1;
465 }
466 } {}
467 do_test fkey2-4.2 {
468 execsql { PRAGMA recursive_triggers = off }
469 execsql {
470 BEGIN;
471 DELETE FROM t1 WHERE node = 1;
472 SELECT node FROM t1;
473 }
474 } {}
475 do_test fkey2-4.3 {
476 execsql {
477 DELETE FROM t2 WHERE node = 1;
478 SELECT node FROM t2;
479 ROLLBACK;
480 }
481 } {4 5 6 7}
482 do_test fkey2-4.4 {
483 execsql { PRAGMA recursive_triggers = on }
484 execsql {
485 BEGIN;
486 DELETE FROM t1 WHERE node = 1;
487 SELECT node FROM t1;
488 }
489 } {}
490 do_test fkey2-4.3 {
491 execsql {
492 DELETE FROM t2 WHERE node = 1;
493 SELECT node FROM t2;
494 ROLLBACK;
495 }
496 } {}
498 #-------------------------------------------------------------------------
499 # Test cases fkey2-5.* verify that the incremental blob API may not
500 # write to a foreign key column while foreign-keys are enabled.
501 #
502 drop_all_tables
503 ifcapable incrblob {
504 do_test fkey2-5.1 {
505 execsql {
506 CREATE TABLE t1(a PRIMARY KEY, b);
507 CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1(a));
508 INSERT INTO t1 VALUES('hello', 'world');
509 INSERT INTO t2 VALUES('key', 'hello');
510 }
511 } {}
512 do_test fkey2-5.2 {
513 set rc [catch { set fd [db incrblob t2 b 1] } msg]
514 list $rc $msg
515 } {1 {cannot open foreign key column for writing}}
516 do_test fkey2-5.3 {
517 set rc [catch { set fd [db incrblob -readonly t2 b 1] } msg]
518 close $fd
519 set rc
520 } {0}
521 do_test fkey2-5.4 {
522 execsql { PRAGMA foreign_keys = off }
523 set rc [catch { set fd [db incrblob t2 b 1] } msg]
524 close $fd
525 set rc
526 } {0}
527 do_test fkey2-5.5 {
528 execsql { PRAGMA foreign_keys = on }
529 } {}
530 }
532 drop_all_tables
533 ifcapable vacuum {
534 do_test fkey2-6.1 {
535 execsql {
536 CREATE TABLE t1(a REFERENCES t2(c), b);
537 CREATE TABLE t2(c UNIQUE, b);
538 INSERT INTO t2 VALUES(1, 2);
539 INSERT INTO t1 VALUES(1, 2);
540 VACUUM;
541 }
542 } {}
543 }
545 #-------------------------------------------------------------------------
546 # Test that it is possible to use an INTEGER PRIMARY KEY as the child key
547 # of a foreign constraint.
548 #
549 drop_all_tables
550 do_test fkey2-7.1 {
551 execsql {
552 CREATE TABLE t1(a PRIMARY KEY, b);
553 CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b);
554 }
555 } {}
556 do_test fkey2-7.2 {
557 catchsql { INSERT INTO t2 VALUES(1, 'A'); }
558 } {1 {FOREIGN KEY constraint failed}}
559 do_test fkey2-7.3 {
560 execsql {
561 INSERT INTO t1 VALUES(1, 2);
562 INSERT INTO t1 VALUES(2, 3);
563 INSERT INTO t2 VALUES(1, 'A');
564 }
565 } {}
566 do_test fkey2-7.4 {
567 execsql { UPDATE t2 SET c = 2 }
568 } {}
569 do_test fkey2-7.5 {
570 catchsql { UPDATE t2 SET c = 3 }
571 } {1 {FOREIGN KEY constraint failed}}
572 do_test fkey2-7.6 {
573 catchsql { DELETE FROM t1 WHERE a = 2 }
574 } {1 {FOREIGN KEY constraint failed}}
575 do_test fkey2-7.7 {
576 execsql { DELETE FROM t1 WHERE a = 1 }
577 } {}
578 do_test fkey2-7.8 {
579 catchsql { UPDATE t1 SET a = 3 }
580 } {1 {FOREIGN KEY constraint failed}}
581 do_test fkey2-7.9 {
582 catchsql { UPDATE t2 SET rowid = 3 }
583 } {1 {FOREIGN KEY constraint failed}}
585 #-------------------------------------------------------------------------
586 # Test that it is not possible to enable/disable FK support while a
587 # transaction is open.
588 #
589 drop_all_tables
590 proc fkey2-8-test {tn zSql value} {
591 do_test fkey-2.8.$tn.1 [list execsql $zSql] {}
592 do_test fkey-2.8.$tn.2 { execsql "PRAGMA foreign_keys" } $value
593 }
594 fkey2-8-test 1 { PRAGMA foreign_keys = 0 } 0
595 fkey2-8-test 2 { PRAGMA foreign_keys = 1 } 1
596 fkey2-8-test 3 { BEGIN } 1
597 fkey2-8-test 4 { PRAGMA foreign_keys = 0 } 1
598 fkey2-8-test 5 { COMMIT } 1
599 fkey2-8-test 6 { PRAGMA foreign_keys = 0 } 0
600 fkey2-8-test 7 { BEGIN } 0
601 fkey2-8-test 8 { PRAGMA foreign_keys = 1 } 0
602 fkey2-8-test 9 { COMMIT } 0
603 fkey2-8-test 10 { PRAGMA foreign_keys = 1 } 1
604 fkey2-8-test 11 { PRAGMA foreign_keys = off } 0
605 fkey2-8-test 12 { PRAGMA foreign_keys = on } 1
606 fkey2-8-test 13 { PRAGMA foreign_keys = no } 0
607 fkey2-8-test 14 { PRAGMA foreign_keys = yes } 1
608 fkey2-8-test 15 { PRAGMA foreign_keys = false } 0
609 fkey2-8-test 16 { PRAGMA foreign_keys = true } 1
611 #-------------------------------------------------------------------------
612 # The following tests, fkey2-9.*, test SET DEFAULT actions.
613 #
614 drop_all_tables
615 do_test fkey2-9.1.1 {
616 execsql {
617 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
618 CREATE TABLE t2(
619 c INTEGER PRIMARY KEY,
620 d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT
621 );
622 DELETE FROM t1;
623 }
624 } {}
625 do_test fkey2-9.1.2 {
626 execsql {
627 INSERT INTO t1 VALUES(1, 'one');
628 INSERT INTO t1 VALUES(2, 'two');
629 INSERT INTO t2 VALUES(1, 2);
630 SELECT * FROM t2;
631 DELETE FROM t1 WHERE a = 2;
632 SELECT * FROM t2;
633 }
634 } {1 2 1 1}
635 do_test fkey2-9.1.3 {
636 execsql {
637 INSERT INTO t1 VALUES(2, 'two');
638 UPDATE t2 SET d = 2;
639 DELETE FROM t1 WHERE a = 1;
640 SELECT * FROM t2;
641 }
642 } {1 2}
643 do_test fkey2-9.1.4 {
644 execsql { SELECT * FROM t1 }
645 } {2 two}
646 do_test fkey2-9.1.5 {
647 catchsql { DELETE FROM t1 }
648 } {1 {FOREIGN KEY constraint failed}}
650 do_test fkey2-9.2.1 {
651 execsql {
652 CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
653 CREATE TABLE cc(d DEFAULT 3, e DEFAULT 1, f DEFAULT 2,
654 FOREIGN KEY(f, d) REFERENCES pp
655 ON UPDATE SET DEFAULT
656 ON DELETE SET NULL
657 );
658 INSERT INTO pp VALUES(1, 2, 3);
659 INSERT INTO pp VALUES(4, 5, 6);
660 INSERT INTO pp VALUES(7, 8, 9);
661 }
662 } {}
663 do_test fkey2-9.2.2 {
664 execsql {
665 INSERT INTO cc VALUES(6, 'A', 5);
666 INSERT INTO cc VALUES(6, 'B', 5);
667 INSERT INTO cc VALUES(9, 'A', 8);
668 INSERT INTO cc VALUES(9, 'B', 8);
669 UPDATE pp SET b = 1 WHERE a = 7;
670 SELECT * FROM cc;
671 }
672 } {6 A 5 6 B 5 3 A 2 3 B 2}
673 do_test fkey2-9.2.3 {
674 execsql {
675 DELETE FROM pp WHERE a = 4;
676 SELECT * FROM cc;
677 }
678 } {{} A {} {} B {} 3 A 2 3 B 2}
680 #-------------------------------------------------------------------------
681 # The following tests, fkey2-10.*, test "foreign key mismatch" and
682 # other errors.
683 #
684 set tn 0
685 foreach zSql [list {
686 CREATE TABLE p(a PRIMARY KEY, b);
687 CREATE TABLE c(x REFERENCES p(c));
688 } {
689 CREATE TABLE c(x REFERENCES v(y));
690 CREATE VIEW v AS SELECT x AS y FROM c;
691 } {
692 CREATE TABLE p(a, b, PRIMARY KEY(a, b));
693 CREATE TABLE c(x REFERENCES p);
694 } {
695 CREATE TABLE p(a COLLATE binary, b);
696 CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
697 CREATE TABLE c(x REFERENCES p(a));
698 }] {
699 drop_all_tables
700 do_test fkey2-10.1.[incr tn] {
701 execsql $zSql
702 catchsql { INSERT INTO c DEFAULT VALUES }
703 } {/1 {foreign key mismatch - "c" referencing "."}/}
704 }
706 # "rowid" cannot be used as part of a child or parent key definition
707 # unless it happens to be the name of an explicitly declared column.
708 #
709 do_test fkey2-10.2.1 {
710 drop_all_tables
711 catchsql {
712 CREATE TABLE t1(a PRIMARY KEY, b);
713 CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a));
714 }
715 } {1 {unknown column "rowid" in foreign key definition}}
716 do_test fkey2-10.2.2 {
717 drop_all_tables
718 catchsql {
719 CREATE TABLE t1(a PRIMARY KEY, b);
720 CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a));
721 }
722 } {0 {}}
723 do_test fkey2-10.2.1 {
724 drop_all_tables
725 catchsql {
726 CREATE TABLE t1(a, b);
727 CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
728 INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
729 INSERT INTO t2 VALUES(1, 1);
730 }
731 } {1 {foreign key mismatch - "t2" referencing "t1"}}
732 do_test fkey2-10.2.2 {
733 drop_all_tables
734 catchsql {
735 CREATE TABLE t1(rowid PRIMARY KEY, b);
736 CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
737 INSERT INTO t1(rowid, b) VALUES(1, 1);
738 INSERT INTO t2 VALUES(1, 1);
739 }
740 } {0 {}}
743 #-------------------------------------------------------------------------
744 # The following tests, fkey2-11.*, test CASCADE actions.
745 #
746 drop_all_tables
747 do_test fkey2-11.1.1 {
748 execsql {
749 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
750 CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
752 INSERT INTO t1 VALUES(10, 100);
753 INSERT INTO t2 VALUES(10, 100);
754 UPDATE t1 SET a = 15;
755 SELECT * FROM t2;
756 }
757 } {15 100}
759 #-------------------------------------------------------------------------
760 # The following tests, fkey2-12.*, test RESTRICT actions.
761 #
762 drop_all_tables
763 do_test fkey2-12.1.1 {
764 execsql {
765 CREATE TABLE t1(a, b PRIMARY KEY);
766 CREATE TABLE t2(
767 x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED
768 );
769 INSERT INTO t1 VALUES(1, 'one');
770 INSERT INTO t1 VALUES(2, 'two');
771 INSERT INTO t1 VALUES(3, 'three');
772 }
773 } {}
774 do_test fkey2-12.1.2 {
775 execsql "BEGIN"
776 execsql "INSERT INTO t2 VALUES('two')"
777 } {}
778 do_test fkey2-12.1.3 {
779 execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'"
780 } {}
781 do_test fkey2-12.1.4 {
782 catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'"
783 } {1 {FOREIGN KEY constraint failed}}
784 do_test fkey2-12.1.5 {
785 execsql "DELETE FROM t1 WHERE b = 'two'"
786 } {}
787 do_test fkey2-12.1.6 {
788 catchsql "COMMIT"
789 } {1 {FOREIGN KEY constraint failed}}
790 do_test fkey2-12.1.7 {
791 execsql {
792 INSERT INTO t1 VALUES(2, 'two');
793 COMMIT;
794 }
795 } {}
797 drop_all_tables
798 do_test fkey2-12.2.1 {
799 execsql {
800 CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY);
801 CREATE TRIGGER tt1 AFTER DELETE ON t1
802 WHEN EXISTS ( SELECT 1 FROM t2 WHERE old.x = y )
803 BEGIN
804 INSERT INTO t1 VALUES(old.x);
805 END;
806 CREATE TABLE t2(y REFERENCES t1);
807 INSERT INTO t1 VALUES('A');
808 INSERT INTO t1 VALUES('B');
809 INSERT INTO t2 VALUES('a');
810 INSERT INTO t2 VALUES('b');
812 SELECT * FROM t1;
813 SELECT * FROM t2;
814 }
815 } {A B a b}
816 do_test fkey2-12.2.2 {
817 execsql { DELETE FROM t1 }
818 execsql {
819 SELECT * FROM t1;
820 SELECT * FROM t2;
821 }
822 } {A B a b}
823 do_test fkey2-12.2.3 {
824 execsql {
825 DROP TABLE t2;
826 CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT);
827 INSERT INTO t2 VALUES('a');
828 INSERT INTO t2 VALUES('b');
829 }
830 catchsql { DELETE FROM t1 }
831 } {1 {FOREIGN KEY constraint failed}}
832 do_test fkey2-12.2.4 {
833 execsql {
834 SELECT * FROM t1;
835 SELECT * FROM t2;
836 }
837 } {A B a b}
839 drop_all_tables
840 do_test fkey2-12.3.1 {
841 execsql {
842 CREATE TABLE up(
843 c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
844 c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
845 c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
846 c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
847 PRIMARY KEY(c34, c35)
848 );
849 CREATE TABLE down(
850 c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
851 c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
852 c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
853 c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
854 FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE
855 );
856 }
857 } {}
858 do_test fkey2-12.3.2 {
859 execsql {
860 INSERT INTO up(c34, c35) VALUES('yes', 'no');
861 INSERT INTO down(c39, c38) VALUES('yes', 'no');
862 UPDATE up SET c34 = 'possibly';
863 SELECT c38, c39 FROM down;
864 DELETE FROM down;
865 }
866 } {no possibly}
867 do_test fkey2-12.3.3 {
868 catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') }
869 } {1 {FOREIGN KEY constraint failed}}
870 do_test fkey2-12.3.4 {
871 execsql {
872 INSERT INTO up(c34, c35) VALUES('yes', 'no');
873 INSERT INTO down(c39, c38) VALUES('yes', 'no');
874 }
875 catchsql { DELETE FROM up WHERE c34 = 'yes' }
876 } {1 {FOREIGN KEY constraint failed}}
877 do_test fkey2-12.3.5 {
878 execsql {
879 DELETE FROM up WHERE c34 = 'possibly';
880 SELECT c34, c35 FROM up;
881 SELECT c39, c38 FROM down;
882 }
883 } {yes no yes no}
885 #-------------------------------------------------------------------------
886 # The following tests, fkey2-13.*, test that FK processing is performed
887 # when rows are REPLACEd.
888 #
889 drop_all_tables
890 do_test fkey2-13.1.1 {
891 execsql {
892 CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c));
893 CREATE TABLE cc(d, e, f UNIQUE, FOREIGN KEY(d, e) REFERENCES pp);
894 INSERT INTO pp VALUES(1, 2, 3);
895 INSERT INTO cc VALUES(2, 3, 1);
896 }
897 } {}
898 foreach {tn stmt} {
899 1 "REPLACE INTO pp VALUES(1, 4, 5)"
900 2 "REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 3, 4)"
901 } {
902 do_test fkey2-13.1.$tn.1 {
903 catchsql $stmt
904 } {1 {FOREIGN KEY constraint failed}}
905 do_test fkey2-13.1.$tn.2 {
906 execsql {
907 SELECT * FROM pp;
908 SELECT * FROM cc;
909 }
910 } {1 2 3 2 3 1}
911 do_test fkey2-13.1.$tn.3 {
912 execsql BEGIN;
913 catchsql $stmt
914 } {1 {FOREIGN KEY constraint failed}}
915 do_test fkey2-13.1.$tn.4 {
916 execsql {
917 COMMIT;
918 SELECT * FROM pp;
919 SELECT * FROM cc;
920 }
921 } {1 2 3 2 3 1}
922 }
923 do_test fkey2-13.1.3 {
924 execsql {
925 REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 2, 3);
926 SELECT rowid, * FROM pp;
927 SELECT * FROM cc;
928 }
929 } {1 2 2 3 2 3 1}
930 do_test fkey2-13.1.4 {
931 execsql {
932 REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3);
933 SELECT rowid, * FROM pp;
934 SELECT * FROM cc;
935 }
936 } {2 2 2 3 2 3 1}
938 #-------------------------------------------------------------------------
939 # The following tests, fkey2-14.*, test that the "DROP TABLE" and "ALTER
940 # TABLE" commands work as expected wrt foreign key constraints.
941 #
942 # fkey2-14.1*: ALTER TABLE ADD COLUMN
943 # fkey2-14.2*: ALTER TABLE RENAME TABLE
944 # fkey2-14.3*: DROP TABLE
945 #
946 drop_all_tables
947 ifcapable altertable {
948 do_test fkey2-14.1.1 {
949 # Adding a column with a REFERENCES clause is not supported.
950 execsql {
951 CREATE TABLE t1(a PRIMARY KEY);
952 CREATE TABLE t2(a, b);
953 }
954 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
955 } {0 {}}
956 do_test fkey2-14.1.2 {
957 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
958 } {0 {}}
959 do_test fkey2-14.1.3 {
960 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
961 } {0 {}}
962 do_test fkey2-14.1.4 {
963 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
964 } {1 {Cannot add a REFERENCES column with non-NULL default value}}
965 do_test fkey2-14.1.5 {
966 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
967 } {1 {Cannot add a REFERENCES column with non-NULL default value}}
968 do_test fkey2-14.1.6 {
969 execsql {
970 PRAGMA foreign_keys = off;
971 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
972 PRAGMA foreign_keys = on;
973 SELECT sql FROM sqlite_master WHERE name='t2';
974 }
975 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
978 # Test the sqlite_rename_parent() function directly.
979 #
980 proc test_rename_parent {zCreate zOld zNew} {
981 db eval {SELECT sqlite_rename_parent($zCreate, $zOld, $zNew)}
982 }
983 do_test fkey2-14.2.1.1 {
984 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
985 } {{CREATE TABLE t1(a REFERENCES "t3")}}
986 do_test fkey2-14.2.1.2 {
987 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
988 } {{CREATE TABLE t1(a REFERENCES t2)}}
989 do_test fkey2-14.2.1.3 {
990 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
991 } {{CREATE TABLE t1(a REFERENCES "t3")}}
993 # Test ALTER TABLE RENAME TABLE a bit.
994 #
995 do_test fkey2-14.2.2.1 {
996 drop_all_tables
997 execsql {
998 CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1);
999 CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
1000 CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
1001 }
1002 execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
1003 } [list \
1004 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \
1005 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \
1006 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \
1007 ]
1008 do_test fkey2-14.2.2.2 {
1009 execsql { ALTER TABLE t1 RENAME TO t4 }
1010 execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
1011 } [list \
1012 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \
1013 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \
1014 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
1015 ]
1016 do_test fkey2-14.2.2.3 {
1017 catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
1018 } {1 {FOREIGN KEY constraint failed}}
1019 do_test fkey2-14.2.2.4 {
1020 execsql { INSERT INTO t4 VALUES(1, NULL) }
1021 } {}
1022 do_test fkey2-14.2.2.5 {
1023 catchsql { UPDATE t4 SET b = 5 }
1024 } {1 {FOREIGN KEY constraint failed}}
1025 do_test fkey2-14.2.2.6 {
1026 catchsql { UPDATE t4 SET b = 1 }
1027 } {0 {}}
1028 do_test fkey2-14.2.2.7 {
1029 execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
1030 } {}
1032 # Repeat for TEMP tables
1033 #
1034 drop_all_tables
1035 do_test fkey2-14.1tmp.1 {
1036 # Adding a column with a REFERENCES clause is not supported.
1037 execsql {
1038 CREATE TEMP TABLE t1(a PRIMARY KEY);
1039 CREATE TEMP TABLE t2(a, b);
1040 }
1041 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
1042 } {0 {}}
1043 do_test fkey2-14.1tmp.2 {
1044 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
1045 } {0 {}}
1046 do_test fkey2-14.1tmp.3 {
1047 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
1048 } {0 {}}
1049 do_test fkey2-14.1tmp.4 {
1050 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
1051 } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1052 do_test fkey2-14.1tmp.5 {
1053 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
1054 } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1055 do_test fkey2-14.1tmp.6 {
1056 execsql {
1057 PRAGMA foreign_keys = off;
1058 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
1059 PRAGMA foreign_keys = on;
1060 SELECT sql FROM sqlite_temp_master WHERE name='t2';
1061 }
1062 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
1064 do_test fkey2-14.2tmp.1.1 {
1065 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
1066 } {{CREATE TABLE t1(a REFERENCES "t3")}}
1067 do_test fkey2-14.2tmp.1.2 {
1068 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
1069 } {{CREATE TABLE t1(a REFERENCES t2)}}
1070 do_test fkey2-14.2tmp.1.3 {
1071 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
1072 } {{CREATE TABLE t1(a REFERENCES "t3")}}
1074 # Test ALTER TABLE RENAME TABLE a bit.
1075 #
1076 do_test fkey2-14.2tmp.2.1 {
1077 drop_all_tables
1078 execsql {
1079 CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1);
1080 CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
1081 CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
1082 }
1083 execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
1084 } [list \
1085 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \
1086 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \
1087 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \
1088 ]
1089 do_test fkey2-14.2tmp.2.2 {
1090 execsql { ALTER TABLE t1 RENAME TO t4 }
1091 execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
1092 } [list \
1093 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \
1094 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \
1095 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
1096 ]
1097 do_test fkey2-14.2tmp.2.3 {
1098 catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
1099 } {1 {FOREIGN KEY constraint failed}}
1100 do_test fkey2-14.2tmp.2.4 {
1101 execsql { INSERT INTO t4 VALUES(1, NULL) }
1102 } {}
1103 do_test fkey2-14.2tmp.2.5 {
1104 catchsql { UPDATE t4 SET b = 5 }
1105 } {1 {FOREIGN KEY constraint failed}}
1106 do_test fkey2-14.2tmp.2.6 {
1107 catchsql { UPDATE t4 SET b = 1 }
1108 } {0 {}}
1109 do_test fkey2-14.2tmp.2.7 {
1110 execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
1111 } {}
1113 # Repeat for ATTACH-ed tables
1114 #
1115 drop_all_tables
1116 do_test fkey2-14.1aux.1 {
1117 # Adding a column with a REFERENCES clause is not supported.
1118 execsql {
1119 ATTACH ':memory:' AS aux;
1120 CREATE TABLE aux.t1(a PRIMARY KEY);
1121 CREATE TABLE aux.t2(a, b);
1122 }
1123 catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
1124 } {0 {}}
1125 do_test fkey2-14.1aux.2 {
1126 catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
1127 } {0 {}}
1128 do_test fkey2-14.1aux.3 {
1129 catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
1130 } {0 {}}
1131 do_test fkey2-14.1aux.4 {
1132 catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
1133 } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1134 do_test fkey2-14.1aux.5 {
1135 catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
1136 } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1137 do_test fkey2-14.1aux.6 {
1138 execsql {
1139 PRAGMA foreign_keys = off;
1140 ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
1141 PRAGMA foreign_keys = on;
1142 SELECT sql FROM aux.sqlite_master WHERE name='t2';
1143 }
1144 } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
1146 do_test fkey2-14.2aux.1.1 {
1147 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
1148 } {{CREATE TABLE t1(a REFERENCES "t3")}}
1149 do_test fkey2-14.2aux.1.2 {
1150 test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
1151 } {{CREATE TABLE t1(a REFERENCES t2)}}
1152 do_test fkey2-14.2aux.1.3 {
1153 test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
1154 } {{CREATE TABLE t1(a REFERENCES "t3")}}
1156 # Test ALTER TABLE RENAME TABLE a bit.
1157 #
1158 do_test fkey2-14.2aux.2.1 {
1159 drop_all_tables
1160 execsql {
1161 CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1);
1162 CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
1163 CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
1164 }
1165 execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
1166 } [list \
1167 {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \
1168 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \
1169 {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \
1170 ]
1171 do_test fkey2-14.2aux.2.2 {
1172 execsql { ALTER TABLE t1 RENAME TO t4 }
1173 execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
1174 } [list \
1175 {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \
1176 {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \
1177 {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
1178 ]
1179 do_test fkey2-14.2aux.2.3 {
1180 catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
1181 } {1 {FOREIGN KEY constraint failed}}
1182 do_test fkey2-14.2aux.2.4 {
1183 execsql { INSERT INTO t4 VALUES(1, NULL) }
1184 } {}
1185 do_test fkey2-14.2aux.2.5 {
1186 catchsql { UPDATE t4 SET b = 5 }
1187 } {1 {FOREIGN KEY constraint failed}}
1188 do_test fkey2-14.2aux.2.6 {
1189 catchsql { UPDATE t4 SET b = 1 }
1190 } {0 {}}
1191 do_test fkey2-14.2aux.2.7 {
1192 execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
1193 } {}
1194 }
1196 do_test fkey-2.14.3.1 {
1197 drop_all_tables
1198 execsql {
1199 CREATE TABLE t1(a, b REFERENCES nosuchtable);
1200 DROP TABLE t1;
1201 }
1202 } {}
1203 do_test fkey-2.14.3.2 {
1204 execsql {
1205 CREATE TABLE t1(a PRIMARY KEY, b);
1206 INSERT INTO t1 VALUES('a', 1);
1207 CREATE TABLE t2(x REFERENCES t1);
1208 INSERT INTO t2 VALUES('a');
1209 }
1210 } {}
1211 do_test fkey-2.14.3.3 {
1212 catchsql { DROP TABLE t1 }
1213 } {1 {FOREIGN KEY constraint failed}}
1214 do_test fkey-2.14.3.4 {
1215 execsql {
1216 DELETE FROM t2;
1217 DROP TABLE t1;
1218 }
1219 } {}
1220 do_test fkey-2.14.3.4 {
1221 catchsql { INSERT INTO t2 VALUES('x') }
1222 } {1 {no such table: main.t1}}
1223 do_test fkey-2.14.3.5 {
1224 execsql {
1225 CREATE TABLE t1(x PRIMARY KEY);
1226 INSERT INTO t1 VALUES('x');
1227 }
1228 execsql { INSERT INTO t2 VALUES('x') }
1229 } {}
1230 do_test fkey-2.14.3.6 {
1231 catchsql { DROP TABLE t1 }
1232 } {1 {FOREIGN KEY constraint failed}}
1233 do_test fkey-2.14.3.7 {
1234 execsql {
1235 DROP TABLE t2;
1236 DROP TABLE t1;
1237 }
1238 } {}
1239 do_test fkey-2.14.3.8 {
1240 execsql {
1241 CREATE TABLE pp(x, y, PRIMARY KEY(x, y));
1242 CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
1243 }
1244 catchsql { INSERT INTO cc VALUES(1, 2) }
1245 } {1 {foreign key mismatch - "cc" referencing "pp"}}
1246 do_test fkey-2.14.3.9 {
1247 execsql { DROP TABLE cc }
1248 } {}
1249 do_test fkey-2.14.3.10 {
1250 execsql {
1251 CREATE TABLE cc(a, b,
1252 FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED
1253 );
1254 }
1255 execsql {
1256 INSERT INTO pp VALUES('a', 'b');
1257 INSERT INTO cc VALUES('a', 'b');
1258 BEGIN;
1259 DROP TABLE pp;
1260 CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
1261 INSERT INTO pp VALUES(1, 'a', 'b');
1262 COMMIT;
1263 }
1264 } {}
1265 do_test fkey-2.14.3.11 {
1266 execsql {
1267 BEGIN;
1268 DROP TABLE cc;
1269 DROP TABLE pp;
1270 COMMIT;
1271 }
1272 } {}
1273 do_test fkey-2.14.3.12 {
1274 execsql {
1275 CREATE TABLE b1(a, b);
1276 CREATE TABLE b2(a, b REFERENCES b1);
1277 DROP TABLE b1;
1278 }
1279 } {}
1280 do_test fkey-2.14.3.13 {
1281 execsql {
1282 CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED);
1283 DROP TABLE b2;
1284 }
1285 } {}
1287 # Test that nothing goes wrong when dropping a table that refers to a view.
1288 # Or dropping a view that an existing FK (incorrectly) refers to. Or either
1289 # of the above scenarios with a virtual table.
1290 drop_all_tables
1291 do_test fkey-2.14.4.1 {
1292 execsql {
1293 CREATE TABLE t1(x REFERENCES v);
1294 CREATE VIEW v AS SELECT * FROM t1;
1295 }
1296 } {}
1297 do_test fkey-2.14.4.2 {
1298 execsql {
1299 DROP VIEW v;
1300 }
1301 } {}
1302 ifcapable vtab {
1303 register_echo_module db
1304 do_test fkey-2.14.4.3 {
1305 execsql { CREATE VIRTUAL TABLE v USING echo(t1) }
1306 } {}
1307 do_test fkey-2.14.4.2 {
1308 execsql {
1309 DROP TABLE v;
1310 }
1311 } {}
1312 }
1314 #-------------------------------------------------------------------------
1315 # The following tests, fkey2-15.*, test that unnecessary FK related scans
1316 # and lookups are avoided when the constraint counters are zero.
1317 #
1318 drop_all_tables
1319 proc execsqlS {zSql} {
1320 set ::sqlite_search_count 0
1321 set ::sqlite_found_count 0
1322 set res [uplevel [list execsql $zSql]]
1323 concat [expr $::sqlite_found_count + $::sqlite_search_count] $res
1324 }
1325 do_test fkey2-15.1.1 {
1326 execsql {
1327 CREATE TABLE pp(a PRIMARY KEY, b);
1328 CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED);
1329 INSERT INTO pp VALUES(1, 'one');
1330 INSERT INTO pp VALUES(2, 'two');
1331 INSERT INTO cc VALUES('neung', 1);
1332 INSERT INTO cc VALUES('song', 2);
1333 }
1334 } {}
1335 do_test fkey2-15.1.2 {
1336 execsqlS { INSERT INTO pp VALUES(3, 'three') }
1337 } {0}
1338 do_test fkey2-15.1.3 {
1339 execsql {
1340 BEGIN;
1341 INSERT INTO cc VALUES('see', 4); -- Violates deferred constraint
1342 }
1343 execsqlS { INSERT INTO pp VALUES(5, 'five') }
1344 } {2}
1345 do_test fkey2-15.1.4 {
1346 execsql { DELETE FROM cc WHERE x = 'see' }
1347 execsqlS { INSERT INTO pp VALUES(6, 'six') }
1348 } {0}
1349 do_test fkey2-15.1.5 {
1350 execsql COMMIT
1351 } {}
1352 do_test fkey2-15.1.6 {
1353 execsql BEGIN
1354 execsqlS {
1355 DELETE FROM cc WHERE x = 'neung';
1356 ROLLBACK;
1357 }
1358 } {1}
1359 do_test fkey2-15.1.7 {
1360 execsql {
1361 BEGIN;
1362 DELETE FROM pp WHERE a = 2;
1363 }
1364 execsqlS {
1365 DELETE FROM cc WHERE x = 'neung';
1366 ROLLBACK;
1367 }
1368 } {2}
1370 #-------------------------------------------------------------------------
1371 # This next block of tests, fkey2-16.*, test that rows that refer to
1372 # themselves may be inserted and deleted.
1373 #
1374 foreach {tn zSchema} {
1375 1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) }
1376 2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) }
1377 3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) }
1378 } {
1379 drop_all_tables
1380 do_test fkey2-16.1.$tn.1 {
1381 execsql $zSchema
1382 execsql { INSERT INTO self VALUES(13, 13) }
1383 } {}
1384 do_test fkey2-16.1.$tn.2 {
1385 execsql { UPDATE self SET a = 14, b = 14 }
1386 } {}
1388 do_test fkey2-16.1.$tn.3 {
1389 catchsql { UPDATE self SET b = 15 }
1390 } {1 {FOREIGN KEY constraint failed}}
1392 do_test fkey2-16.1.$tn.4 {
1393 catchsql { UPDATE self SET a = 15 }
1394 } {1 {FOREIGN KEY constraint failed}}
1396 do_test fkey2-16.1.$tn.5 {
1397 catchsql { UPDATE self SET a = 15, b = 16 }
1398 } {1 {FOREIGN KEY constraint failed}}
1400 do_test fkey2-16.1.$tn.6 {
1401 catchsql { UPDATE self SET a = 17, b = 17 }
1402 } {0 {}}
1404 do_test fkey2-16.1.$tn.7 {
1405 execsql { DELETE FROM self }
1406 } {}
1407 do_test fkey2-16.1.$tn.8 {
1408 catchsql { INSERT INTO self VALUES(20, 21) }
1409 } {1 {FOREIGN KEY constraint failed}}
1410 }
1412 #-------------------------------------------------------------------------
1413 # This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes"
1414 # is turned on statements that violate immediate FK constraints return
1415 # SQLITE_CONSTRAINT immediately, not after returning a number of rows.
1416 # Whereas statements that violate deferred FK constraints return the number
1417 # of rows before failing.
1418 #
1419 # Also test that rows modified by FK actions are not counted in either the
1420 # returned row count or the values returned by sqlite3_changes(). Like
1421 # trigger related changes, they are included in sqlite3_total_changes() though.
1422 #
1423 drop_all_tables
1424 do_test fkey2-17.1.1 {
1425 execsql { PRAGMA count_changes = 1 }
1426 execsql {
1427 CREATE TABLE one(a, b, c, UNIQUE(b, c));
1428 CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c));
1429 INSERT INTO one VALUES(1, 2, 3);
1430 }
1431 } {1}
1432 do_test fkey2-17.1.2 {
1433 set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy]
1434 sqlite3_step $STMT
1435 } {SQLITE_CONSTRAINT}
1436 verify_ex_errcode fkey2-17.1.2b SQLITE_CONSTRAINT_FOREIGNKEY
1437 ifcapable autoreset {
1438 do_test fkey2-17.1.3 {
1439 sqlite3_step $STMT
1440 } {SQLITE_CONSTRAINT}
1441 verify_ex_errcode fkey2-17.1.3b SQLITE_CONSTRAINT_FOREIGNKEY
1442 } else {
1443 do_test fkey2-17.1.3 {
1444 sqlite3_step $STMT
1445 } {SQLITE_MISUSE}
1446 }
1447 do_test fkey2-17.1.4 {
1448 sqlite3_finalize $STMT
1449 } {SQLITE_CONSTRAINT}
1450 verify_ex_errcode fkey2-17.1.4b SQLITE_CONSTRAINT_FOREIGNKEY
1451 do_test fkey2-17.1.5 {
1452 execsql {
1453 INSERT INTO one VALUES(2, 3, 4);
1454 INSERT INTO one VALUES(3, 4, 5);
1455 INSERT INTO two VALUES(1, 2, 3);
1456 INSERT INTO two VALUES(2, 3, 4);
1457 INSERT INTO two VALUES(3, 4, 5);
1458 }
1459 } {1 1 1 1 1}
1460 do_test fkey2-17.1.6 {
1461 catchsql {
1462 BEGIN;
1463 INSERT INTO one VALUES(0, 0, 0);
1464 UPDATE two SET e=e+1, f=f+1;
1465 }
1466 } {1 {FOREIGN KEY constraint failed}}
1467 do_test fkey2-17.1.7 {
1468 execsql { SELECT * FROM one }
1469 } {1 2 3 2 3 4 3 4 5 0 0 0}
1470 do_test fkey2-17.1.8 {
1471 execsql { SELECT * FROM two }
1472 } {1 2 3 2 3 4 3 4 5}
1473 do_test fkey2-17.1.9 {
1474 execsql COMMIT
1475 } {}
1476 do_test fkey2-17.1.10 {
1477 execsql {
1478 CREATE TABLE three(
1479 g, h, i,
1480 FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED
1481 );
1482 }
1483 } {}
1484 do_test fkey2-17.1.11 {
1485 set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy]
1486 sqlite3_step $STMT
1487 } {SQLITE_ROW}
1488 do_test fkey2-17.1.12 {
1489 sqlite3_column_text $STMT 0
1490 } {1}
1491 do_test fkey2-17.1.13 {
1492 sqlite3_step $STMT
1493 } {SQLITE_CONSTRAINT}
1494 verify_ex_errcode fkey2-17.1.13b SQLITE_CONSTRAINT_FOREIGNKEY
1495 do_test fkey2-17.1.14 {
1496 sqlite3_finalize $STMT
1497 } {SQLITE_CONSTRAINT}
1498 verify_ex_errcode fkey2-17.1.14b SQLITE_CONSTRAINT_FOREIGNKEY
1500 drop_all_tables
1501 do_test fkey2-17.2.1 {
1502 execsql {
1503 CREATE TABLE high("a'b!" PRIMARY KEY, b);
1504 CREATE TABLE low(
1505 c,
1506 "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE
1507 );
1508 }
1509 } {}
1510 do_test fkey2-17.2.2 {
1511 execsql {
1512 INSERT INTO high VALUES('a', 'b');
1513 INSERT INTO low VALUES('b', 'a');
1514 }
1515 db changes
1516 } {1}
1517 set nTotal [db total_changes]
1518 do_test fkey2-17.2.3 {
1519 execsql { UPDATE high SET "a'b!" = 'c' }
1520 } {1}
1521 do_test fkey2-17.2.4 {
1522 db changes
1523 } {1}
1524 do_test fkey2-17.2.5 {
1525 expr [db total_changes] - $nTotal
1526 } {2}
1527 do_test fkey2-17.2.6 {
1528 execsql { SELECT * FROM high ; SELECT * FROM low }
1529 } {c b b c}
1530 do_test fkey2-17.2.7 {
1531 execsql { DELETE FROM high }
1532 } {1}
1533 do_test fkey2-17.2.8 {
1534 db changes
1535 } {1}
1536 do_test fkey2-17.2.9 {
1537 expr [db total_changes] - $nTotal
1538 } {4}
1539 do_test fkey2-17.2.10 {
1540 execsql { SELECT * FROM high ; SELECT * FROM low }
1541 } {}
1542 execsql { PRAGMA count_changes = 0 }
1544 #-------------------------------------------------------------------------
1545 # Test that the authorization callback works.
1546 #
1548 ifcapable auth {
1549 do_test fkey2-18.1 {
1550 execsql {
1551 CREATE TABLE long(a, b PRIMARY KEY, c);
1552 CREATE TABLE short(d, e, f REFERENCES long);
1553 CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED);
1554 }
1555 } {}
1557 proc auth {args} {eval lappend ::authargs [lrange $args 0 4]; return SQLITE_OK}
1558 db auth auth
1560 # An insert on the parent table must read the child key of any deferred
1561 # foreign key constraints. But not the child key of immediate constraints.
1562 set authargs {}
1563 do_test fkey2-18.2 {
1564 execsql { INSERT INTO long VALUES(1, 2, 3) }
1565 set authargs
1566 } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}}
1568 # An insert on the child table of an immediate constraint must read the
1569 # parent key columns (to see if it is a violation or not).
1570 set authargs {}
1571 do_test fkey2-18.3 {
1572 execsql { INSERT INTO short VALUES(1, 3, 2) }
1573 set authargs
1574 } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}}
1576 # As must an insert on the child table of a deferred constraint.
1577 set authargs {}
1578 do_test fkey2-18.4 {
1579 execsql { INSERT INTO mid VALUES(1, 3, 2) }
1580 set authargs
1581 } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}}
1583 do_test fkey2-18.5 {
1584 execsql {
1585 CREATE TABLE nought(a, b PRIMARY KEY, c);
1586 CREATE TABLE cross(d, e, f,
1587 FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE
1588 );
1589 }
1590 execsql { INSERT INTO nought VALUES(2, 1, 2) }
1591 execsql { INSERT INTO cross VALUES(0, 1, 0) }
1592 set authargs [list]
1593 execsql { UPDATE nought SET b = 5 }
1594 set authargs
1595 } {SQLITE_UPDATE nought b main {} SQLITE_READ cross e main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_UPDATE cross e main {} SQLITE_READ nought b main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {}}
1597 do_test fkey2-18.6 {
1598 execsql {SELECT * FROM cross}
1599 } {0 5 0}
1601 do_test fkey2-18.7 {
1602 execsql {
1603 CREATE TABLE one(a INTEGER PRIMARY KEY, b);
1604 CREATE TABLE two(b, c REFERENCES one);
1605 INSERT INTO one VALUES(101, 102);
1606 }
1607 set authargs [list]
1608 execsql { INSERT INTO two VALUES(100, 101); }
1609 set authargs
1610 } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}}
1612 # Return SQLITE_IGNORE to requests to read from the parent table. This
1613 # causes inserts of non-NULL keys into the child table to fail.
1614 #
1615 rename auth {}
1616 proc auth {args} {
1617 if {[lindex $args 1] == "long"} {return SQLITE_IGNORE}
1618 return SQLITE_OK
1619 }
1620 do_test fkey2-18.8 {
1621 catchsql { INSERT INTO short VALUES(1, 3, 2) }
1622 } {1 {FOREIGN KEY constraint failed}}
1623 do_test fkey2-18.9 {
1624 execsql { INSERT INTO short VALUES(1, 3, NULL) }
1625 } {}
1626 do_test fkey2-18.10 {
1627 execsql { SELECT * FROM short }
1628 } {1 3 2 1 3 {}}
1629 do_test fkey2-18.11 {
1630 catchsql { UPDATE short SET f = 2 WHERE f IS NULL }
1631 } {1 {FOREIGN KEY constraint failed}}
1633 db auth {}
1634 unset authargs
1635 }
1638 do_test fkey2-19.1 {
1639 execsql {
1640 CREATE TABLE main(id INTEGER PRIMARY KEY);
1641 CREATE TABLE sub(id INT REFERENCES main(id));
1642 INSERT INTO main VALUES(1);
1643 INSERT INTO main VALUES(2);
1644 INSERT INTO sub VALUES(2);
1645 }
1646 } {}
1647 do_test fkey2-19.2 {
1648 set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy]
1649 sqlite3_bind_int $S 1 2
1650 sqlite3_step $S
1651 } {SQLITE_CONSTRAINT}
1652 verify_ex_errcode fkey2-19.2b SQLITE_CONSTRAINT_FOREIGNKEY
1653 do_test fkey2-19.3 {
1654 sqlite3_reset $S
1655 } {SQLITE_CONSTRAINT}
1656 verify_ex_errcode fkey2-19.3b SQLITE_CONSTRAINT_FOREIGNKEY
1657 do_test fkey2-19.4 {
1658 sqlite3_bind_int $S 1 1
1659 sqlite3_step $S
1660 } {SQLITE_DONE}
1661 do_test fkey2-19.4 {
1662 sqlite3_finalize $S
1663 } {SQLITE_OK}
1665 drop_all_tables
1666 do_test fkey2-20.1 {
1667 execsql {
1668 CREATE TABLE pp(a PRIMARY KEY, b);
1669 CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp);
1670 }
1671 } {}
1673 foreach {tn insert} {
1674 1 "INSERT"
1675 2 "INSERT OR IGNORE"
1676 3 "INSERT OR ABORT"
1677 4 "INSERT OR ROLLBACK"
1678 5 "INSERT OR REPLACE"
1679 6 "INSERT OR FAIL"
1680 } {
1681 do_test fkey2-20.2.$tn.1 {
1682 catchsql "$insert INTO cc VALUES(1, 2)"
1683 } {1 {FOREIGN KEY constraint failed}}
1684 do_test fkey2-20.2.$tn.2 {
1685 execsql { SELECT * FROM cc }
1686 } {}
1687 do_test fkey2-20.2.$tn.3 {
1688 execsql {
1689 BEGIN;
1690 INSERT INTO pp VALUES(2, 'two');
1691 INSERT INTO cc VALUES(1, 2);
1692 }
1693 catchsql "$insert INTO cc VALUES(3, 4)"
1694 } {1 {FOREIGN KEY constraint failed}}
1695 do_test fkey2-20.2.$tn.4 {
1696 execsql { COMMIT ; SELECT * FROM cc }
1697 } {1 2}
1698 do_test fkey2-20.2.$tn.5 {
1699 execsql { DELETE FROM cc ; DELETE FROM pp }
1700 } {}
1701 }
1703 foreach {tn update} {
1704 1 "UPDATE"
1705 2 "UPDATE OR IGNORE"
1706 3 "UPDATE OR ABORT"
1707 4 "UPDATE OR ROLLBACK"
1708 5 "UPDATE OR REPLACE"
1709 6 "UPDATE OR FAIL"
1710 } {
1711 do_test fkey2-20.3.$tn.1 {
1712 execsql {
1713 INSERT INTO pp VALUES(2, 'two');
1714 INSERT INTO cc VALUES(1, 2);
1715 }
1716 } {}
1717 do_test fkey2-20.3.$tn.2 {
1718 catchsql "$update pp SET a = 1"
1719 } {1 {FOREIGN KEY constraint failed}}
1720 do_test fkey2-20.3.$tn.3 {
1721 execsql { SELECT * FROM pp }
1722 } {2 two}
1723 do_test fkey2-20.3.$tn.4 {
1724 catchsql "$update cc SET d = 1"
1725 } {1 {FOREIGN KEY constraint failed}}
1726 do_test fkey2-20.3.$tn.5 {
1727 execsql { SELECT * FROM cc }
1728 } {1 2}
1729 do_test fkey2-20.3.$tn.6 {
1730 execsql {
1731 BEGIN;
1732 INSERT INTO pp VALUES(3, 'three');
1733 }
1734 catchsql "$update pp SET a = 1 WHERE a = 2"
1735 } {1 {FOREIGN KEY constraint failed}}
1736 do_test fkey2-20.3.$tn.7 {
1737 execsql { COMMIT ; SELECT * FROM pp }
1738 } {2 two 3 three}
1739 do_test fkey2-20.3.$tn.8 {
1740 execsql {
1741 BEGIN;
1742 INSERT INTO cc VALUES(2, 2);
1743 }
1744 catchsql "$update cc SET d = 1 WHERE c = 1"
1745 } {1 {FOREIGN KEY constraint failed}}
1746 do_test fkey2-20.3.$tn.9 {
1747 execsql { COMMIT ; SELECT * FROM cc }
1748 } {1 2 2 2}
1749 do_test fkey2-20.3.$tn.10 {
1750 execsql { DELETE FROM cc ; DELETE FROM pp }
1751 } {}
1752 }
1754 #-------------------------------------------------------------------------
1755 # The following block of tests, those prefixed with "fkey2-genfkey.", are
1756 # the same tests that were used to test the ".genfkey" command provided
1757 # by the shell tool. So these tests show that the built-in foreign key
1758 # implementation is more or less compatible with the triggers generated
1759 # by genfkey.
1760 #
1761 drop_all_tables
1762 do_test fkey2-genfkey.1.1 {
1763 execsql {
1764 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
1765 CREATE TABLE t2(e REFERENCES t1, f);
1766 CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
1767 }
1768 } {}
1769 do_test fkey2-genfkey.1.2 {
1770 catchsql { INSERT INTO t2 VALUES(1, 2) }
1771 } {1 {FOREIGN KEY constraint failed}}
1772 do_test fkey2-genfkey.1.3 {
1773 execsql {
1774 INSERT INTO t1 VALUES(1, 2, 3);
1775 INSERT INTO t2 VALUES(1, 2);
1776 }
1777 } {}
1778 do_test fkey2-genfkey.1.4 {
1779 execsql { INSERT INTO t2 VALUES(NULL, 3) }
1780 } {}
1781 do_test fkey2-genfkey.1.5 {
1782 catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
1783 } {1 {FOREIGN KEY constraint failed}}
1784 do_test fkey2-genfkey.1.6 {
1785 execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
1786 } {}
1787 do_test fkey2-genfkey.1.7 {
1788 execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
1789 } {}
1790 do_test fkey2-genfkey.1.8 {
1791 catchsql { UPDATE t1 SET a = 10 }
1792 } {1 {FOREIGN KEY constraint failed}}
1793 do_test fkey2-genfkey.1.9 {
1794 catchsql { UPDATE t1 SET a = NULL }
1795 } {1 {datatype mismatch}}
1796 do_test fkey2-genfkey.1.10 {
1797 catchsql { DELETE FROM t1 }
1798 } {1 {FOREIGN KEY constraint failed}}
1799 do_test fkey2-genfkey.1.11 {
1800 execsql { UPDATE t2 SET e = NULL }
1801 } {}
1802 do_test fkey2-genfkey.1.12 {
1803 execsql {
1804 UPDATE t1 SET a = 10;
1805 DELETE FROM t1;
1806 DELETE FROM t2;
1807 }
1808 } {}
1809 do_test fkey2-genfkey.1.13 {
1810 execsql {
1811 INSERT INTO t3 VALUES(1, NULL, NULL);
1812 INSERT INTO t3 VALUES(1, 2, NULL);
1813 INSERT INTO t3 VALUES(1, NULL, 3);
1814 }
1815 } {}
1816 do_test fkey2-genfkey.1.14 {
1817 catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
1818 } {1 {FOREIGN KEY constraint failed}}
1819 do_test fkey2-genfkey.1.15 {
1820 execsql {
1821 INSERT INTO t1 VALUES(1, 1, 4);
1822 INSERT INTO t3 VALUES(3, 1, 4);
1823 }
1824 } {}
1825 do_test fkey2-genfkey.1.16 {
1826 catchsql { DELETE FROM t1 }
1827 } {1 {FOREIGN KEY constraint failed}}
1828 do_test fkey2-genfkey.1.17 {
1829 catchsql { UPDATE t1 SET b = 10}
1830 } {1 {FOREIGN KEY constraint failed}}
1831 do_test fkey2-genfkey.1.18 {
1832 execsql { UPDATE t1 SET a = 10}
1833 } {}
1834 do_test fkey2-genfkey.1.19 {
1835 catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
1836 } {1 {FOREIGN KEY constraint failed}}
1838 drop_all_tables
1839 do_test fkey2-genfkey.2.1 {
1840 execsql {
1841 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
1842 CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
1843 CREATE TABLE t3(g, h, i,
1844 FOREIGN KEY (h, i)
1845 REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
1846 );
1847 }
1848 } {}
1849 do_test fkey2-genfkey.2.2 {
1850 execsql {
1851 INSERT INTO t1 VALUES(1, 2, 3);
1852 INSERT INTO t1 VALUES(4, 5, 6);
1853 INSERT INTO t2 VALUES(1, 'one');
1854 INSERT INTO t2 VALUES(4, 'four');
1855 }
1856 } {}
1857 do_test fkey2-genfkey.2.3 {
1858 execsql {
1859 UPDATE t1 SET a = 2 WHERE a = 1;
1860 SELECT * FROM t2;
1861 }
1862 } {2 one 4 four}
1863 do_test fkey2-genfkey.2.4 {
1864 execsql {
1865 DELETE FROM t1 WHERE a = 4;
1866 SELECT * FROM t2;
1867 }
1868 } {2 one}
1870 do_test fkey2-genfkey.2.5 {
1871 execsql {
1872 INSERT INTO t3 VALUES('hello', 2, 3);
1873 UPDATE t1 SET c = 2;
1874 SELECT * FROM t3;
1875 }
1876 } {hello 2 2}
1877 do_test fkey2-genfkey.2.6 {
1878 execsql {
1879 DELETE FROM t1;
1880 SELECT * FROM t3;
1881 }
1882 } {}
1884 drop_all_tables
1885 do_test fkey2-genfkey.3.1 {
1886 execsql {
1887 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));
1888 CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
1889 CREATE TABLE t3(g, h, i,
1890 FOREIGN KEY (h, i)
1891 REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
1892 );
1893 }
1894 } {}
1895 do_test fkey2-genfkey.3.2 {
1896 execsql {
1897 INSERT INTO t1 VALUES(1, 2, 3);
1898 INSERT INTO t1 VALUES(4, 5, 6);
1899 INSERT INTO t2 VALUES(1, 'one');
1900 INSERT INTO t2 VALUES(4, 'four');
1901 }
1902 } {}
1903 do_test fkey2-genfkey.3.3 {
1904 execsql {
1905 UPDATE t1 SET a = 2 WHERE a = 1;
1906 SELECT * FROM t2;
1907 }
1908 } {{} one 4 four}
1909 do_test fkey2-genfkey.3.4 {
1910 execsql {
1911 DELETE FROM t1 WHERE a = 4;
1912 SELECT * FROM t2;
1913 }
1914 } {{} one {} four}
1915 do_test fkey2-genfkey.3.5 {
1916 execsql {
1917 INSERT INTO t3 VALUES('hello', 2, 3);
1918 UPDATE t1 SET c = 2;
1919 SELECT * FROM t3;
1920 }
1921 } {hello {} {}}
1922 do_test fkey2-genfkey.3.6 {
1923 execsql {
1924 UPDATE t3 SET h = 2, i = 2;
1925 DELETE FROM t1;
1926 SELECT * FROM t3;
1927 }
1928 } {hello {} {}}
1930 #-------------------------------------------------------------------------
1931 # Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been
1932 # fixed.
1933 #
1934 do_test fkey2-dd08e5.1.1 {
1935 execsql {
1936 PRAGMA foreign_keys=ON;
1937 CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b);
1938 CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
1939 INSERT INTO tdd08 VALUES(200,300);
1941 CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b));
1942 INSERT INTO tdd08_b VALUES(100,200,300);
1943 }
1944 } {}
1945 do_test fkey2-dd08e5.1.2 {
1946 catchsql {
1947 DELETE FROM tdd08;
1948 }
1949 } {1 {FOREIGN KEY constraint failed}}
1950 do_test fkey2-dd08e5.1.3 {
1951 execsql {
1952 SELECT * FROM tdd08;
1953 }
1954 } {200 300}
1955 do_test fkey2-dd08e5.1.4 {
1956 catchsql {
1957 INSERT INTO tdd08_b VALUES(400,500,300);
1958 }
1959 } {1 {FOREIGN KEY constraint failed}}
1960 do_test fkey2-dd08e5.1.5 {
1961 catchsql {
1962 UPDATE tdd08_b SET x=x+1;
1963 }
1964 } {1 {FOREIGN KEY constraint failed}}
1965 do_test fkey2-dd08e5.1.6 {
1966 catchsql {
1967 UPDATE tdd08 SET a=a+1;
1968 }
1969 } {1 {FOREIGN KEY constraint failed}}
1971 #-------------------------------------------------------------------------
1972 # Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
1973 # fixed.
1974 #
1975 do_test fkey2-ce7c13.1.1 {
1976 execsql {
1977 CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
1978 CREATE UNIQUE INDEX ice71 ON tce71(a,b);
1979 INSERT INTO tce71 VALUES(100,200);
1980 CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
1981 INSERT INTO tce72 VALUES(300,100,200);
1982 UPDATE tce71 set b = 200 where a = 100;
1983 SELECT * FROM tce71, tce72;
1984 }
1985 } {100 200 300 100 200}
1986 do_test fkey2-ce7c13.1.2 {
1987 catchsql {
1988 UPDATE tce71 set b = 201 where a = 100;
1989 }
1990 } {1 {FOREIGN KEY constraint failed}}
1991 do_test fkey2-ce7c13.1.3 {
1992 catchsql {
1993 UPDATE tce71 set a = 101 where a = 100;
1994 }
1995 } {1 {FOREIGN KEY constraint failed}}
1996 do_test fkey2-ce7c13.1.4 {
1997 execsql {
1998 CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
1999 INSERT INTO tce73 VALUES(100,200);
2000 CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
2001 INSERT INTO tce74 VALUES(300,100,200);
2002 UPDATE tce73 set b = 200 where a = 100;
2003 SELECT * FROM tce73, tce74;
2004 }
2005 } {100 200 300 100 200}
2006 do_test fkey2-ce7c13.1.5 {
2007 catchsql {
2008 UPDATE tce73 set b = 201 where a = 100;
2009 }
2010 } {1 {FOREIGN KEY constraint failed}}
2011 do_test fkey2-ce7c13.1.6 {
2012 catchsql {
2013 UPDATE tce73 set a = 101 where a = 100;
2014 }
2015 } {1 {FOREIGN KEY constraint failed}}
2017 finish_test