000001 # 2012 December 17
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for SQLite library.
000012 #
000013 # This file tests the PRAGMA defer_foreign_keys and
000014 # SQLITE_DBSTATUS_DEFERRED_FKS
000015 #
000016 # EVIDENCE-OF: R-18981-16292 When the defer_foreign_keys PRAGMA is on,
000017 # enforcement of all foreign key constraints is delayed until the
000018 # outermost transaction is committed.
000019 #
000020 # EVIDENCE-OF: R-28911-57501 The defer_foreign_keys pragma defaults to
000021 # OFF so that foreign key constraints are only deferred if they are
000022 # created as "DEFERRABLE INITIALLY DEFERRED".
000023
000024 set testdir [file dirname $argv0]
000025 source $testdir/tester.tcl
000026 set testprefix fkey6
000027
000028 ifcapable {!foreignkey} {
000029 finish_test
000030 return
000031 }
000032
000033 do_execsql_test fkey6-1.0 {
000034 PRAGMA defer_foreign_keys;
000035 } {0}
000036
000037 do_execsql_test fkey6-1.1 {
000038 PRAGMA foreign_keys=ON;
000039 CREATE TABLE t1(x INTEGER PRIMARY KEY);
000040 CREATE TABLE t2(y INTEGER PRIMARY KEY,
000041 z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED);
000042 CREATE INDEX t2z ON t2(z);
000043 CREATE TABLE t3(u INTEGER PRIMARY KEY, v INTEGER REFERENCES t1(x));
000044 CREATE INDEX t3v ON t3(v);
000045 INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
000046 INSERT INTO t2 VALUES(1,1),(2,2);
000047 INSERT INTO t3 VALUES(3,3),(4,4);
000048 } {}
000049 do_test fkey6-1.2 {
000050 catchsql {DELETE FROM t1 WHERE x=2;}
000051 } {1 {FOREIGN KEY constraint failed}}
000052 do_test fkey6-1.3 {
000053 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000054 } {0 0 0}
000055 do_test fkey6-1.4 {
000056 execsql {
000057 BEGIN;
000058 DELETE FROM t1 WHERE x=1;
000059 }
000060 } {}
000061 do_test fkey6-1.5.1 {
000062 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 1
000063 } {0 1 0}
000064 do_test fkey6-1.5.2 {
000065 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000066 } {0 1 0}
000067 do_test fkey6-1.6 {
000068 execsql {
000069 ROLLBACK;
000070 }
000071 } {}
000072 do_test fkey6-1.7 {
000073 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000074 } {0 0 0}
000075 do_test fkey6-1.8 {
000076 execsql {
000077 PRAGMA defer_foreign_keys=ON;
000078 BEGIN;
000079 DELETE FROM t1 WHERE x=3;
000080 }
000081 } {}
000082 do_test fkey6-1.9 {
000083 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000084 } {0 1 0}
000085
000086 # EVIDENCE-OF: R-21752-26913 The defer_foreign_keys pragma is
000087 # automatically switched off at each COMMIT or ROLLBACK. Hence, the
000088 # defer_foreign_keys pragma must be separately enabled for each
000089 # transaction.
000090 do_execsql_test fkey6-1.10.1 {
000091 PRAGMA defer_foreign_keys;
000092 ROLLBACK;
000093 PRAGMA defer_foreign_keys;
000094 BEGIN;
000095 PRAGMA defer_foreign_keys=ON;
000096 PRAGMA defer_foreign_keys;
000097 COMMIT;
000098 PRAGMA defer_foreign_keys;
000099 BEGIN;
000100 } {1 0 1 0}
000101 do_test fkey6-1.10.2 {
000102 catchsql {DELETE FROM t1 WHERE x=3}
000103 } {1 {FOREIGN KEY constraint failed}}
000104 db eval {ROLLBACK}
000105
000106 do_test fkey6-1.20 {
000107 execsql {
000108 BEGIN;
000109 DELETE FROM t1 WHERE x=1;
000110 }
000111 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000112 } {0 1 0}
000113 do_test fkey6-1.21 {
000114 execsql {
000115 DELETE FROM t2 WHERE y=1;
000116 }
000117 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
000118 } {0 0 0}
000119 do_test fkey6-1.22 {
000120 execsql {
000121 COMMIT;
000122 }
000123 } {}
000124
000125 do_execsql_test fkey6-2.1 {
000126 CREATE TABLE p1(a PRIMARY KEY);
000127 INSERT INTO p1 VALUES('one'), ('two');
000128 CREATE TABLE c1(x REFERENCES p1);
000129 INSERT INTO c1 VALUES('two'), ('one');
000130 }
000131
000132 do_execsql_test fkey6-2.2 {
000133 BEGIN;
000134 PRAGMA defer_foreign_keys = 1;
000135 DELETE FROM p1;
000136 ROLLBACK;
000137 PRAGMA defer_foreign_keys;
000138 } {0}
000139
000140 do_execsql_test fkey6-2.3 {
000141 BEGIN;
000142 PRAGMA defer_foreign_keys = 1;
000143 DROP TABLE p1;
000144 PRAGMA vdbe_trace = 0;
000145 ROLLBACK;
000146 PRAGMA defer_foreign_keys;
000147 } {0}
000148
000149 do_execsql_test fkey6-2.4 {
000150 BEGIN;
000151 PRAGMA defer_foreign_keys = 1;
000152 DELETE FROM p1;
000153 DROP TABLE c1;
000154 COMMIT;
000155 PRAGMA defer_foreign_keys;
000156 } {0}
000157
000158 do_execsql_test fkey6-2.5 {
000159 DROP TABLE p1;
000160 CREATE TABLE p1(a PRIMARY KEY);
000161 INSERT INTO p1 VALUES('one'), ('two');
000162 CREATE TABLE c1(x REFERENCES p1);
000163 INSERT INTO c1 VALUES('two'), ('one');
000164 }
000165
000166 do_execsql_test fkey6-2.6 {
000167 BEGIN;
000168 PRAGMA defer_foreign_keys = 1;
000169 INSERT INTO c1 VALUES('three');
000170 DROP TABLE c1;
000171 COMMIT;
000172 PRAGMA defer_foreign_keys;
000173 } {0}
000174
000175 #--------------------------------------------------------------------------
000176 # Test that defer_foreign_keys disables RESTRICT.
000177 #
000178 do_execsql_test 3.1 {
000179 CREATE TABLE p2(a PRIMARY KEY, b);
000180 CREATE TABLE c2(x, y REFERENCES p2 ON DELETE RESTRICT ON UPDATE RESTRICT);
000181 INSERT INTO p2 VALUES(1, 'one');
000182 INSERT INTO p2 VALUES(2, 'two');
000183 INSERT INTO c2 VALUES('i', 1);
000184 }
000185
000186 do_catchsql_test 3.2.1 {
000187 BEGIN;
000188 UPDATE p2 SET a=a-1;
000189 } {1 {FOREIGN KEY constraint failed}}
000190 do_execsql_test 3.2.2 { COMMIT }
000191
000192 do_execsql_test 3.2.3 {
000193 BEGIN;
000194 PRAGMA defer_foreign_keys = 1;
000195 UPDATE p2 SET a=a-1;
000196 COMMIT;
000197 }
000198
000199 do_execsql_test 3.2.4 {
000200 BEGIN;
000201 PRAGMA defer_foreign_keys = 1;
000202 UPDATE p2 SET a=a-1;
000203 }
000204 do_catchsql_test 3.2.5 {
000205 COMMIT;
000206 } {1 {FOREIGN KEY constraint failed}}
000207 do_execsql_test 3.2.6 { ROLLBACK }
000208
000209 do_execsql_test 3.3.1 {
000210 CREATE TRIGGER p2t AFTER DELETE ON p2 BEGIN
000211 INSERT INTO p2 VALUES(old.a, 'deleted!');
000212 END;
000213 }
000214 do_catchsql_test 3.3.2 {
000215 BEGIN;
000216 DELETE FROM p2 WHERE a=1;
000217 } {1 {FOREIGN KEY constraint failed}}
000218 do_execsql_test 3.3.3 { COMMIT }
000219
000220 do_execsql_test 3.3.4 {
000221 BEGIN;
000222 PRAGMA defer_foreign_keys = 1;
000223 DELETE FROM p2 WHERE a=1;
000224 COMMIT;
000225 SELECT * FROM p2;
000226 } {0 one 1 deleted!}
000227
000228
000229 finish_test