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