test/fkey6.test

   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