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 # 2021-07-03 https://sqlite.org/forum/forumpost/a6b0c05277
 226 # 2021-07-07 https://sqlite.org/forum/forumpost/79c9e4797d
 227 # Failure to allocate enough registers in the VDBE for a
 228 # PRAGMA foreign_key_check when the foreign key has more
 229 # columns than the table.
 230 #
 231 reset_db
 232 do_execsql_test 7.1 {
 233   PRAGMA foreign_keys=OFF;
 234   CREATE TABLE t1(a,b,c,FOREIGN KEY(a,a,a,a,a,a,a,a,a,a,a,a,a,a) REFERENCES t0);
 235   INSERT INTO t1 VALUES(1,2,3);
 236   PRAGMA foreign_key_check;
 237 } {t1 1 t0 0}
 238 do_execsql_test 7.2 {
 239   DROP TABLE t1;
 240   CREATE TABLE t1(a,b,c AS(1),d, FOREIGN KEY(c,d,b,a,b,d,b,c) REFERENCES t0);
 241   PRAGMA foreign_key_check;
 242 } {}
 243
 244 # 2021-12-31 forum https://sqlite.org/forum/forumpost/24bd1fef7e9323ef
 245 # Memory leak caused by sqlite3NestedParse() running on a corrupt system
 246 # table.  Discovered by Jingzhou Fu.
 247 #
 248 reset_db
 249 do_execsql_test 8.1 {
 250   PRAGMA writable_schema=ON;
 251   PRAGMA foreign_keys = ON;
 252   CREATE TABLE sqlite_stat1 (tbl INTEGER PRIMARY KEY DESC, idx UNIQUE DEFAULT NULL) WITHOUT ROWID;
 253   PRAGMA writable_schema=OFF;
 254   CREATE TABLE sqlsim4(stat PRIMARY KEY);;
 255   CREATE TABLE t1(sqlsim7 REFERENCES sqlite_stat1 ON DELETE CASCADE);
 256   DROP table "sqlsim4";
 257 } {}
 258 # 2022-01-01 dbsqlfuzz 1c57440219f6f0aedf5e8f72a8ddd75f15aea381
 259 # Follow-up case to the above.  Assertion is not true if the schema
 260 # is corrupt.
 261 reset_db
 262 database_may_be_corrupt
 263 do_execsql_test 8.2 {
 264   CREATE TABLE t1(a REFERENCES sqlite_stat1 ON DELETE CASCADE);
 265   CREATE TABLE t2(a TEXT PRIMARY KEY);
 266   PRAGMA writable_schema=ON;
 267   CREATE TABLE sqlite_stat1(tbl INTEGER PRIMARY KEY DESC, idx UNIQUE DEFAULT NULL) WITHOUT ROWID;
 268   UPDATE sqlite_schema SET name='sqlite_autoindex_sqlite_stat1_1' WHERE name='sqlite_autoindex_sqlite_stat1_2';
 269   PRAGMA writable_schema=RESET;
 270 } {}
 271 do_catchsql_test 8.3 {
 272   REINDEX;
 273 } {1 {database disk image is malformed}}
 274
 275 # 2023-04-13 https://bugs.chromium.org/p/chromium/issues/detail?id=1405220
 276 # Avoid double-de-quoting of table names when processing foreign keys.
 277 #
 278 reset_db
 279 do_execsql_test 9.1 {
 280   PRAGMA foreign_keys = ON;
 281   CREATE TABLE """1"("""2", """3" PRIMARY KEY);
 282   CREATE TABLE """4"("""5" REFERENCES """1" ON DELETE RESTRICT);
 283   DELETE FROM """1";
 284 }
 285
 286 finish_test