Debian Codegraph @

test/fkey7.test

raw
1 # 2001 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 set testprefix fkey7
19
20 ifcapable {!foreignkey} {
21 finish_test
22 return
23 }
24
25 do_execsql_test 1.1 {
26 PRAGMA foreign_keys = 1;
27
28 CREATE TABLE s1(a PRIMARY KEY, b);
29 CREATE TABLE par(a, b REFERENCES s1, c UNIQUE, PRIMARY KEY(a));
30
31 CREATE TABLE c1(a, b REFERENCES par);
32 CREATE TABLE c2(a, b REFERENCES par);
33 CREATE TABLE c3(a, b REFERENCES par(c));
34 }
35
36 proc auth {op tbl args} {
37 if {$op == "SQLITE_READ"} { set ::tbls($tbl) 1 }
38 return "SQLITE_OK"
39 }
40 db auth auth
41 db cache size 0
42 proc do_tblsread_test {tn sql tbllist} {
43 array unset ::tbls
44 uplevel [list execsql $sql]
45 uplevel [list do_test $tn {lsort [array names ::tbls]} $tbllist]
46 }
47
48 do_tblsread_test 1.2 { UPDATE par SET b=? WHERE a=? } {par s1}
49 do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par}
50 do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par}
51 do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1}
52
53 ifcapable incrblob {
54 do_execsql_test 2.0 {
55 CREATE TABLE pX(x PRIMARY KEY);
56 CREATE TABLE cX(a INTEGER PRIMARY KEY, b REFERENCES pX);
57 }
58
59 do_catchsql_test 2.1 {
60 INSERT INTO cX VALUES(11, zeroblob(40));
61 } {1 {FOREIGN KEY constraint failed}}
62
63 do_test 2.2 {
64 set stmt [sqlite3_prepare_v2 db "INSERT INTO cX VALUES(11, ?)" -1]
65 sqlite3_bind_zeroblob $stmt 1 45
66 sqlite3_step $stmt
67 sqlite3_finalize $stmt
68 } {SQLITE_CONSTRAINT}
69 }
70
71 ifcapable stat4 {
72 do_execsql_test 3.0 {
73 CREATE TABLE p4 (id INTEGER NOT NULL PRIMARY KEY);
74 INSERT INTO p4 VALUES(1), (2), (3);
75
76 CREATE TABLE c4(x INTEGER REFERENCES p4(id) DEFERRABLE INITIALLY DEFERRED);
77 CREATE INDEX c4_x ON c4(x);
78 INSERT INTO c4 VALUES(1), (2), (3);
79
80 ANALYZE;
81 INSERT INTO p4(id) VALUES(4);
82 }
83 }
84
85
86 do_execsql_test 4.0 {
87 PRAGMA foreign_keys = true;
88 CREATE TABLE parent(
89 p PRIMARY KEY
90 );
91 CREATE TABLE child(
92 c UNIQUE REFERENCES parent(p)
93 );
94 }
95
96 do_catchsql_test 4.1 {
97 INSERT OR FAIL INTO child VALUES(123), (123);
98 } {1 {FOREIGN KEY constraint failed}}
99
100 do_execsql_test 4.2 {
101 SELECT * FROM child;
102 } {}
103
104 do_execsql_test 4.3 {
105 PRAGMA foreign_key_check;
106 } {}
107
108 do_catchsql_test 4.4 {
109 INSERT INTO parent VALUES(123);
110 INSERT OR FAIL INTO child VALUES(123), (123);
111 } {1 {UNIQUE constraint failed: child.c}}
112
113 do_execsql_test 4.5 {
114 SELECT * FROM child;
115 } {123}
116
117 do_execsql_test 4.6 {
118 PRAGMA foreign_key_check;
119 } {}
120
121 finish_test