xref: /sqlite-3.40.0/test/fkey6.test (revision e4bf4f08) 
1# 2013-07-11
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 tests the PRAGMA defer_foreign_keys and
14# SQLITE_DBSTATUS_DEFERRED_FKS
15#
16# EVIDENCE-OF: R-18981-16292 When the defer_foreign_keys PRAGMA is on,
17# enforcement of all foreign key constraints is delayed until the
18# outermost transaction is committed.
19#
20# EVIDENCE-OF: R-28911-57501 The defer_foreign_keys pragma defaults to
21# OFF so that foreign key constraints are only deferred if they are
22# created as "DEFERRABLE INITIALLY DEFERRED".
23
24set testdir [file dirname $argv0]
25source $testdir/tester.tcl
26
27ifcapable {!foreignkey} {
28  finish_test
29  return
30}
31
32do_execsql_test fkey6-1.0 {
33  PRAGMA defer_foreign_keys;
34} {0}
35
36do_execsql_test fkey6-1.1 {
37  PRAGMA foreign_keys=ON;
38  CREATE TABLE t1(x INTEGER PRIMARY KEY);
39  CREATE TABLE t2(y INTEGER PRIMARY KEY,
40          z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED);
41  CREATE INDEX t2z ON t2(z);
42  CREATE TABLE t3(u INTEGER PRIMARY KEY, v INTEGER REFERENCES t1(x));
43  CREATE INDEX t3v ON t3(v);
44  INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
45  INSERT INTO t2 VALUES(1,1),(2,2);
46  INSERT INTO t3 VALUES(3,3),(4,4);
47} {}
48do_test fkey6-1.2 {
49  catchsql {DELETE FROM t1 WHERE x=2;}
50} {1 {foreign key constraint failed}}
51do_test fkey6-1.3 {
52  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
53} {0 0 0}
54do_test fkey6-1.4 {
55  execsql {
56    BEGIN;
57    DELETE FROM t1 WHERE x=1;
58  }
59} {}
60do_test fkey6-1.5.1 {
61  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 1
62} {0 1 0}
63do_test fkey6-1.5.2 {
64  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
65} {0 1 0}
66do_test fkey6-1.6 {
67  execsql {
68    ROLLBACK;
69  }
70} {}
71do_test fkey6-1.7 {
72  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
73} {0 0 0}
74do_test fkey6-1.8 {
75  execsql {
76    PRAGMA defer_foreign_keys=ON;
77    BEGIN;
78    DELETE FROM t1 WHERE x=3;
79  }
80} {}
81do_test fkey6-1.9 {
82  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
83} {0 1 0}
84do_test fkey6-1.10 {
85  execsql {
86    ROLLBACK;
87    PRAGMA defer_foreign_keys=OFF;
88    BEGIN;
89  }
90  catchsql {DELETE FROM t1 WHERE x=3}
91} {1 {foreign key constraint failed}}
92db eval {ROLLBACK}
93
94do_test fkey6-1.20 {
95  execsql {
96    BEGIN;
97    DELETE FROM t1 WHERE x=1;
98  }
99  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
100} {0 1 0}
101do_test fkey6-1.21 {
102  execsql {
103    DELETE FROM t2 WHERE y=1;
104  }
105  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
106} {0 0 0}
107do_test fkey6-1.22 {
108  execsql {
109    COMMIT;
110  }
111} {}
112
113
114finish_test
115