xref: /sqlite-3.40.0/test/fkey6.test (revision dfe4e6bb) 
1# 2012 December 17
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
26set testprefix fkey6
27
28ifcapable {!foreignkey} {
29  finish_test
30  return
31}
32
33do_execsql_test fkey6-1.0 {
34  PRAGMA defer_foreign_keys;
35} {0}
36
37do_execsql_test fkey6-1.1 {
38  PRAGMA foreign_keys=ON;
39  CREATE TABLE t1(x INTEGER PRIMARY KEY);
40  CREATE TABLE t2(y INTEGER PRIMARY KEY,
41          z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED);
42  CREATE INDEX t2z ON t2(z);
43  CREATE TABLE t3(u INTEGER PRIMARY KEY, v INTEGER REFERENCES t1(x));
44  CREATE INDEX t3v ON t3(v);
45  INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
46  INSERT INTO t2 VALUES(1,1),(2,2);
47  INSERT INTO t3 VALUES(3,3),(4,4);
48} {}
49do_test fkey6-1.2 {
50  catchsql {DELETE FROM t1 WHERE x=2;}
51} {1 {FOREIGN KEY constraint failed}}
52do_test fkey6-1.3 {
53  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
54} {0 0 0}
55do_test fkey6-1.4 {
56  execsql {
57    BEGIN;
58    DELETE FROM t1 WHERE x=1;
59  }
60} {}
61do_test fkey6-1.5.1 {
62  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 1
63} {0 1 0}
64do_test fkey6-1.5.2 {
65  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
66} {0 1 0}
67do_test fkey6-1.6 {
68  execsql {
69    ROLLBACK;
70  }
71} {}
72do_test fkey6-1.7 {
73  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
74} {0 0 0}
75do_test fkey6-1.8 {
76  execsql {
77    PRAGMA defer_foreign_keys=ON;
78    BEGIN;
79    DELETE FROM t1 WHERE x=3;
80  }
81} {}
82do_test fkey6-1.9 {
83  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
84} {0 1 0}
85
86# EVIDENCE-OF: R-21752-26913 The defer_foreign_keys pragma is
87# automatically switched off at each COMMIT or ROLLBACK. Hence, the
88# defer_foreign_keys pragma must be separately enabled for each
89# transaction.
90do_execsql_test fkey6-1.10.1 {
91  PRAGMA defer_foreign_keys;
92  ROLLBACK;
93  PRAGMA defer_foreign_keys;
94  BEGIN;
95  PRAGMA defer_foreign_keys=ON;
96  PRAGMA defer_foreign_keys;
97  COMMIT;
98  PRAGMA defer_foreign_keys;
99  BEGIN;
100} {1 0 1 0}
101do_test fkey6-1.10.2 {
102  catchsql {DELETE FROM t1 WHERE x=3}
103} {1 {FOREIGN KEY constraint failed}}
104db eval {ROLLBACK}
105
106do_test fkey6-1.20 {
107  execsql {
108    BEGIN;
109    DELETE FROM t1 WHERE x=1;
110  }
111  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
112} {0 1 0}
113do_test fkey6-1.21 {
114  execsql {
115    DELETE FROM t2 WHERE y=1;
116  }
117  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
118} {0 0 0}
119do_test fkey6-1.22 {
120  execsql {
121    COMMIT;
122  }
123} {}
124
125do_execsql_test fkey6-2.1 {
126  CREATE TABLE p1(a PRIMARY KEY);
127  INSERT INTO p1 VALUES('one'), ('two');
128  CREATE TABLE c1(x REFERENCES p1);
129  INSERT INTO c1 VALUES('two'), ('one');
130}
131
132do_execsql_test fkey6-2.2 {
133  BEGIN;
134    PRAGMA defer_foreign_keys = 1;
135    DELETE FROM p1;
136  ROLLBACK;
137  PRAGMA defer_foreign_keys;
138} {0}
139
140do_execsql_test fkey6-2.3 {
141  BEGIN;
142    PRAGMA defer_foreign_keys = 1;
143    DROP TABLE p1;
144    PRAGMA vdbe_trace = 0;
145  ROLLBACK;
146  PRAGMA defer_foreign_keys;
147} {0}
148
149do_execsql_test fkey6-2.4 {
150  BEGIN;
151    PRAGMA defer_foreign_keys = 1;
152    DELETE FROM p1;
153    DROP TABLE c1;
154  COMMIT;
155  PRAGMA defer_foreign_keys;
156} {0}
157
158do_execsql_test fkey6-2.5 {
159  DROP TABLE p1;
160  CREATE TABLE p1(a PRIMARY KEY);
161  INSERT INTO p1 VALUES('one'), ('two');
162  CREATE TABLE c1(x REFERENCES p1);
163  INSERT INTO c1 VALUES('two'), ('one');
164}
165
166do_execsql_test fkey6-2.6 {
167  BEGIN;
168    PRAGMA defer_foreign_keys = 1;
169    INSERT INTO c1 VALUES('three');
170    DROP TABLE c1;
171  COMMIT;
172  PRAGMA defer_foreign_keys;
173} {0}
174
175#--------------------------------------------------------------------------
176# Test that defer_foreign_keys disables RESTRICT.
177#
178do_execsql_test 3.1 {
179  CREATE TABLE p2(a PRIMARY KEY, b);
180  CREATE TABLE c2(x, y REFERENCES p2 ON DELETE RESTRICT ON UPDATE RESTRICT);
181  INSERT INTO p2 VALUES(1, 'one');
182  INSERT INTO p2 VALUES(2, 'two');
183  INSERT INTO c2 VALUES('i', 1);
184}
185
186do_catchsql_test 3.2.1 {
187  BEGIN;
188    UPDATE p2 SET a=a-1;
189} {1 {FOREIGN KEY constraint failed}}
190do_execsql_test 3.2.2 { COMMIT }
191
192do_execsql_test 3.2.3 {
193  BEGIN;
194    PRAGMA defer_foreign_keys = 1;
195    UPDATE p2 SET a=a-1;
196  COMMIT;
197}
198
199do_execsql_test 3.2.4 {
200  BEGIN;
201    PRAGMA defer_foreign_keys = 1;
202    UPDATE p2 SET a=a-1;
203}
204do_catchsql_test 3.2.5 {
205  COMMIT;
206} {1 {FOREIGN KEY constraint failed}}
207do_execsql_test 3.2.6 { ROLLBACK }
208
209do_execsql_test 3.3.1 {
210  CREATE TRIGGER p2t AFTER DELETE ON p2 BEGIN
211    INSERT INTO p2 VALUES(old.a, 'deleted!');
212  END;
213}
214do_catchsql_test 3.3.2 {
215  BEGIN;
216    DELETE FROM p2 WHERE a=1;
217} {1 {FOREIGN KEY constraint failed}}
218do_execsql_test 3.3.3 { COMMIT }
219
220do_execsql_test 3.3.4 {
221  BEGIN;
222    PRAGMA defer_foreign_keys = 1;
223    DELETE FROM p2 WHERE a=1;
224  COMMIT;
225  SELECT * FROM p2;
226} {0 one 1 deleted!}
227
228
229finish_test
230