Annotation of embedaddon/sqlite3/test/fkey2.test, revision 1.1.1.1
1.1 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to fkey2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test