test/fkey8.test

   1 # 2001 September 15
   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 implements tests for foreign keys.
  14 #
  15
  16 set testdir [file dirname $argv0]
  17 source $testdir/tester.tcl
  18 set testprefix fkey8
  19
  20 ifcapable {!foreignkey} {
  21   finish_test
  22   return
  23 }
  24 do_execsql_test 1.0 { PRAGMA foreign_keys = 1; }
  25
  26
  27 foreach {tn use_stmt sql schema} {
  28   1   1 "DELETE FROM p1" {
  29     CREATE TABLE p1(a PRIMARY KEY);
  30     CREATE TABLE c1(b REFERENCES p1);
  31   }
  32
  33   2.1     0 "DELETE FROM p1" {
  34     CREATE TABLE p1(a PRIMARY KEY);
  35     CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE);
  36   }
  37   2.2   0 "DELETE FROM p1" {
  38     CREATE TABLE p1(a PRIMARY KEY);
  39     CREATE TABLE c1(b REFERENCES p1 ON DELETE SET NULL);
  40   }
  41   2.3   1 "DELETE FROM p1" {
  42     CREATE TABLE p1(a PRIMARY KEY);
  43     CREATE TABLE c1(b REFERENCES p1 ON DELETE SET DEFAULT);
  44   }
  45
  46   3   1 "DELETE FROM p1" {
  47     CREATE TABLE p1(a PRIMARY KEY);
  48     CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE);
  49     CREATE TRIGGER ct1 AFTER DELETE ON c1 BEGIN
  50       INSERT INTO p1 VALUES('x');
  51     END;
  52   }
  53
  54   4   1 "DELETE FROM p1" {
  55     CREATE TABLE p1(a PRIMARY KEY);
  56     CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
  57     CREATE TABLE cc1(d REFERENCES c1);
  58   }
  59
  60   5.1   0 "DELETE FROM p1" {
  61     CREATE TABLE p1(a PRIMARY KEY);
  62     CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
  63     CREATE TABLE cc1(d REFERENCES c1 ON DELETE CASCADE);
  64   }
  65   5.2   0 "DELETE FROM p1" {
  66     CREATE TABLE p1(a PRIMARY KEY);
  67     CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
  68     CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET NULL);
  69   }
  70   5.3   1 "DELETE FROM p1" {
  71     CREATE TABLE p1(a PRIMARY KEY);
  72     CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY);
  73     CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET DEFAULT);
  74   }
  75
  76   6.1   1 "UPDATE p1 SET a = ?" {
  77     CREATE TABLE p1(a PRIMARY KEY);
  78     CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c);
  79   }
  80   6.2   0 "UPDATE OR IGNORE p1 SET a = ?" {
  81     CREATE TABLE p1(a PRIMARY KEY);
  82     CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c);
  83   }
  84   6.3   1 "UPDATE OR IGNORE p1 SET a = ?" {
  85     CREATE TABLE p1(a PRIMARY KEY);
  86     CREATE TABLE c1(b REFERENCES p1 ON UPDATE CASCADE, c);
  87   }
  88   6.4   1 "UPDATE OR IGNORE p1 SET a = ?" {
  89     CREATE TABLE p1(a PRIMARY KEY);
  90     CREATE TABLE c1(b NOT NULL REFERENCES p1 ON UPDATE SET NULL, c);
  91   }
  92
  93 } {
  94   drop_all_tables
  95   do_test 1.$tn {
  96     execsql $schema
  97     set stmt [sqlite3_prepare_v2 db $sql -1 dummy]
  98     set ret [uses_stmt_journal $stmt]
  99     sqlite3_finalize $stmt
 100     set ret
 101   } $use_stmt
 102 }
 103
 104 #-------------------------------------------------------------------------
 105 # The following tests check that foreign key constaint counters are
 106 # correctly updated for any implicit DELETE operations that occur
 107 # when a REPLACE command is executed against a WITHOUT ROWID table
 108 # that has no triggers or auxiliary indexes.
 109 #
 110 reset_db
 111 do_execsql_test 2.1.0 {
 112   PRAGMA foreign_keys = on;
 113   CREATE TABLE p1(a PRIMARY KEY, b) WITHOUT ROWID;
 114   CREATE TABLE c1(x REFERENCES p1 DEFERRABLE INITIALLY DEFERRED);
 115
 116   INSERT INTO p1 VALUES(1, 'one');
 117   INSERT INTO p1 VALUES(2, 'two');
 118   INSERT INTO c1 VALUES(1);
 119   INSERT INTO c1 VALUES(2);
 120 }
 121
 122 do_catchsql_test 2.1.2 {
 123   BEGIN;
 124     DELETE FROM p1 WHERE a=1;
 125     INSERT OR REPLACE INTO p1 VALUES(2, 'two');
 126   COMMIT;
 127 } {1 {FOREIGN KEY constraint failed}}
 128
 129 reset_db
 130 do_execsql_test 2.2.0 {
 131   PRAGMA foreign_keys = on;
 132   CREATE TABLE p2(a PRIMARY KEY, b);
 133   CREATE TABLE c2(
 134     x PRIMARY KEY,
 135     y REFERENCES p2 DEFERRABLE INITIALLY DEFERRED
 136   ) WITHOUT ROWID;
 137 }
 138
 139 do_catchsql_test 2.2.1 {
 140   BEGIN;
 141     INSERT INTO c2 VALUES(13, 13);
 142     INSERT OR REPLACE INTO c2 VALUES(13, 13);
 143     DELETE FROM c2;
 144   COMMIT;
 145 } {0 {}}
 146
 147 reset_db
 148 do_execsql_test 2.3.0 {
 149   PRAGMA foreign_keys = on;
 150   CREATE TABLE p3(a PRIMARY KEY, b) WITHOUT ROWID;
 151   CREATE TABLE c3(x REFERENCES p3);
 152
 153   INSERT INTO p3 VALUES(1, 'one');
 154   INSERT INTO p3 VALUES(2, 'two');
 155   INSERT INTO c3 VALUES(1);
 156   INSERT INTO c3 VALUES(2);
 157
 158   CREATE TRIGGER p3d AFTER DELETE ON p3 WHEN old.a=1 BEGIN
 159     INSERT OR REPLACE INTO p3 VALUES(2, 'three');
 160   END;
 161 }
 162
 163 do_catchsql_test 2.3.1 {
 164   DELETE FROM p3 WHERE a=1
 165 } {1 {FOREIGN KEY constraint failed}}
 166
 167
 168 do_execsql_test 3.0 {
 169   PRAGMA foreign_keys=ON;
 170   CREATE TABLE t2(
 171     a PRIMARY KEY, b, c, d, e,
 172       FOREIGN KEY(b, c) REFERENCES t2(d, e)
 173   ) WITHOUT ROWID;
 174   CREATE UNIQUE INDEX idx ON t2(d, e);
 175
 176   INSERT INTO t2 VALUES(1, 'one', 'one', 'one', 'one'); -- row is parent of self
 177   INSERT INTO t2 VALUES(2, 'one', 'one', 'one', NULL);  -- parent is row 1
 178 }
 179
 180 do_catchsql_test 3.1 {
 181   DELETE FROM t2 WHERE a=1;
 182 } {1 {FOREIGN KEY constraint failed}}
 183
 184 do_execsql_test 4.0 {
 185   CREATE TABLE t1 (
 186       c1 PRIMARY KEY,
 187       c2 NUMERIC,
 188       FOREIGN KEY(c1) REFERENCES t1(c2)
 189       ) WITHOUT ROWID ;
 190   CREATE INDEX t1c1 ON t1(c1);
 191   CREATE UNIQUE INDEX t1c1unique ON t1(c2);
 192 }
 193 do_catchsql_test 4.1 {
 194   INSERT OR REPLACE INTO t1 VALUES(10000, 20000);
 195 } {1 {FOREIGN KEY constraint failed}}
 196 do_execsql_test 4.2 {
 197   INSERT OR REPLACE INTO t1 VALUES(20000, 20000);
 198 }
 199
 200 #-------------------------------------------------------------------------
 201 reset_db
 202 do_execsql_test 5.0 {
 203   PRAGMA foreign_keys = true;
 204   CREATE TABLE parent(
 205     p TEXT PRIMARY KEY
 206   );
 207   CREATE TABLE child(
 208     c INTEGER UNIQUE,
 209     FOREIGN KEY(c) REFERENCES parent(p) DEFERRABLE INITIALLY DEFERRED
 210   );
 211   BEGIN;
 212     INSERT INTO child VALUES(123);
 213     INSERT INTO parent VALUES('123');
 214   COMMIT;
 215 }
 216 do_execsql_test 5.1 {
 217   PRAGMA integrity_check;
 218 } {ok}
 219
 220 do_execsql_test 5.2 {
 221   INSERT INTO parent VALUES(1200);
 222   BEGIN;
 223     INSERT INTO child VALUES(456);
 224     UPDATE parent SET p = '456' WHERE p=1200;
 225   COMMIT;
 226 }
 227 do_execsql_test 5.3 {
 228   PRAGMA integrity_check;
 229 } {ok}
 230
 231 #-------------------------------------------------------------------------
 232 reset_db
 233 forcedelete test.db2
 234 do_execsql_test 6.1 {
 235   PRAGMA foreign_keys = on;
 236   CREATE TABLE c1(b);
 237   INSERT INTO c1 VALUES(123);
 238 }
 239
 240 do_execsql_test 6.2 {
 241   ATTACH 'test.db2' AS aux;
 242   CREATE TABLE aux.p1(a INTEGER PRIMARY KEY);
 243   CREATE TABLE aux.c1(b REFERENCES p1(a) ON DELETE RESTRICT);
 244
 245   INSERT INTO aux.p1 VALUES(123);
 246 }
 247
 248 do_execsql_test 6.3 {
 249   DELETE FROM aux.p1 WHERE a=123;
 250 }
 251
 252
 253 finish_test