xref: /sqlite-3.40.0/test/fkey7.test (revision f116ad85) 
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
16set testdir [file dirname $argv0]
17source $testdir/tester.tcl
18set testprefix fkey7
19
20ifcapable {!foreignkey} {
21  finish_test
22  return
23}
24
25do_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
36proc auth {op tbl args} {
37  if {$op == "SQLITE_READ"} { set ::tbls($tbl) 1 }
38  return "SQLITE_OK"
39}
40db auth auth
41db cache size 0
42proc 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
48do_tblsread_test 1.2 { UPDATE par SET b=? WHERE a=? } {par s1}
49do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par}
50do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par}
51do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1}
52
53ifcapable 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
71ifcapable 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
86do_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
96do_catchsql_test 4.1 {
97  INSERT OR FAIL INTO child VALUES(123), (123);
98} {1 {FOREIGN KEY constraint failed}}
99
100do_execsql_test 4.2 {
101  SELECT * FROM child;
102} {}
103
104do_execsql_test 4.3 {
105  PRAGMA foreign_key_check;
106} {}
107
108do_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
113do_execsql_test 4.5 {
114  SELECT * FROM child;
115} {123}
116
117do_execsql_test 4.6 {
118  PRAGMA foreign_key_check;
119} {}
120
121finish_test
122