wasCSharpSQLite – Blame information for rev 1

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