gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/fkey6.test (about) 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 24 set testdir [file dirname $argv0] 25 source $testdir/tester.tcl 26 set testprefix fkey6 27 28 ifcapable {!foreignkey} { 29 finish_test 30 return 31 } 32 33 do_execsql_test fkey6-1.0 { 34 PRAGMA defer_foreign_keys; 35 } {0} 36 37 do_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 } {} 49 do_test fkey6-1.2 { 50 catchsql {DELETE FROM t1 WHERE x=2;} 51 } {1 {FOREIGN KEY constraint failed}} 52 do_test fkey6-1.3 { 53 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 54 } {0 0 0} 55 do_test fkey6-1.4 { 56 execsql { 57 BEGIN; 58 DELETE FROM t1 WHERE x=1; 59 } 60 } {} 61 do_test fkey6-1.5.1 { 62 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 1 63 } {0 1 0} 64 do_test fkey6-1.5.2 { 65 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 66 } {0 1 0} 67 do_test fkey6-1.6 { 68 execsql { 69 ROLLBACK; 70 } 71 } {} 72 do_test fkey6-1.7 { 73 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 74 } {0 0 0} 75 do_test fkey6-1.8 { 76 execsql { 77 PRAGMA defer_foreign_keys=ON; 78 BEGIN; 79 DELETE FROM t1 WHERE x=3; 80 } 81 } {} 82 do_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. 90 do_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} 101 do_test fkey6-1.10.2 { 102 catchsql {DELETE FROM t1 WHERE x=3} 103 } {1 {FOREIGN KEY constraint failed}} 104 db eval {ROLLBACK} 105 106 do_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} 113 do_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} 119 do_test fkey6-1.22 { 120 execsql { 121 COMMIT; 122 } 123 } {} 124 125 do_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 132 do_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 140 do_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 149 do_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 158 do_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 166 do_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 # 178 do_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 186 do_catchsql_test 3.2.1 { 187 BEGIN; 188 UPDATE p2 SET a=a-1; 189 } {1 {FOREIGN KEY constraint failed}} 190 do_execsql_test 3.2.2 { COMMIT } 191 192 do_execsql_test 3.2.3 { 193 BEGIN; 194 PRAGMA defer_foreign_keys = 1; 195 UPDATE p2 SET a=a-1; 196 COMMIT; 197 } 198 199 do_execsql_test 3.2.4 { 200 BEGIN; 201 PRAGMA defer_foreign_keys = 1; 202 UPDATE p2 SET a=a-1; 203 } 204 do_catchsql_test 3.2.5 { 205 COMMIT; 206 } {1 {FOREIGN KEY constraint failed}} 207 do_execsql_test 3.2.6 { ROLLBACK } 208 209 do_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 } 214 do_catchsql_test 3.3.2 { 215 BEGIN; 216 DELETE FROM p2 WHERE a=1; 217 } {1 {FOREIGN KEY constraint failed}} 218 do_execsql_test 3.3.3 { COMMIT } 219 220 do_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 229 finish_test