test/fkey1.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 fkey1
  19
  20 ifcapable {!foreignkey} {
  21   finish_test
  22   return
  23 }
  24
  25 # Create a table and some data to work with.
  26 #
  27 do_test fkey1-1.0 {
  28   execsql {
  29     CREATE TABLE t1(
  30       a INTEGER PRIMARY KEY,
  31       b INTEGER
  32            REFERENCES t1 ON DELETE CASCADE
  33            REFERENCES t2,
  34       c TEXT,
  35       FOREIGN KEY (b,c) REFERENCES t2(x,y) ON UPDATE CASCADE
  36     );
  37   }
  38 } {}
  39 do_test fkey1-1.1 {
  40   execsql {
  41     CREATE TABLE t2(
  42       x INTEGER PRIMARY KEY,
  43       y TEXT
  44     );
  45   }
  46 } {}
  47 do_test fkey1-1.2 {
  48   execsql {
  49     CREATE TABLE t3(
  50       a INTEGER REFERENCES t2,
  51       b INTEGER REFERENCES t1,
  52       FOREIGN KEY (a,b) REFERENCES t2(x,y)
  53     );
  54   }
  55 } {}
  56
  57 do_test fkey1-2.1 {
  58   execsql {
  59     CREATE TABLE t4(a integer primary key);
  60     CREATE TABLE t5(x references t4);
  61     CREATE TABLE t6(x references t4);
  62     CREATE TABLE t7(x references t4);
  63     CREATE TABLE t8(x references t4);
  64     CREATE TABLE t9(x references t4);
  65     CREATE TABLE t10(x references t4);
  66     DROP TABLE t7;
  67     DROP TABLE t9;
  68     DROP TABLE t5;
  69     DROP TABLE t8;
  70     DROP TABLE t6;
  71     DROP TABLE t10;
  72   }
  73 } {}
  74
  75 do_test fkey1-3.1 {
  76   execsql {
  77     CREATE TABLE t5(a PRIMARY KEY, b, c);
  78     CREATE TABLE t6(
  79       d REFERENCES t5,
  80       e REFERENCES t5(c)
  81     );
  82     PRAGMA foreign_key_list(t6);
  83   }
  84 } [concat                                         \
  85   {0 0 t5 e c {NO ACTION} {NO ACTION} NONE}       \
  86   {1 0 t5 d {} {NO ACTION} {NO ACTION} NONE}      \
  87 ]
  88 do_test fkey1-3.2 {
  89   execsql {
  90     CREATE TABLE t7(d, e, f,
  91       FOREIGN KEY (d, e) REFERENCES t5(a, b)
  92     );
  93     PRAGMA foreign_key_list(t7);
  94   }
  95 } [concat                                   \
  96   {0 0 t5 d a {NO ACTION} {NO ACTION} NONE} \
  97   {0 1 t5 e b {NO ACTION} {NO ACTION} NONE} \
  98 ]
  99 do_test fkey1-3.3 {
 100   execsql {
 101     CREATE TABLE t8(d, e, f,
 102       FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL
 103     );
 104     PRAGMA foreign_key_list(t8);
 105   }
 106 } [concat                        \
 107   {0 0 t5 d {} {SET NULL} CASCADE NONE} \
 108   {0 1 t5 e {} {SET NULL} CASCADE NONE} \
 109 ]
 110 do_test fkey1-3.4 {
 111   execsql {
 112     CREATE TABLE t9(d, e, f,
 113       FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT
 114     );
 115     PRAGMA foreign_key_list(t9);
 116   }
 117 } [concat                        \
 118   {0 0 t5 d {} {SET DEFAULT} CASCADE NONE} \
 119   {0 1 t5 e {} {SET DEFAULT} CASCADE NONE} \
 120 ]
 121 do_test fkey1-3.5 {
 122   sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
 123 } {0 0 0}
 124
 125 # Stress the dequoting logic.  The first test is not so bad.
 126 do_execsql_test fkey1-4.0 {
 127   PRAGMA foreign_keys=ON;
 128   CREATE TABLE "xx1"("xx2" TEXT PRIMARY KEY, "xx3" TEXT);
 129   INSERT INTO "xx1"("xx2","xx3") VALUES('abc','def');
 130   CREATE TABLE "xx4"("xx5" TEXT REFERENCES "xx1" ON DELETE CASCADE);
 131   INSERT INTO "xx4"("xx5") VALUES('abc');
 132   INSERT INTO "xx1"("xx2","xx3") VALUES('uvw','xyz');
 133   SELECT 1, "xx5" FROM "xx4";
 134   DELETE FROM "xx1";
 135   SELECT 2, "xx5" FROM "xx4";
 136 } {1 abc}
 137
 138 # This case is identical to the previous except the "xx" in each name
 139 # is changed to a single escaped double-quote character.
 140 do_execsql_test fkey1-4.1 {
 141   PRAGMA foreign_keys=ON;
 142   CREATE TABLE """1"("""2" TEXT PRIMARY KEY, """3" TEXT);
 143   INSERT INTO """1"("""2","""3") VALUES('abc','def');
 144   CREATE TABLE """4"("""5" TEXT REFERENCES """1" ON DELETE CASCADE);
 145   INSERT INTO """4"("""5") VALUES('abc');
 146   INSERT INTO """1"("""2","""3") VALUES('uvw','xyz');
 147   SELECT 1, """5" FROM """4";
 148   DELETE FROM """1";
 149   SELECT 2, """5" FROM """4";
 150 } {1 abc}
 151 do_execsql_test fkey1-4.2 {
 152   PRAGMA table_info="""1";
 153 } {0 {"2} TEXT 0 {} 1 1 {"3} TEXT 0 {} 0}
 154
 155 #-------------------------------------------------------------------------
 156 #
 157 do_execsql_test fkey1-5.1 {
 158   CREATE TABLE t11(
 159     x INTEGER PRIMARY KEY,
 160     parent REFERENCES t11 ON DELETE CASCADE
 161   );
 162   INSERT INTO t11 VALUES (1, NULL), (2, 1), (3, 2);
 163 } {}
 164
 165 # The REPLACE part of this statement deletes the row (2, 1). Then the
 166 # DELETE CASCADE caused by deleting that row removes the (3, 2) row. Which
 167 # would have been the parent of the new row being inserted. Causing an
 168 # FK violation.
 169 #
 170 do_catchsql_test fkey1-5.2 {
 171   INSERT OR REPLACE INTO t11 VALUES (2, 3);
 172 } {1 {FOREIGN KEY constraint failed}}
 173
 174 # Make sure sqlite3_trace() output works with triggers used to implement
 175 # FK constraints
 176 #
 177 ifcapable trace {
 178   proc sqltrace {txt} {
 179     global traceoutput
 180     lappend traceoutput $txt
 181   }
 182   do_test fkey1-5.2.1 {
 183     unset -nocomplain traceoutput
 184     db trace sqltrace
 185     catch {db eval {INSERT OR REPLACE INTO t11 VALUES(2,3);}}
 186     set traceoutput
 187   } {{INSERT OR REPLACE INTO t11 VALUES(2,3);} {INSERT OR REPLACE INTO t11 VALUES(2,3);} {INSERT OR REPLACE INTO t11 VALUES(2,3);}}
 188 }
 189
 190 # A similar test to the above.
 191 do_execsql_test fkey1-5.3 {
 192   CREATE TABLE Foo (
 193     Id INTEGER PRIMARY KEY,
 194     ParentId INTEGER REFERENCES Foo(Id) ON DELETE CASCADE, C1
 195   );
 196   INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (1, null, 'A');
 197   INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 1, 'A-2-1');
 198   INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (3, 2, 'A-3-2');
 199   INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (4, 3, 'A-4-3');
 200 }
 201 do_catchsql_test fkey1-5.4 {
 202   INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 3, 'A-2-3');
 203 } {1 {FOREIGN KEY constraint failed}}
 204
 205 #-------------------------------------------------------------------------
 206 # Check that foreign key processing is not fooled by partial indexes
 207 # on the parent table.
 208 #
 209 do_execsql_test 6.0 {
 210   CREATE TABLE p1(x, y);
 211   CREATE UNIQUE INDEX p1x ON p1(x) WHERE y<2;
 212   INSERT INTO p1 VALUES(1, 1);
 213   CREATE TABLE c1(a REFERENCES p1(x));
 214 }
 215
 216 do_catchsql_test 6.1 {
 217   INSERT INTO c1 VALUES(1);
 218 } {1 {foreign key mismatch - "c1" referencing "p1"}}
 219
 220 do_execsql_test 6.2 {
 221   CREATE UNIQUE INDEX p1x2 ON p1(x);
 222   INSERT INTO c1 VALUES(1);
 223 } {}
 224
 225
 226 finish_test