xref: /sqlite-3.40.0/test/fkey6.test (revision 648e2643) 
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
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20ifcapable {!foreignkey} {
21  finish_test
22  return
23}
24
25do_execsql_test fkey6-1.1 {
26  PRAGMA foreign_keys=ON;
27  CREATE TABLE t1(x INTEGER PRIMARY KEY);
28  CREATE TABLE t2(y INTEGER PRIMARY KEY,
29          z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED);
30  CREATE INDEX t2z ON t2(z);
31  CREATE TABLE t3(u INTEGER PRIMARY KEY, v INTEGER REFERENCES t1(x));
32  CREATE INDEX t3v ON t3(v);
33  INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
34  INSERT INTO t2 VALUES(1,1),(2,2);
35  INSERT INTO t3 VALUES(3,3),(4,4);
36} {}
37do_test fkey6-1.2 {
38  catchsql {DELETE FROM t1 WHERE x=2;}
39} {1 {foreign key constraint failed}}
40do_test fkey6-1.3 {
41  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
42} {0 0 0}
43do_test fkey6-1.4 {
44  execsql {
45    BEGIN;
46    DELETE FROM t1 WHERE x=1;
47  }
48} {}
49do_test fkey6-1.5.1 {
50  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 1
51} {0 1 0}
52do_test fkey6-1.5.2 {
53  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
54} {0 1 0}
55do_test fkey6-1.6 {
56  execsql {
57    ROLLBACK;
58  }
59} {}
60do_test fkey6-1.7 {
61  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
62} {0 0 0}
63do_test fkey6-1.8 {
64  execsql {
65    PRAGMA defer_foreign_keys=ON;
66    BEGIN;
67    DELETE FROM t1 WHERE x=3;
68  }
69} {}
70do_test fkey6-1.9 {
71  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
72} {0 1 0}
73do_test fkey6-1.10 {
74  execsql {
75    ROLLBACK;
76    PRAGMA defer_foreign_keys=OFF;
77    BEGIN;
78  }
79  catchsql {DELETE FROM t1 WHERE x=3}
80} {1 {foreign key constraint failed}}
81db eval {ROLLBACK}
82
83do_test fkey6-1.20 {
84  execsql {
85    BEGIN;
86    DELETE FROM t1 WHERE x=1;
87  }
88  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
89} {0 1 0}
90do_test fkey6-1.21 {
91  execsql {
92    DELETE FROM t2 WHERE y=1;
93  }
94  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
95} {0 0 0}
96do_test fkey6-1.22 {
97  execsql {
98    COMMIT;
99  }
100} {}
101
102
103finish_test
104