xref: /sqlite-3.40.0/test/fkey6.test (revision 7aa3ebee) 
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}
84
85# EVIDENCE-OF: R-21752-26913 The defer_foreign_keys pragma is
86# automatically switched off at each COMMIT or ROLLBACK. Hence, the
87# defer_foreign_keys pragma must be separately enabled for each
88# transaction.
89do_execsql_test fkey6-1.10.1 {
90  PRAGMA defer_foreign_keys;
91  ROLLBACK;
92  PRAGMA defer_foreign_keys;
93  BEGIN;
94  PRAGMA defer_foreign_keys=ON;
95  PRAGMA defer_foreign_keys;
96  COMMIT;
97  PRAGMA defer_foreign_keys;
98  BEGIN;
99} {1 0 1 0}
100do_test fkey6-1.10.2 {
101  catchsql {DELETE FROM t1 WHERE x=3}
102} {1 {FOREIGN KEY constraint failed}}
103db eval {ROLLBACK}
104
105do_test fkey6-1.20 {
106  execsql {
107    BEGIN;
108    DELETE FROM t1 WHERE x=1;
109  }
110  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
111} {0 1 0}
112do_test fkey6-1.21 {
113  execsql {
114    DELETE FROM t2 WHERE y=1;
115  }
116  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
117} {0 0 0}
118do_test fkey6-1.22 {
119  execsql {
120    COMMIT;
121  }
122} {}
123
124do_execsql_test fkey6-2.1 {
125  CREATE TABLE p1(a PRIMARY KEY);
126  INSERT INTO p1 VALUES('one'), ('two');
127  CREATE TABLE c1(x REFERENCES p1);
128  INSERT INTO c1 VALUES('two'), ('one');
129}
130
131do_execsql_test fkey6-2.2 {
132  BEGIN;
133    PRAGMA defer_foreign_keys = 1;
134    DELETE FROM p1;
135  ROLLBACK;
136  PRAGMA defer_foreign_keys;
137} {0}
138
139do_execsql_test fkey6-2.3 {
140  BEGIN;
141    PRAGMA defer_foreign_keys = 1;
142    DROP TABLE p1;
143    PRAGMA vdbe_trace = 0;
144  ROLLBACK;
145  PRAGMA defer_foreign_keys;
146} {0}
147
148do_execsql_test fkey6-2.4 {
149  BEGIN;
150    PRAGMA defer_foreign_keys = 1;
151    DELETE FROM p1;
152    DROP TABLE c1;
153  COMMIT;
154  PRAGMA defer_foreign_keys;
155} {0}
156
157do_execsql_test fkey6-2.5 {
158  DROP TABLE p1;
159  CREATE TABLE p1(a PRIMARY KEY);
160  INSERT INTO p1 VALUES('one'), ('two');
161  CREATE TABLE c1(x REFERENCES p1);
162  INSERT INTO c1 VALUES('two'), ('one');
163}
164
165do_execsql_test fkey6-2.6 {
166  BEGIN;
167    PRAGMA defer_foreign_keys = 1;
168    INSERT INTO c1 VALUES('three');
169    DROP TABLE c1;
170  COMMIT;
171  PRAGMA defer_foreign_keys;
172} {0}
173
174
175finish_test
176