test/fkey2.test

   1 # 2009 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||!trigger} {
  20   finish_test
  21   return
  22 }
  23
  24 #-------------------------------------------------------------------------
  25 # Test structure:
  26 #
  27 # fkey2-1.*: Simple tests to check that immediate and deferred foreign key
  28 #            constraints work when not inside a transaction.
  29 #
  30 # fkey2-2.*: Tests to verify that deferred foreign keys work inside
  31 #            explicit transactions (i.e that processing really is deferred).
  32 #
  33 # fkey2-3.*: Tests that a statement transaction is rolled back if an
  34 #            immediate foreign key constraint is violated.
  35 #
  36 # fkey2-4.*: Test that FK actions may recurse even when recursive triggers
  37 #            are disabled.
  38 #
  39 # fkey2-5.*: Check that if foreign-keys are enabled, it is not possible
  40 #            to write to an FK column using the incremental blob API.
  41 #
  42 # fkey2-6.*: Test that FK processing is automatically disabled when
  43 #            running VACUUM.
  44 #
  45 # fkey2-7.*: Test using an IPK as the key in the child (referencing) table.
  46 #
  47 # fkey2-8.*: Test that enabling/disabling foreign key support while a
  48 #            transaction is active is not possible.
  49 #
  50 # fkey2-9.*: Test SET DEFAULT actions.
  51 #
  52 # fkey2-10.*: Test errors.
  53 #
  54 # fkey2-11.*: Test CASCADE actions.
  55 #
  56 # fkey2-12.*: Test RESTRICT actions.
  57 #
  58 # fkey2-13.*: Test that FK processing is performed when a row is REPLACED by
  59 #             an UPDATE or INSERT statement.
  60 #
  61 # fkey2-14.*: Test the ALTER TABLE and DROP TABLE commands.
  62 #
  63 # fkey2-15.*: Test that if there are no (known) outstanding foreign key
  64 #             constraint violations in the database, inserting into a parent
  65 #             table or deleting from a child table does not cause SQLite
  66 #             to check if this has repaired an outstanding violation.
  67 #
  68 # fkey2-16.*: Test that rows that refer to themselves may be inserted,
  69 #             updated and deleted.
  70 #
  71 # fkey2-17.*: Test that the "count_changes" pragma does not interfere with
  72 #             FK constraint processing.
  73 #
  74 # fkey2-18.*: Test that the authorization callback is invoked when processing
  75 #             FK constraints.
  76 #
  77 # fkey2-20.*: Test that ON CONFLICT clauses specified as part of statements
  78 #             do not affect the operation of FK constraints.
  79 #
  80 # fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
  81 #            command. Recycled to test the built-in implementation.
  82 #
  83 # fkey2-dd08e5.*:  Tests to verify that ticket dd08e5a988d00decc4a543daa8d
  84 #                  has been fixed.
  85 #
  86
  87
  88 execsql { PRAGMA foreign_keys = on }
  89
  90 set FkeySimpleSchema {
  91   PRAGMA foreign_keys = on;
  92   CREATE TABLE t1(a PRIMARY KEY, b);
  93   CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);
  94
  95   CREATE TABLE t3(a PRIMARY KEY, b);
  96   CREATE TABLE t4(c REFERENCES t3 /D/, d);
  97
  98   CREATE TABLE t7(a, b INTEGER PRIMARY KEY);
  99   CREATE TABLE t8(c REFERENCES t7 /D/, d);
 100
 101   CREATE TABLE t9(a REFERENCES nosuchtable, b);
 102   CREATE TABLE t10(a REFERENCES t9(c) /D/, b);
 103 }
 104
 105
 106 set FkeySimpleTests {
 107   1.1  "INSERT INTO t2 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
 108   1.2  "INSERT INTO t1 VALUES(1, 2)"      {0 {}}
 109   1.3  "INSERT INTO t2 VALUES(1, 3)"      {0 {}}
 110   1.4  "INSERT INTO t2 VALUES(2, 4)"      {1 {FOREIGN KEY constraint failed}}
 111   1.5  "INSERT INTO t2 VALUES(NULL, 4)"   {0 {}}
 112   1.6  "UPDATE t2 SET c=2 WHERE d=4"      {1 {FOREIGN KEY constraint failed}}
 113   1.7  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
 114   1.9  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
 115   1.10 "UPDATE t2 SET c=NULL WHERE d=4"   {0 {}}
 116   1.11 "DELETE FROM t1 WHERE a=1"         {1 {FOREIGN KEY constraint failed}}
 117   1.12 "UPDATE t1 SET a = 2"              {1 {FOREIGN KEY constraint failed}}
 118   1.13 "UPDATE t1 SET a = 1"              {0 {}}
 119
 120   2.1  "INSERT INTO t4 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
 121   2.2  "INSERT INTO t3 VALUES(1, 2)"      {0 {}}
 122   2.3  "INSERT INTO t4 VALUES(1, 3)"      {0 {}}
 123
 124   4.1  "INSERT INTO t8 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
 125   4.2  "INSERT INTO t7 VALUES(2, 1)"      {0 {}}
 126   4.3  "INSERT INTO t8 VALUES(1, 3)"      {0 {}}
 127   4.4  "INSERT INTO t8 VALUES(2, 4)"      {1 {FOREIGN KEY constraint failed}}
 128   4.5  "INSERT INTO t8 VALUES(NULL, 4)"   {0 {}}
 129   4.6  "UPDATE t8 SET c=2 WHERE d=4"      {1 {FOREIGN KEY constraint failed}}
 130   4.7  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
 131   4.9  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
 132   4.10 "UPDATE t8 SET c=NULL WHERE d=4"   {0 {}}
 133   4.11 "DELETE FROM t7 WHERE b=1"         {1 {FOREIGN KEY constraint failed}}
 134   4.12 "UPDATE t7 SET b = 2"              {1 {FOREIGN KEY constraint failed}}
 135   4.13 "UPDATE t7 SET b = 1"              {0 {}}
 136   4.14 "INSERT INTO t8 VALUES('a', 'b')"  {1 {FOREIGN KEY constraint failed}}
 137   4.15 "UPDATE t7 SET b = 5"              {1 {FOREIGN KEY constraint failed}}
 138   4.16 "UPDATE t7 SET rowid = 5"          {1 {FOREIGN KEY constraint failed}}
 139   4.17 "UPDATE t7 SET a = 10"             {0 {}}
 140
 141   5.1  "INSERT INTO t9 VALUES(1, 3)"      {1 {no such table: main.nosuchtable}}
 142   5.2  "INSERT INTO t10 VALUES(1, 3)"
 143                             {1 {foreign key mismatch - "t10" referencing "t9"}}
 144 }
 145
 146 do_test fkey2-1.1.0 {
 147   execsql [string map {/D/ {}} $FkeySimpleSchema]
 148 } {}
 149 foreach {tn zSql res} $FkeySimpleTests {
 150   do_test fkey2-1.1.$tn.1 { catchsql $zSql } $res
 151   do_test fkey2-1.1.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
 152   do_test fkey2-1.1.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
 153   do_test fkey2-1.1.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
 154   do_test fkey2-1.1.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
 155   do_test fkey2-1.1.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
 156   do_test fkey2-1.1.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
 157 }
 158 drop_all_tables
 159
 160 do_test fkey2-1.2.0 {
 161   execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
 162 } {}
 163 foreach {tn zSql res} $FkeySimpleTests {
 164   do_test fkey2-1.2.$tn { catchsql $zSql } $res
 165   do_test fkey2-1.2.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
 166   do_test fkey2-1.2.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
 167   do_test fkey2-1.2.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
 168   do_test fkey2-1.2.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
 169   do_test fkey2-1.2.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
 170   do_test fkey2-1.2.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
 171 }
 172 drop_all_tables
 173
 174 do_test fkey2-1.3.0 {
 175   execsql [string map {/D/ {}} $FkeySimpleSchema]
 176   execsql { PRAGMA count_changes = 1 }
 177 } {}
 178 foreach {tn zSql res} $FkeySimpleTests {
 179   if {$res == "0 {}"} { set res {0 1} }
 180   do_test fkey2-1.3.$tn { catchsql $zSql } $res
 181   do_test fkey2-1.3.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
 182   do_test fkey2-1.3.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
 183   do_test fkey2-1.3.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
 184   do_test fkey2-1.3.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
 185   do_test fkey2-1.3.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
 186   do_test fkey2-1.3.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
 187 }
 188 execsql { PRAGMA count_changes = 0 }
 189 drop_all_tables
 190
 191 do_test fkey2-1.4.0 {
 192   execsql [string map {/D/ {}} $FkeySimpleSchema]
 193   execsql { PRAGMA count_changes = 1 }
 194 } {}
 195 foreach {tn zSql res} $FkeySimpleTests {
 196   if {$res == "0 {}"} { set res {0 1} }
 197   execsql BEGIN
 198   do_test fkey2-1.4.$tn { catchsql $zSql } $res
 199   execsql COMMIT
 200 }
 201 execsql { PRAGMA count_changes = 0 }
 202 drop_all_tables
 203
 204 # Special test: When the parent key is an IPK, make sure the affinity of
 205 # the IPK is not applied to the child key value before it is inserted
 206 # into the child table.
 207 do_test fkey2-1.5.1 {
 208   execsql {
 209     CREATE TABLE i(i INTEGER PRIMARY KEY);
 210     CREATE TABLE j(j REFERENCES i);
 211     INSERT INTO i VALUES(35);
 212     INSERT INTO j VALUES('35.0');
 213     SELECT j, typeof(j) FROM j;
 214   }
 215 } {35.0 text}
 216 do_test fkey2-1.5.2 {
 217   catchsql { DELETE FROM i }
 218 } {1 {FOREIGN KEY constraint failed}}
 219
 220 # Same test using a regular primary key with integer affinity.
 221 drop_all_tables
 222 do_test fkey2-1.6.1 {
 223   execsql {
 224     CREATE TABLE i(i INT UNIQUE);
 225     CREATE TABLE j(j REFERENCES i(i));
 226     INSERT INTO i VALUES('35.0');
 227     INSERT INTO j VALUES('35.0');
 228     SELECT j, typeof(j) FROM j;
 229     SELECT i, typeof(i) FROM i;
 230   }
 231 } {35.0 text 35 integer}
 232 do_test fkey2-1.6.2 {
 233   catchsql { DELETE FROM i }
 234 } {1 {FOREIGN KEY constraint failed}}
 235
 236 # Use a collation sequence on the parent key.
 237 drop_all_tables
 238 do_test fkey2-1.7.1 {
 239   execsql {
 240     CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
 241     CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
 242     INSERT INTO i VALUES('SQLite');
 243     INSERT INTO j VALUES('sqlite');
 244   }
 245   catchsql { DELETE FROM i }
 246 } {1 {FOREIGN KEY constraint failed}}
 247
 248 # Use the parent key collation even if it is default and the child key
 249 # has an explicit value.
 250 drop_all_tables
 251 do_test fkey2-1.7.2 {
 252   execsql {
 253     CREATE TABLE i(i TEXT PRIMARY KEY);        -- Colseq is "BINARY"
 254     CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
 255     INSERT INTO i VALUES('SQLite');
 256   }
 257   catchsql { INSERT INTO j VALUES('sqlite') }
 258 } {1 {FOREIGN KEY constraint failed}}
 259 do_test fkey2-1.7.3 {
 260   execsql {
 261     INSERT INTO i VALUES('sqlite');
 262     INSERT INTO j VALUES('sqlite');
 263     DELETE FROM i WHERE i = 'SQLite';
 264   }
 265   catchsql { DELETE FROM i WHERE i = 'sqlite' }
 266 } {1 {FOREIGN KEY constraint failed}}
 267
 268 #-------------------------------------------------------------------------
 269 # This section (test cases fkey2-2.*) contains tests to check that the
 270 # deferred foreign key constraint logic works.
 271 #
 272 proc fkey2-2-test {tn nocommit sql {res {}}} {
 273   if {$res eq "FKV"} {
 274     set expected {1 {FOREIGN KEY constraint failed}}
 275   } else {
 276     set expected [list 0 $res]
 277   }
 278   do_test fkey2-2.$tn [list catchsql $sql] $expected
 279   if {$nocommit} {
 280     do_test fkey2-2.${tn}c {
 281       catchsql COMMIT
 282     } {1 {FOREIGN KEY constraint failed}}
 283   }
 284 }
 285
 286 fkey2-2-test 1 0 {
 287   CREATE TABLE node(
 288     nodeid PRIMARY KEY,
 289     parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
 290   );
 291   CREATE TABLE leaf(
 292     cellid PRIMARY KEY,
 293     parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
 294   );
 295 }
 296
 297 fkey2-2-test 1  0 "INSERT INTO node VALUES(1, 0)"       FKV
 298 fkey2-2-test 2  0 "BEGIN"
 299 fkey2-2-test 3  1   "INSERT INTO node VALUES(1, 0)"
 300 fkey2-2-test 4  0   "UPDATE node SET parent = NULL"
 301 fkey2-2-test 5  0 "COMMIT"
 302 fkey2-2-test 6  0 "SELECT * FROM node" {1 {}}
 303
 304 fkey2-2-test 7  0 "BEGIN"
 305 fkey2-2-test 8  1   "INSERT INTO leaf VALUES('a', 2)"
 306 fkey2-2-test 9  1   "INSERT INTO node VALUES(2, 0)"
 307 fkey2-2-test 10 0   "UPDATE node SET parent = 1 WHERE nodeid = 2"
 308 fkey2-2-test 11 0 "COMMIT"
 309 fkey2-2-test 12 0 "SELECT * FROM node" {1 {} 2 1}
 310 fkey2-2-test 13 0 "SELECT * FROM leaf" {a 2}
 311
 312 fkey2-2-test 14 0 "BEGIN"
 313 fkey2-2-test 15 1   "DELETE FROM node WHERE nodeid = 2"
 314 fkey2-2-test 16 0   "INSERT INTO node VALUES(2, NULL)"
 315 fkey2-2-test 17 0 "COMMIT"
 316 fkey2-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}}
 317 fkey2-2-test 19 0 "SELECT * FROM leaf" {a 2}
 318
 319 fkey2-2-test 20 0 "BEGIN"
 320 fkey2-2-test 21 0   "INSERT INTO leaf VALUES('b', 1)"
 321 fkey2-2-test 22 0   "SAVEPOINT save"
 322 fkey2-2-test 23 0     "DELETE FROM node WHERE nodeid = 1"
 323 fkey2-2-test 24 0   "ROLLBACK TO save"
 324 fkey2-2-test 25 0 "COMMIT"
 325 fkey2-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}}
 326 fkey2-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1}
 327
 328 fkey2-2-test 28 0 "BEGIN"
 329 fkey2-2-test 29 0   "INSERT INTO leaf VALUES('c', 1)"
 330 fkey2-2-test 30 0   "SAVEPOINT save"
 331 fkey2-2-test 31 0     "DELETE FROM node WHERE nodeid = 1"
 332 fkey2-2-test 32 1   "RELEASE save"
 333 fkey2-2-test 33 1   "DELETE FROM leaf WHERE cellid = 'b'"
 334 fkey2-2-test 34 0   "DELETE FROM leaf WHERE cellid = 'c'"
 335 fkey2-2-test 35 0 "COMMIT"
 336 fkey2-2-test 36 0 "SELECT * FROM node" {2 {}}
 337 fkey2-2-test 37 0 "SELECT * FROM leaf" {a 2}
 338
 339 fkey2-2-test 38 0 "SAVEPOINT outer"
 340 fkey2-2-test 39 1   "INSERT INTO leaf VALUES('d', 3)"
 341 fkey2-2-test 40 1 "RELEASE outer"    FKV
 342 fkey2-2-test 41 1   "INSERT INTO leaf VALUES('e', 3)"
 343 fkey2-2-test 42 0   "INSERT INTO node VALUES(3, 2)"
 344 fkey2-2-test 43 0 "RELEASE outer"
 345
 346 fkey2-2-test 44 0 "SAVEPOINT outer"
 347 fkey2-2-test 45 1   "DELETE FROM node WHERE nodeid=3"
 348 fkey2-2-test 47 0   "INSERT INTO node VALUES(3, 2)"
 349 fkey2-2-test 48 0 "ROLLBACK TO outer"
 350 fkey2-2-test 49 0 "RELEASE outer"
 351
 352 fkey2-2-test 50 0 "SAVEPOINT outer"
 353 fkey2-2-test 51 1   "INSERT INTO leaf VALUES('f', 4)"
 354 fkey2-2-test 52 1   "SAVEPOINT inner"
 355 fkey2-2-test 53 1     "INSERT INTO leaf VALUES('g', 4)"
 356 fkey2-2-test 54 1  "RELEASE outer"   FKV
 357 fkey2-2-test 55 1   "ROLLBACK TO inner"
 358 fkey2-2-test 56 0  "COMMIT"          FKV
 359 fkey2-2-test 57 0   "INSERT INTO node VALUES(4, NULL)"
 360 fkey2-2-test 58 0 "RELEASE outer"
 361 fkey2-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}}
 362 fkey2-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4}
 363
 364 # The following set of tests check that if a statement that affects
 365 # multiple rows violates some foreign key constraints, then strikes a
 366 # constraint that causes the statement-transaction to be rolled back,
 367 # the deferred constraint counter is correctly reset to the value it
 368 # had before the statement-transaction was opened.
 369 #
 370 fkey2-2-test 61 0 "BEGIN"
 371 fkey2-2-test 62 0   "DELETE FROM leaf"
 372 fkey2-2-test 63 0   "DELETE FROM node"
 373 fkey2-2-test 64 1   "INSERT INTO leaf VALUES('a', 1)"
 374 fkey2-2-test 65 1   "INSERT INTO leaf VALUES('b', 2)"
 375 fkey2-2-test 66 1   "INSERT INTO leaf VALUES('c', 1)"
 376 do_test fkey2-2-test-67 {
 377   catchsql          "INSERT INTO node SELECT parent, 3 FROM leaf"
 378 } {1 {UNIQUE constraint failed: node.nodeid}}
 379 fkey2-2-test 68 0 "COMMIT"           FKV
 380 fkey2-2-test 69 1   "INSERT INTO node VALUES(1, NULL)"
 381 fkey2-2-test 70 0   "INSERT INTO node VALUES(2, NULL)"
 382 fkey2-2-test 71 0 "COMMIT"
 383
 384 fkey2-2-test 72 0 "BEGIN"
 385 fkey2-2-test 73 1   "DELETE FROM node"
 386 fkey2-2-test 74 0   "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf"
 387 fkey2-2-test 75 0 "COMMIT"
 388
 389 #-------------------------------------------------------------------------
 390 # Test cases fkey2-3.* test that a program that executes foreign key
 391 # actions (CASCADE, SET DEFAULT, SET NULL etc.) or tests FK constraints
 392 # opens a statement transaction if required.
 393 #
 394 # fkey2-3.1.*: Test UPDATE statements.
 395 # fkey2-3.2.*: Test DELETE statements.
 396 #
 397 drop_all_tables
 398 do_test fkey2-3.1.1 {
 399   execsql {
 400     CREATE TABLE ab(a PRIMARY KEY, b);
 401     CREATE TABLE cd(
 402       c PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE,
 403       d
 404     );
 405     CREATE TABLE ef(
 406       e REFERENCES cd ON UPDATE CASCADE,
 407       f, CHECK (e!=5)
 408     );
 409   }
 410 } {}
 411 do_test fkey2-3.1.2 {
 412   execsql {
 413     INSERT INTO ab VALUES(1, 'b');
 414     INSERT INTO cd VALUES(1, 'd');
 415     INSERT INTO ef VALUES(1, 'e');
 416   }
 417 } {}
 418 do_test fkey2-3.1.3 {
 419   catchsql { UPDATE ab SET a = 5 }
 420 } {1 {CHECK constraint failed: e!=5}}
 421 do_test fkey2-3.1.4 {
 422   execsql { SELECT * FROM ab }
 423 } {1 b}
 424 do_test fkey2-3.1.4 {
 425   execsql BEGIN;
 426   catchsql { UPDATE ab SET a = 5 }
 427 } {1 {CHECK constraint failed: e!=5}}
 428 do_test fkey2-3.1.5 {
 429   execsql COMMIT;
 430   execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
 431 } {1 b 1 d 1 e}
 432
 433 do_test fkey2-3.2.1 {
 434   execsql BEGIN;
 435   catchsql { DELETE FROM ab }
 436 } {1 {FOREIGN KEY constraint failed}}
 437 do_test fkey2-3.2.2 {
 438   execsql COMMIT
 439   execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
 440 } {1 b 1 d 1 e}
 441
 442 #-------------------------------------------------------------------------
 443 # Test cases fkey2-4.* test that recursive foreign key actions
 444 # (i.e. CASCADE) are allowed even if recursive triggers are disabled.
 445 #
 446 drop_all_tables
 447 do_test fkey2-4.1 {
 448   execsql {
 449     CREATE TABLE t1(
 450       node PRIMARY KEY,
 451       parent REFERENCES t1 ON DELETE CASCADE
 452     );
 453     CREATE TABLE t2(node PRIMARY KEY, parent);
 454     CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN
 455       DELETE FROM t2 WHERE parent = old.node;
 456     END;
 457     INSERT INTO t1 VALUES(1, NULL);
 458     INSERT INTO t1 VALUES(2, 1);
 459     INSERT INTO t1 VALUES(3, 1);
 460     INSERT INTO t1 VALUES(4, 2);
 461     INSERT INTO t1 VALUES(5, 2);
 462     INSERT INTO t1 VALUES(6, 3);
 463     INSERT INTO t1 VALUES(7, 3);
 464     INSERT INTO t2 SELECT * FROM t1;
 465   }
 466 } {}
 467 do_test fkey2-4.2 {
 468   execsql { PRAGMA recursive_triggers = off }
 469   execsql {
 470     BEGIN;
 471       DELETE FROM t1 WHERE node = 1;
 472       SELECT node FROM t1;
 473   }
 474 } {}
 475 do_test fkey2-4.3 {
 476   execsql {
 477       DELETE FROM t2 WHERE node = 1;
 478       SELECT node FROM t2;
 479     ROLLBACK;
 480   }
 481 } {4 5 6 7}
 482 do_test fkey2-4.4 {
 483   execsql { PRAGMA recursive_triggers = on }
 484   execsql {
 485     BEGIN;
 486       DELETE FROM t1 WHERE node = 1;
 487       SELECT node FROM t1;
 488   }
 489 } {}
 490 do_test fkey2-4.3 {
 491   execsql {
 492       DELETE FROM t2 WHERE node = 1;
 493       SELECT node FROM t2;
 494     ROLLBACK;
 495   }
 496 } {}
 497
 498 #-------------------------------------------------------------------------
 499 # Test cases fkey2-5.* verify that the incremental blob API may not
 500 # write to a foreign key column while foreign-keys are enabled.
 501 #
 502 drop_all_tables
 503 ifcapable incrblob {
 504   do_test fkey2-5.1 {
 505     execsql {
 506       CREATE TABLE t1(a PRIMARY KEY, b);
 507       CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1(a));
 508       INSERT INTO t1 VALUES('hello', 'world');
 509       INSERT INTO t2 VALUES('key', 'hello');
 510     }
 511   } {}
 512   do_test fkey2-5.2 {
 513     set rc [catch { set fd [db incrblob t2 b 1] } msg]
 514     list $rc $msg
 515   } {1 {cannot open foreign key column for writing}}
 516   do_test fkey2-5.3 {
 517     set rc [catch { set fd [db incrblob -readonly t2 b 1] } msg]
 518     close $fd
 519     set rc
 520   } {0}
 521   do_test fkey2-5.4 {
 522     execsql { PRAGMA foreign_keys = off }
 523     set rc [catch { set fd [db incrblob t2 b 1] } msg]
 524     close $fd
 525     set rc
 526   } {0}
 527   do_test fkey2-5.5 {
 528     execsql { PRAGMA foreign_keys = on }
 529   } {}
 530 }
 531
 532 drop_all_tables
 533 ifcapable vacuum {
 534   do_test fkey2-6.1 {
 535     execsql {
 536       CREATE TABLE t1(a REFERENCES t2(c), b);
 537       CREATE TABLE t2(c UNIQUE, b);
 538       INSERT INTO t2 VALUES(1, 2);
 539       INSERT INTO t1 VALUES(1, 2);
 540       VACUUM;
 541     }
 542   } {}
 543 }
 544
 545 #-------------------------------------------------------------------------
 546 # Test that it is possible to use an INTEGER PRIMARY KEY as the child key
 547 # of a foreign constraint.
 548 #
 549 drop_all_tables
 550 do_test fkey2-7.1 {
 551   execsql {
 552     CREATE TABLE t1(a PRIMARY KEY, b);
 553     CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b);
 554   }
 555 } {}
 556 do_test fkey2-7.2 {
 557   catchsql { INSERT INTO t2 VALUES(1, 'A'); }
 558 } {1 {FOREIGN KEY constraint failed}}
 559 do_test fkey2-7.3 {
 560   execsql {
 561     INSERT INTO t1 VALUES(1, 2);
 562     INSERT INTO t1 VALUES(2, 3);
 563     INSERT INTO t2 VALUES(1, 'A');
 564   }
 565 } {}
 566 do_test fkey2-7.4 {
 567   execsql { UPDATE t2 SET c = 2 }
 568 } {}
 569 do_test fkey2-7.5 {
 570   catchsql { UPDATE t2 SET c = 3 }
 571 } {1 {FOREIGN KEY constraint failed}}
 572 do_test fkey2-7.6 {
 573   catchsql { DELETE FROM t1 WHERE a = 2 }
 574 } {1 {FOREIGN KEY constraint failed}}
 575 do_test fkey2-7.7 {
 576   execsql { DELETE FROM t1 WHERE a = 1 }
 577 } {}
 578 do_test fkey2-7.8 {
 579   catchsql { UPDATE t1 SET a = 3 }
 580 } {1 {FOREIGN KEY constraint failed}}
 581 do_test fkey2-7.9 {
 582   catchsql { UPDATE t2 SET rowid = 3 }
 583 } {1 {FOREIGN KEY constraint failed}}
 584
 585 #-------------------------------------------------------------------------
 586 # Test that it is not possible to enable/disable FK support while a
 587 # transaction is open.
 588 #
 589 drop_all_tables
 590 proc fkey2-8-test {tn zSql value} {
 591   do_test fkey-2.8.$tn.1 [list execsql $zSql] {}
 592   do_test fkey-2.8.$tn.2 { execsql "PRAGMA foreign_keys" } $value
 593 }
 594 fkey2-8-test  1 { PRAGMA foreign_keys = 0     } 0
 595 fkey2-8-test  2 { PRAGMA foreign_keys = 1     } 1
 596 fkey2-8-test  3 { BEGIN                       } 1
 597 fkey2-8-test  4 { PRAGMA foreign_keys = 0     } 1
 598 fkey2-8-test  5 { COMMIT                      } 1
 599 fkey2-8-test  6 { PRAGMA foreign_keys = 0     } 0
 600 fkey2-8-test  7 { BEGIN                       } 0
 601 fkey2-8-test  8 { PRAGMA foreign_keys = 1     } 0
 602 fkey2-8-test  9 { COMMIT                      } 0
 603 fkey2-8-test 10 { PRAGMA foreign_keys = 1     } 1
 604 fkey2-8-test 11 { PRAGMA foreign_keys = off   } 0
 605 fkey2-8-test 12 { PRAGMA foreign_keys = on    } 1
 606 fkey2-8-test 13 { PRAGMA foreign_keys = no    } 0
 607 fkey2-8-test 14 { PRAGMA foreign_keys = yes   } 1
 608 fkey2-8-test 15 { PRAGMA foreign_keys = false } 0
 609 fkey2-8-test 16 { PRAGMA foreign_keys = true  } 1
 610
 611 #-------------------------------------------------------------------------
 612 # The following tests, fkey2-9.*, test SET DEFAULT actions.
 613 #
 614 drop_all_tables
 615 do_test fkey2-9.1.1 {
 616   execsql {
 617     CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
 618     CREATE TABLE t2(
 619       c INTEGER PRIMARY KEY,
 620       d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT
 621     );
 622     DELETE FROM t1;
 623   }
 624 } {}
 625 do_test fkey2-9.1.2 {
 626   execsql {
 627     INSERT INTO t1 VALUES(1, 'one');
 628     INSERT INTO t1 VALUES(2, 'two');
 629     INSERT INTO t2 VALUES(1, 2);
 630     SELECT * FROM t2;
 631     DELETE FROM t1 WHERE a = 2;
 632     SELECT * FROM t2;
 633   }
 634 } {1 2 1 1}
 635 do_test fkey2-9.1.3 {
 636   execsql {
 637     INSERT INTO t1 VALUES(2, 'two');
 638     UPDATE t2 SET d = 2;
 639     DELETE FROM t1 WHERE a = 1;
 640     SELECT * FROM t2;
 641   }
 642 } {1 2}
 643 do_test fkey2-9.1.4 {
 644   execsql { SELECT * FROM t1 }
 645 } {2 two}
 646 do_test fkey2-9.1.5 {
 647   catchsql { DELETE FROM t1 }
 648 } {1 {FOREIGN KEY constraint failed}}
 649
 650 do_test fkey2-9.2.1 {
 651   execsql {
 652     CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
 653     CREATE TABLE cc(d DEFAULT 3, e DEFAULT 1, f DEFAULT 2,
 654         FOREIGN KEY(f, d) REFERENCES pp
 655         ON UPDATE SET DEFAULT
 656         ON DELETE SET NULL
 657     );
 658     INSERT INTO pp VALUES(1, 2, 3);
 659     INSERT INTO pp VALUES(4, 5, 6);
 660     INSERT INTO pp VALUES(7, 8, 9);
 661   }
 662 } {}
 663 do_test fkey2-9.2.2 {
 664   execsql {
 665     INSERT INTO cc VALUES(6, 'A', 5);
 666     INSERT INTO cc VALUES(6, 'B', 5);
 667     INSERT INTO cc VALUES(9, 'A', 8);
 668     INSERT INTO cc VALUES(9, 'B', 8);
 669     UPDATE pp SET b = 1 WHERE a = 7;
 670     SELECT * FROM cc;
 671   }
 672 } {6 A 5 6 B 5 3 A 2 3 B 2}
 673 do_test fkey2-9.2.3 {
 674   execsql {
 675     DELETE FROM pp WHERE a = 4;
 676     SELECT * FROM cc;
 677   }
 678 } {{} A {} {} B {} 3 A 2 3 B 2}
 679 do_execsql_test fkey2-9.3.0 {
 680   CREATE TABLE t3(x PRIMARY KEY REFERENCES t3 ON DELETE SET NULL);
 681   INSERT INTO t3(x) VALUES(12345);
 682   DROP TABLE t3;
 683 } {}
 684
 685 #-------------------------------------------------------------------------
 686 # The following tests, fkey2-10.*, test "foreign key mismatch" and
 687 # other errors.
 688 #
 689 set tn 0
 690 foreach zSql [list {
 691   CREATE TABLE p(a PRIMARY KEY, b);
 692   CREATE TABLE c(x REFERENCES p(c));
 693 } {
 694   CREATE TABLE c(x REFERENCES v(y));
 695   CREATE VIEW v AS SELECT x AS y FROM c;
 696 } {
 697   CREATE TABLE p(a, b, PRIMARY KEY(a, b));
 698   CREATE TABLE c(x REFERENCES p);
 699 } {
 700   CREATE TABLE p(a COLLATE binary, b);
 701   CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
 702   CREATE TABLE c(x REFERENCES p(a));
 703 }] {
 704   drop_all_tables
 705   do_test fkey2-10.1.[incr tn] {
 706     execsql $zSql
 707     catchsql { INSERT INTO c DEFAULT VALUES }
 708   } {/1 {foreign key mismatch - "c" referencing "."}/}
 709 }
 710
 711 # "rowid" cannot be used as part of a child or parent key definition
 712 # unless it happens to be the name of an explicitly declared column.
 713 #
 714 do_test fkey2-10.2.1 {
 715   drop_all_tables
 716   catchsql {
 717     CREATE TABLE t1(a PRIMARY KEY, b);
 718     CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a));
 719   }
 720 } {1 {unknown column "rowid" in foreign key definition}}
 721 do_test fkey2-10.2.2 {
 722   drop_all_tables
 723   catchsql {
 724     CREATE TABLE t1(a PRIMARY KEY, b);
 725     CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a));
 726   }
 727 } {0 {}}
 728 do_test fkey2-10.2.1 {
 729   drop_all_tables
 730   catchsql {
 731     CREATE TABLE t1(a, b);
 732     CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
 733     INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
 734     INSERT INTO t2 VALUES(1, 1);
 735   }
 736 } {1 {foreign key mismatch - "t2" referencing "t1"}}
 737 do_test fkey2-10.2.2 {
 738   drop_all_tables
 739   catchsql {
 740     CREATE TABLE t1(rowid PRIMARY KEY, b);
 741     CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
 742     INSERT INTO t1(rowid, b) VALUES(1, 1);
 743     INSERT INTO t2 VALUES(1, 1);
 744   }
 745 } {0 {}}
 746
 747
 748 #-------------------------------------------------------------------------
 749 # The following tests, fkey2-11.*, test CASCADE actions.
 750 #
 751 drop_all_tables
 752 do_test fkey2-11.1.1 {
 753   execsql {
 754     CREATE TABLE t1(a INTEGER PRIMARY KEY, b, rowid, _rowid_, oid);
 755     CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
 756
 757     INSERT INTO t1 VALUES(10, 100, 'abc', 'def', 'ghi');
 758     INSERT INTO t2 VALUES(10, 100);
 759     UPDATE t1 SET a = 15;
 760     SELECT * FROM t2;
 761   }
 762 } {15 100}
 763
 764 #-------------------------------------------------------------------------
 765 # The following tests, fkey2-12.*, test RESTRICT actions.
 766 #
 767 drop_all_tables
 768 do_test fkey2-12.1.1 {
 769   execsql {
 770     CREATE TABLE t1(a, b PRIMARY KEY);
 771     CREATE TABLE t2(
 772       x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED
 773     );
 774     INSERT INTO t1 VALUES(1, 'one');
 775     INSERT INTO t1 VALUES(2, 'two');
 776     INSERT INTO t1 VALUES(3, 'three');
 777   }
 778 } {}
 779 do_test fkey2-12.1.2 {
 780   execsql "BEGIN"
 781   execsql "INSERT INTO t2 VALUES('two')"
 782 } {}
 783 do_test fkey2-12.1.3 {
 784   execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'"
 785 } {}
 786 do_test fkey2-12.1.4 {
 787   catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'"
 788 } {1 {FOREIGN KEY constraint failed}}
 789 do_test fkey2-12.1.5 {
 790   execsql "DELETE FROM t1 WHERE b = 'two'"
 791 } {}
 792 do_test fkey2-12.1.6 {
 793   catchsql "COMMIT"
 794 } {1 {FOREIGN KEY constraint failed}}
 795 do_test fkey2-12.1.7 {
 796   execsql {
 797     INSERT INTO t1 VALUES(2, 'two');
 798     COMMIT;
 799   }
 800 } {}
 801
 802 drop_all_tables
 803 do_test fkey2-12.2.1 {
 804   execsql {
 805     CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY);
 806     CREATE TRIGGER tt1 AFTER DELETE ON t1
 807       WHEN EXISTS ( SELECT 1 FROM t2 WHERE old.x = y )
 808     BEGIN
 809       INSERT INTO t1 VALUES(old.x);
 810     END;
 811     CREATE TABLE t2(y REFERENCES t1);
 812     INSERT INTO t1 VALUES('A');
 813     INSERT INTO t1 VALUES('B');
 814     INSERT INTO t2 VALUES('a');
 815     INSERT INTO t2 VALUES('b');
 816
 817     SELECT * FROM t1;
 818     SELECT * FROM t2;
 819   }
 820 } {A B a b}
 821 do_test fkey2-12.2.2 {
 822   execsql { DELETE FROM t1 }
 823   execsql {
 824     SELECT * FROM t1;
 825     SELECT * FROM t2;
 826   }
 827 } {A B a b}
 828 do_test fkey2-12.2.3 {
 829   execsql {
 830     DROP TABLE t2;
 831     CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT);
 832     INSERT INTO t2 VALUES('a');
 833     INSERT INTO t2 VALUES('b');
 834   }
 835   catchsql { DELETE FROM t1 }
 836 } {1 {FOREIGN KEY constraint failed}}
 837 do_test fkey2-12.2.4 {
 838   execsql {
 839     SELECT * FROM t1;
 840     SELECT * FROM t2;
 841   }
 842 } {A B a b}
 843
 844 drop_all_tables
 845 do_test fkey2-12.3.1 {
 846   execsql {
 847     CREATE TABLE up(
 848       c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
 849       c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
 850       c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
 851       c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
 852       PRIMARY KEY(c34, c35)
 853     );
 854     CREATE TABLE down(
 855       c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
 856       c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
 857       c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
 858       c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
 859       FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE
 860     );
 861   }
 862 } {}
 863 do_test fkey2-12.3.2 {
 864   execsql {
 865     INSERT INTO up(c34, c35) VALUES('yes', 'no');
 866     INSERT INTO down(c39, c38) VALUES('yes', 'no');
 867     UPDATE up SET c34 = 'possibly';
 868     SELECT c38, c39 FROM down;
 869     DELETE FROM down;
 870   }
 871 } {no possibly}
 872 do_test fkey2-12.3.3 {
 873   catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') }
 874 } {1 {FOREIGN KEY constraint failed}}
 875 do_test fkey2-12.3.4 {
 876   execsql {
 877     INSERT INTO up(c34, c35) VALUES('yes', 'no');
 878     INSERT INTO down(c39, c38) VALUES('yes', 'no');
 879   }
 880   catchsql { DELETE FROM up WHERE c34 = 'yes' }
 881 } {1 {FOREIGN KEY constraint failed}}
 882 do_test fkey2-12.3.5 {
 883   execsql {
 884     DELETE FROM up WHERE c34 = 'possibly';
 885     SELECT c34, c35 FROM up;
 886     SELECT c39, c38 FROM down;
 887   }
 888 } {yes no yes no}
 889
 890 #-------------------------------------------------------------------------
 891 # The following tests, fkey2-13.*, test that FK processing is performed
 892 # when rows are REPLACEd.
 893 #
 894 drop_all_tables
 895 do_test fkey2-13.1.1 {
 896   execsql {
 897     CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c));
 898     CREATE TABLE cc(d, e, f UNIQUE, FOREIGN KEY(d, e) REFERENCES pp);
 899     INSERT INTO pp VALUES(1, 2, 3);
 900     INSERT INTO cc VALUES(2, 3, 1);
 901   }
 902 } {}
 903 foreach {tn stmt} {
 904   1   "REPLACE INTO pp VALUES(1, 4, 5)"
 905   2   "REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 3, 4)"
 906 } {
 907   do_test fkey2-13.1.$tn.1 {
 908     catchsql $stmt
 909   } {1 {FOREIGN KEY constraint failed}}
 910   do_test fkey2-13.1.$tn.2 {
 911     execsql {
 912       SELECT * FROM pp;
 913       SELECT * FROM cc;
 914     }
 915   } {1 2 3 2 3 1}
 916   do_test fkey2-13.1.$tn.3 {
 917     execsql BEGIN;
 918     catchsql $stmt
 919   } {1 {FOREIGN KEY constraint failed}}
 920   do_test fkey2-13.1.$tn.4 {
 921     execsql {
 922       COMMIT;
 923       SELECT * FROM pp;
 924       SELECT * FROM cc;
 925     }
 926   } {1 2 3 2 3 1}
 927 }
 928 do_test fkey2-13.1.3 {
 929   execsql {
 930     REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 2, 3);
 931     SELECT rowid, * FROM pp;
 932     SELECT * FROM cc;
 933   }
 934 } {1 2 2 3 2 3 1}
 935 do_test fkey2-13.1.4 {
 936   execsql {
 937     REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3);
 938     SELECT rowid, * FROM pp;
 939     SELECT * FROM cc;
 940   }
 941 } {2 2 2 3 2 3 1}
 942
 943 #-------------------------------------------------------------------------
 944 # The following tests, fkey2-14.*, test that the "DROP TABLE" and "ALTER
 945 # TABLE" commands work as expected wrt foreign key constraints.
 946 #
 947 # fkey2-14.1*: ALTER TABLE ADD COLUMN
 948 # fkey2-14.2*: ALTER TABLE RENAME TABLE
 949 # fkey2-14.3*: DROP TABLE
 950 #
 951 drop_all_tables
 952 ifcapable altertable {
 953   do_test fkey2-14.1.1 {
 954     # Adding a column with a REFERENCES clause is not supported.
 955     execsql {
 956       CREATE TABLE t1(a PRIMARY KEY);
 957       CREATE TABLE t2(a, b);
 958       INSERT INTO t2 VALUES(1,2);
 959     }
 960     catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
 961   } {0 {}}
 962   do_test fkey2-14.1.2 {
 963     catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
 964   } {0 {}}
 965   do_test fkey2-14.1.3 {
 966     catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
 967   } {0 {}}
 968   do_test fkey2-14.1.4 {
 969     catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
 970   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
 971   do_test fkey2-14.1.5 {
 972     catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
 973   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
 974   do_test fkey2-14.1.6 {
 975     execsql {
 976       PRAGMA foreign_keys = off;
 977       ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
 978       PRAGMA foreign_keys = on;
 979       SELECT sql FROM sqlite_master WHERE name='t2';
 980     }
 981   } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
 982
 983
 984   # Test the sqlite_rename_parent() function directly.
 985   #
 986   proc test_rename_parent {zCreate zOld zNew} {
 987     db eval {SELECT sqlite_rename_table(
 988         'main', 'table', 't1', $zCreate, $zOld, $zNew, 0
 989     )}
 990   }
 991   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
 992   do_test fkey2-14.2.1.1 {
 993     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
 994   } {{CREATE TABLE t1(a REFERENCES "t3")}}
 995   do_test fkey2-14.2.1.2 {
 996     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
 997   } {{CREATE TABLE t1(a REFERENCES t2)}}
 998   do_test fkey2-14.2.1.3 {
 999     test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
1000   } {{CREATE TABLE t1(a REFERENCES "t3")}}
1001   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
1002
1003   # Test ALTER TABLE RENAME TABLE a bit.
1004   #
1005   do_test fkey2-14.2.2.1 {
1006     drop_all_tables
1007     execsql {
1008       CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1);
1009       CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
1010       CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
1011     }
1012     execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
1013   } [list \
1014     {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)}                     \
1015     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)}    \
1016     {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
1017   ]
1018   do_test fkey2-14.2.2.2 {
1019     execsql { ALTER TABLE t1 RENAME TO t4 }
1020     execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
1021   } [list \
1022     {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
1023     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
1024     {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
1025   ]
1026   do_test fkey2-14.2.2.3 {
1027     catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
1028   } {1 {FOREIGN KEY constraint failed}}
1029   do_test fkey2-14.2.2.4 {
1030     execsql { INSERT INTO t4 VALUES(1, NULL) }
1031   } {}
1032   do_test fkey2-14.2.2.5 {
1033     catchsql { UPDATE t4 SET b = 5 }
1034   } {1 {FOREIGN KEY constraint failed}}
1035   do_test fkey2-14.2.2.6 {
1036     catchsql { UPDATE t4 SET b = 1 }
1037   } {0 {}}
1038   do_test fkey2-14.2.2.7 {
1039     execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
1040   } {}
1041
1042   # Repeat for TEMP tables
1043   #
1044   drop_all_tables
1045   do_test fkey2-14.1tmp.1 {
1046     # Adding a column with a REFERENCES clause is not supported.
1047     execsql {
1048       CREATE TEMP TABLE t1(a PRIMARY KEY);
1049       CREATE TEMP TABLE t2(a, b);
1050       INSERT INTO temp.t2 VALUES(1,2);
1051     }
1052     catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
1053   } {0 {}}
1054   do_test fkey2-14.1tmp.2 {
1055     catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
1056   } {0 {}}
1057   do_test fkey2-14.1tmp.3 {
1058     catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
1059   } {0 {}}
1060   do_test fkey2-14.1tmp.4 {
1061     catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
1062   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1063   do_test fkey2-14.1tmp.5 {
1064     catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
1065   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1066   do_test fkey2-14.1tmp.6 {
1067     execsql {
1068       PRAGMA foreign_keys = off;
1069       ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
1070       PRAGMA foreign_keys = on;
1071       SELECT sql FROM temp.sqlite_master WHERE name='t2';
1072     }
1073   } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
1074
1075   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
1076   do_test fkey2-14.2tmp.1.1 {
1077     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
1078   } {{CREATE TABLE t1(a REFERENCES "t3")}}
1079   do_test fkey2-14.2tmp.1.2 {
1080     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
1081   } {{CREATE TABLE t1(a REFERENCES t2)}}
1082   do_test fkey2-14.2tmp.1.3 {
1083     test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
1084   } {{CREATE TABLE t1(a REFERENCES "t3")}}
1085   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
1086
1087   # Test ALTER TABLE RENAME TABLE a bit.
1088   #
1089   do_test fkey2-14.2tmp.2.1 {
1090     drop_all_tables
1091     execsql {
1092       CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1);
1093       CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
1094       CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
1095     }
1096     execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
1097   } [list \
1098     {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)}                     \
1099     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)}    \
1100     {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
1101   ]
1102   do_test fkey2-14.2tmp.2.2 {
1103     execsql { ALTER TABLE t1 RENAME TO t4 }
1104     execsql { SELECT sql FROM temp.sqlite_master WHERE type = 'table'}
1105   } [list \
1106     {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
1107     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
1108     {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
1109   ]
1110   do_test fkey2-14.2tmp.2.3 {
1111     catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
1112   } {1 {FOREIGN KEY constraint failed}}
1113   do_test fkey2-14.2tmp.2.4 {
1114     execsql { INSERT INTO t4 VALUES(1, NULL) }
1115   } {}
1116   do_test fkey2-14.2tmp.2.5 {
1117     catchsql { UPDATE t4 SET b = 5 }
1118   } {1 {FOREIGN KEY constraint failed}}
1119   do_test fkey2-14.2tmp.2.6 {
1120     catchsql { UPDATE t4 SET b = 1 }
1121   } {0 {}}
1122   do_test fkey2-14.2tmp.2.7 {
1123     execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
1124   } {}
1125
1126   # Repeat for ATTACH-ed tables
1127   #
1128   drop_all_tables
1129   do_test fkey2-14.1aux.1 {
1130     # Adding a column with a REFERENCES clause is not supported.
1131     execsql {
1132       ATTACH ':memory:' AS aux;
1133       CREATE TABLE aux.t1(a PRIMARY KEY);
1134       CREATE TABLE aux.t2(a, b);
1135       INSERT INTO aux.t2(a,b) VALUES(1,2);
1136     }
1137     catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
1138   } {0 {}}
1139   do_test fkey2-14.1aux.2 {
1140     catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
1141   } {0 {}}
1142   do_test fkey2-14.1aux.3 {
1143     catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
1144   } {0 {}}
1145   do_test fkey2-14.1aux.4 {
1146     catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
1147   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1148   do_test fkey2-14.1aux.5 {
1149     catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
1150   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1151   do_test fkey2-14.1aux.6 {
1152     execsql {
1153       PRAGMA foreign_keys = off;
1154       ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
1155       PRAGMA foreign_keys = on;
1156       SELECT sql FROM aux.sqlite_master WHERE name='t2';
1157     }
1158   } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
1159
1160   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
1161   do_test fkey2-14.2aux.1.1 {
1162     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
1163   } {{CREATE TABLE t1(a REFERENCES "t3")}}
1164   do_test fkey2-14.2aux.1.2 {
1165     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
1166   } {{CREATE TABLE t1(a REFERENCES t2)}}
1167   do_test fkey2-14.2aux.1.3 {
1168     test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
1169   } {{CREATE TABLE t1(a REFERENCES "t3")}}
1170   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
1171
1172   # Test ALTER TABLE RENAME TABLE a bit.
1173   #
1174   do_test fkey2-14.2aux.2.1 {
1175     drop_all_tables
1176     execsql {
1177       CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1);
1178       CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
1179       CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
1180     }
1181     execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
1182   } [list \
1183     {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)}                     \
1184     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)}    \
1185     {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
1186   ]
1187   do_test fkey2-14.2aux.2.2 {
1188     execsql { ALTER TABLE t1 RENAME TO t4 }
1189     execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
1190   } [list \
1191     {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
1192     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
1193     {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
1194   ]
1195   do_test fkey2-14.2aux.2.3 {
1196     catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
1197   } {1 {FOREIGN KEY constraint failed}}
1198   do_test fkey2-14.2aux.2.4 {
1199     execsql { INSERT INTO t4 VALUES(1, NULL) }
1200   } {}
1201   do_test fkey2-14.2aux.2.5 {
1202     catchsql { UPDATE t4 SET b = 5 }
1203   } {1 {FOREIGN KEY constraint failed}}
1204   do_test fkey2-14.2aux.2.6 {
1205     catchsql { UPDATE t4 SET b = 1 }
1206   } {0 {}}
1207   do_test fkey2-14.2aux.2.7 {
1208     execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
1209   } {}
1210 }
1211
1212 do_test fkey-2.14.3.1 {
1213   drop_all_tables
1214   execsql {
1215     CREATE TABLE t1(a, b REFERENCES nosuchtable);
1216     DROP TABLE t1;
1217   }
1218 } {}
1219 do_test fkey-2.14.3.2 {
1220   execsql {
1221     CREATE TABLE t1(a PRIMARY KEY, b);
1222     INSERT INTO t1 VALUES('a', 1);
1223     CREATE TABLE t2(x REFERENCES t1);
1224     INSERT INTO t2 VALUES('a');
1225   }
1226 } {}
1227 do_test fkey-2.14.3.3 {
1228   catchsql { DROP TABLE t1 }
1229 } {1 {FOREIGN KEY constraint failed}}
1230 do_test fkey-2.14.3.4 {
1231   execsql {
1232     DELETE FROM t2;
1233     DROP TABLE t1;
1234   }
1235 } {}
1236 do_test fkey-2.14.3.4 {
1237   catchsql { INSERT INTO t2 VALUES('x') }
1238 } {1 {no such table: main.t1}}
1239 do_test fkey-2.14.3.5 {
1240   execsql {
1241     CREATE TABLE t1(x PRIMARY KEY);
1242     INSERT INTO t1 VALUES('x');
1243   }
1244   execsql { INSERT INTO t2 VALUES('x') }
1245 } {}
1246 do_test fkey-2.14.3.6 {
1247   catchsql { DROP TABLE t1 }
1248 } {1 {FOREIGN KEY constraint failed}}
1249 do_test fkey-2.14.3.7 {
1250   execsql {
1251     DROP TABLE t2;
1252     DROP TABLE t1;
1253   }
1254 } {}
1255 do_test fkey-2.14.3.8 {
1256   execsql {
1257     CREATE TABLE pp(x, y, PRIMARY KEY(x, y));
1258     CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
1259   }
1260   catchsql { INSERT INTO cc VALUES(1, 2) }
1261 } {1 {foreign key mismatch - "cc" referencing "pp"}}
1262 do_test fkey-2.14.3.9 {
1263   execsql { DROP TABLE cc }
1264 } {}
1265 do_test fkey-2.14.3.10 {
1266   execsql {
1267     CREATE TABLE cc(a, b,
1268       FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED
1269     );
1270   }
1271   execsql {
1272     INSERT INTO pp VALUES('a', 'b');
1273     INSERT INTO cc VALUES('a', 'b');
1274     BEGIN;
1275       DROP TABLE pp;
1276       CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
1277       INSERT INTO pp VALUES(1, 'a', 'b');
1278     COMMIT;
1279   }
1280 } {}
1281 do_test fkey-2.14.3.11 {
1282   execsql {
1283     BEGIN;
1284       DROP TABLE cc;
1285       DROP TABLE pp;
1286     COMMIT;
1287   }
1288 } {}
1289 do_test fkey-2.14.3.12 {
1290   execsql {
1291     CREATE TABLE b1(a, b);
1292     CREATE TABLE b2(a, b REFERENCES b1);
1293     DROP TABLE b1;
1294   }
1295 } {}
1296 do_test fkey-2.14.3.13 {
1297   execsql {
1298     CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED);
1299     DROP TABLE b2;
1300   }
1301 } {}
1302
1303 # Test that nothing goes wrong when dropping a table that refers to a view.
1304 # Or dropping a view that an existing FK (incorrectly) refers to. Or either
1305 # of the above scenarios with a virtual table.
1306 drop_all_tables
1307 do_test fkey-2.14.4.1 {
1308   execsql {
1309     CREATE TABLE t1(x REFERENCES v);
1310     CREATE VIEW v AS SELECT * FROM t1;
1311   }
1312 } {}
1313 do_test fkey-2.14.4.2 {
1314   execsql {
1315     DROP VIEW v;
1316   }
1317 } {}
1318 ifcapable vtab {
1319   register_echo_module db
1320   do_test fkey-2.14.4.3 {
1321     execsql { CREATE VIRTUAL TABLE v USING echo(t1) }
1322   } {}
1323   do_test fkey-2.14.4.2 {
1324     execsql {
1325       DROP TABLE v;
1326     }
1327   } {}
1328 }
1329
1330 #-------------------------------------------------------------------------
1331 # The following tests, fkey2-15.*, test that unnecessary FK related scans
1332 # and lookups are avoided when the constraint counters are zero.
1333 #
1334 drop_all_tables
1335 proc execsqlS {zSql} {
1336   set ::sqlite_search_count 0
1337   set ::sqlite_found_count 0
1338   set res [uplevel [list execsql $zSql]]
1339   concat [expr $::sqlite_found_count + $::sqlite_search_count] $res
1340 }
1341 do_test fkey2-15.1.1 {
1342   execsql {
1343     CREATE TABLE pp(a PRIMARY KEY, b);
1344     CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED);
1345     INSERT INTO pp VALUES(1, 'one');
1346     INSERT INTO pp VALUES(2, 'two');
1347     INSERT INTO cc VALUES('neung', 1);
1348     INSERT INTO cc VALUES('song', 2);
1349   }
1350 } {}
1351 do_test fkey2-15.1.2 {
1352   execsqlS { INSERT INTO pp VALUES(3, 'three') }
1353 } {0}
1354 do_test fkey2-15.1.3 {
1355   execsql {
1356     BEGIN;
1357       INSERT INTO cc VALUES('see', 4);    -- Violates deferred constraint
1358   }
1359   execsqlS { INSERT INTO pp VALUES(5, 'five') }
1360 } {2}
1361 do_test fkey2-15.1.4 {
1362   execsql { DELETE FROM cc WHERE x = 'see' }
1363   execsqlS { INSERT INTO pp VALUES(6, 'six') }
1364 } {0}
1365 do_test fkey2-15.1.5 {
1366   execsql COMMIT
1367 } {}
1368 do_test fkey2-15.1.6 {
1369   execsql BEGIN
1370   execsqlS {
1371     DELETE FROM cc WHERE x = 'neung';
1372     ROLLBACK;
1373   }
1374 } {1}
1375 do_test fkey2-15.1.7 {
1376   execsql {
1377     BEGIN;
1378     DELETE FROM pp WHERE a = 2;
1379   }
1380   execsqlS {
1381     DELETE FROM cc WHERE x = 'neung';
1382     ROLLBACK;
1383   }
1384 } {2}
1385
1386 #-------------------------------------------------------------------------
1387 # This next block of tests, fkey2-16.*, test that rows that refer to
1388 # themselves may be inserted and deleted.
1389 #
1390 foreach {tn zSchema} {
1391   1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) }
1392   2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) }
1393   3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) }
1394 } {
1395   drop_all_tables
1396   do_test fkey2-16.1.$tn.1 {
1397     execsql $zSchema
1398     execsql { INSERT INTO self VALUES(13, 13) }
1399   } {}
1400   do_test fkey2-16.1.$tn.2 {
1401     execsql { UPDATE self SET a = 14, b = 14 }
1402   } {}
1403
1404   do_test fkey2-16.1.$tn.3 {
1405     catchsql { UPDATE self SET b = 15 }
1406   } {1 {FOREIGN KEY constraint failed}}
1407
1408   do_test fkey2-16.1.$tn.4 {
1409     catchsql { UPDATE self SET a = 15 }
1410   } {1 {FOREIGN KEY constraint failed}}
1411
1412   do_test fkey2-16.1.$tn.5 {
1413     catchsql { UPDATE self SET a = 15, b = 16 }
1414   } {1 {FOREIGN KEY constraint failed}}
1415
1416   do_test fkey2-16.1.$tn.6 {
1417     catchsql { UPDATE self SET a = 17, b = 17 }
1418   } {0 {}}
1419
1420   do_test fkey2-16.1.$tn.7 {
1421     execsql { DELETE FROM self }
1422   } {}
1423   do_test fkey2-16.1.$tn.8 {
1424     catchsql { INSERT INTO self VALUES(20, 21) }
1425   } {1 {FOREIGN KEY constraint failed}}
1426 }
1427
1428 #-------------------------------------------------------------------------
1429 # This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes"
1430 # is turned on statements that violate immediate FK constraints return
1431 # SQLITE_CONSTRAINT immediately, not after returning a number of rows.
1432 # Whereas statements that violate deferred FK constraints return the number
1433 # of rows before failing.
1434 #
1435 # Also test that rows modified by FK actions are not counted in either the
1436 # returned row count or the values returned by sqlite3_changes(). Like
1437 # trigger related changes, they are included in sqlite3_total_changes() though.
1438 #
1439 drop_all_tables
1440 do_test fkey2-17.1.1 {
1441   execsql { PRAGMA count_changes = 1 }
1442   execsql {
1443     CREATE TABLE one(a, b, c, UNIQUE(b, c));
1444     CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c));
1445     INSERT INTO one VALUES(1, 2, 3);
1446   }
1447 } {1}
1448 do_test fkey2-17.1.2 {
1449   set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy]
1450   sqlite3_step $STMT
1451 } {SQLITE_CONSTRAINT}
1452 verify_ex_errcode fkey2-17.1.2b SQLITE_CONSTRAINT_FOREIGNKEY
1453 ifcapable autoreset {
1454   do_test fkey2-17.1.3 {
1455     sqlite3_step $STMT
1456   } {SQLITE_CONSTRAINT}
1457   verify_ex_errcode fkey2-17.1.3b SQLITE_CONSTRAINT_FOREIGNKEY
1458 } else {
1459   do_test fkey2-17.1.3 {
1460     sqlite3_step $STMT
1461   } {SQLITE_MISUSE}
1462 }
1463 do_test fkey2-17.1.4 {
1464   sqlite3_finalize $STMT
1465 } {SQLITE_CONSTRAINT}
1466 verify_ex_errcode fkey2-17.1.4b SQLITE_CONSTRAINT_FOREIGNKEY
1467 do_test fkey2-17.1.5 {
1468   execsql {
1469     INSERT INTO one VALUES(2, 3, 4);
1470     INSERT INTO one VALUES(3, 4, 5);
1471     INSERT INTO two VALUES(1, 2, 3);
1472     INSERT INTO two VALUES(2, 3, 4);
1473     INSERT INTO two VALUES(3, 4, 5);
1474   }
1475 } {1 1 1 1 1}
1476 do_test fkey2-17.1.6 {
1477   catchsql {
1478     BEGIN;
1479       INSERT INTO one VALUES(0, 0, 0);
1480       UPDATE two SET e=e+1, f=f+1;
1481   }
1482 } {1 {FOREIGN KEY constraint failed}}
1483 do_test fkey2-17.1.7 {
1484   execsql { SELECT * FROM one }
1485 } {1 2 3 2 3 4 3 4 5 0 0 0}
1486 do_test fkey2-17.1.8 {
1487   execsql { SELECT * FROM two }
1488 } {1 2 3 2 3 4 3 4 5}
1489 do_test fkey2-17.1.9 {
1490   execsql COMMIT
1491 } {}
1492 do_test fkey2-17.1.10 {
1493   execsql {
1494     CREATE TABLE three(
1495       g, h, i,
1496       FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED
1497     );
1498   }
1499 } {}
1500 do_test fkey2-17.1.11 {
1501   set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy]
1502   sqlite3_step $STMT
1503 } {SQLITE_ROW}
1504 do_test fkey2-17.1.12 {
1505   sqlite3_column_text $STMT 0
1506 } {1}
1507 do_test fkey2-17.1.13 {
1508   sqlite3_step $STMT
1509 } {SQLITE_CONSTRAINT}
1510 verify_ex_errcode fkey2-17.1.13b SQLITE_CONSTRAINT_FOREIGNKEY
1511 do_test fkey2-17.1.14 {
1512   sqlite3_finalize $STMT
1513 } {SQLITE_CONSTRAINT}
1514 verify_ex_errcode fkey2-17.1.14b SQLITE_CONSTRAINT_FOREIGNKEY
1515
1516 drop_all_tables
1517 do_test fkey2-17.2.1 {
1518   execsql {
1519     CREATE TABLE high("a'b!" PRIMARY KEY, b);
1520     CREATE TABLE low(
1521       c,
1522       "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE
1523     );
1524   }
1525 } {}
1526 do_test fkey2-17.2.2 {
1527   execsql {
1528     INSERT INTO high VALUES('a', 'b');
1529     INSERT INTO low VALUES('b', 'a');
1530   }
1531   db changes
1532 } {1}
1533 set nTotal [db total_changes]
1534 do_test fkey2-17.2.3 {
1535   execsql { UPDATE high SET "a'b!" = 'c' }
1536 } {1}
1537 do_test fkey2-17.2.4 {
1538   db changes
1539 } {1}
1540 do_test fkey2-17.2.5 {
1541   expr [db total_changes] - $nTotal
1542 } {2}
1543 do_test fkey2-17.2.6 {
1544   execsql { SELECT * FROM high ; SELECT * FROM low }
1545 } {c b b c}
1546 do_test fkey2-17.2.7 {
1547   execsql { DELETE FROM high }
1548 } {1}
1549 do_test fkey2-17.2.8 {
1550   db changes
1551 } {1}
1552 do_test fkey2-17.2.9 {
1553   expr [db total_changes] - $nTotal
1554 } {4}
1555 do_test fkey2-17.2.10 {
1556   execsql { SELECT * FROM high ; SELECT * FROM low }
1557 } {}
1558 execsql { PRAGMA count_changes = 0 }
1559
1560 #-------------------------------------------------------------------------
1561 # Test that the authorization callback works.
1562 #
1563
1564 ifcapable auth {
1565   do_test fkey2-18.1 {
1566     execsql {
1567       CREATE TABLE long(a, b PRIMARY KEY, c);
1568       CREATE TABLE short(d, e, f REFERENCES long);
1569       CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED);
1570     }
1571   } {}
1572
1573   proc auth {args} {eval lappend ::authargs [lrange $args 0 4]; return SQLITE_OK}
1574   db auth auth
1575
1576   # An insert on the parent table must read the child key of any deferred
1577   # foreign key constraints. But not the child key of immediate constraints.
1578   set authargs {}
1579   do_test fkey2-18.2 {
1580     execsql { INSERT INTO long VALUES(1, 2, 3) }
1581     set authargs
1582   } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}}
1583
1584   # An insert on the child table of an immediate constraint must read the
1585   # parent key columns (to see if it is a violation or not).
1586   set authargs {}
1587   do_test fkey2-18.3 {
1588     execsql { INSERT INTO short VALUES(1, 3, 2) }
1589     set authargs
1590   } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}}
1591
1592   # As must an insert on the child table of a deferred constraint.
1593   set authargs {}
1594   do_test fkey2-18.4 {
1595     execsql { INSERT INTO mid VALUES(1, 3, 2) }
1596     set authargs
1597   } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}}
1598
1599   do_test fkey2-18.5 {
1600     execsql {
1601       CREATE TABLE nought(a, b PRIMARY KEY, c);
1602       CREATE TABLE cross(d, e, f,
1603         FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE
1604       );
1605     }
1606     execsql { INSERT INTO nought VALUES(2, 1, 2) }
1607     execsql { INSERT INTO cross VALUES(0, 1, 0) }
1608     set authargs [list]
1609     execsql { UPDATE nought SET b = 5 }
1610     set authargs
1611   } {SQLITE_UPDATE nought b main {} SQLITE_READ cross e main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_UPDATE cross e main {} SQLITE_READ nought b main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {}}
1612
1613   do_test fkey2-18.6 {
1614     execsql {SELECT * FROM cross}
1615   } {0 5 0}
1616
1617   do_test fkey2-18.7 {
1618     execsql {
1619       CREATE TABLE one(a INTEGER PRIMARY KEY, b);
1620       CREATE TABLE two(b, c REFERENCES one);
1621       INSERT INTO one VALUES(101, 102);
1622     }
1623     set authargs [list]
1624     execsql { INSERT INTO two VALUES(100, 101); }
1625     set authargs
1626   } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}}
1627
1628   # Return SQLITE_IGNORE to requests to read from the parent table. This
1629   # causes inserts of non-NULL keys into the child table to fail.
1630   #
1631   rename auth {}
1632   proc auth {args} {
1633     if {[lindex $args 1] == "long"} {return SQLITE_IGNORE}
1634     return SQLITE_OK
1635   }
1636   do_test fkey2-18.8 {
1637     catchsql { INSERT INTO short VALUES(1, 3, 2) }
1638   } {1 {FOREIGN KEY constraint failed}}
1639   do_test fkey2-18.9 {
1640     execsql { INSERT INTO short VALUES(1, 3, NULL) }
1641   } {}
1642   do_test fkey2-18.10 {
1643     execsql { SELECT * FROM short }
1644   } {1 3 2 1 3 {}}
1645   do_test fkey2-18.11 {
1646     catchsql { UPDATE short SET f = 2 WHERE f IS NULL }
1647   } {1 {FOREIGN KEY constraint failed}}
1648
1649   db auth {}
1650   unset authargs
1651 }
1652
1653
1654 do_test fkey2-19.1 {
1655   execsql {
1656     CREATE TABLE main(id INTEGER PRIMARY KEY);
1657     CREATE TABLE sub(id INT REFERENCES main(id));
1658     INSERT INTO main VALUES(1);
1659     INSERT INTO main VALUES(2);
1660     INSERT INTO sub VALUES(2);
1661   }
1662 } {}
1663 do_test fkey2-19.2 {
1664   set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy]
1665   sqlite3_bind_int $S 1 2
1666   sqlite3_step $S
1667 } {SQLITE_CONSTRAINT}
1668 verify_ex_errcode fkey2-19.2b SQLITE_CONSTRAINT_FOREIGNKEY
1669 do_test fkey2-19.3 {
1670   sqlite3_reset $S
1671 } {SQLITE_CONSTRAINT}
1672 verify_ex_errcode fkey2-19.3b SQLITE_CONSTRAINT_FOREIGNKEY
1673 do_test fkey2-19.4 {
1674   sqlite3_bind_int $S 1 1
1675   sqlite3_step $S
1676 } {SQLITE_DONE}
1677 do_test fkey2-19.4 {
1678   sqlite3_finalize $S
1679 } {SQLITE_OK}
1680
1681 drop_all_tables
1682 do_test fkey2-20.1 {
1683   execsql {
1684     CREATE TABLE pp(a PRIMARY KEY, b);
1685     CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp);
1686   }
1687 } {}
1688
1689 foreach {tn insert} {
1690   1 "INSERT"
1691   2 "INSERT OR IGNORE"
1692   3 "INSERT OR ABORT"
1693   4 "INSERT OR ROLLBACK"
1694   5 "INSERT OR REPLACE"
1695   6 "INSERT OR FAIL"
1696 } {
1697   do_test fkey2-20.2.$tn.1 {
1698     catchsql "$insert INTO cc VALUES(1, 2)"
1699   } {1 {FOREIGN KEY constraint failed}}
1700   do_test fkey2-20.2.$tn.2 {
1701     execsql { SELECT * FROM cc }
1702   } {}
1703   do_test fkey2-20.2.$tn.3 {
1704     execsql {
1705       BEGIN;
1706         INSERT INTO pp VALUES(2, 'two');
1707         INSERT INTO cc VALUES(1, 2);
1708     }
1709     catchsql "$insert INTO cc VALUES(3, 4)"
1710   } {1 {FOREIGN KEY constraint failed}}
1711   do_test fkey2-20.2.$tn.4 {
1712     execsql { COMMIT ; SELECT * FROM cc }
1713   } {1 2}
1714   do_test fkey2-20.2.$tn.5 {
1715     execsql { DELETE FROM cc ; DELETE FROM pp }
1716   } {}
1717 }
1718
1719 foreach {tn update} {
1720   1 "UPDATE"
1721   2 "UPDATE OR IGNORE"
1722   3 "UPDATE OR ABORT"
1723   4 "UPDATE OR ROLLBACK"
1724   5 "UPDATE OR REPLACE"
1725   6 "UPDATE OR FAIL"
1726 } {
1727   do_test fkey2-20.3.$tn.1 {
1728     execsql {
1729       INSERT INTO pp VALUES(2, 'two');
1730       INSERT INTO cc VALUES(1, 2);
1731     }
1732   } {}
1733   do_test fkey2-20.3.$tn.2 {
1734     catchsql "$update pp SET a = 1"
1735   } {1 {FOREIGN KEY constraint failed}}
1736   do_test fkey2-20.3.$tn.3 {
1737     execsql { SELECT * FROM pp }
1738   } {2 two}
1739   do_test fkey2-20.3.$tn.4 {
1740     catchsql "$update cc SET d = 1"
1741   } {1 {FOREIGN KEY constraint failed}}
1742   do_test fkey2-20.3.$tn.5 {
1743     execsql { SELECT * FROM cc }
1744   } {1 2}
1745   do_test fkey2-20.3.$tn.6 {
1746     execsql {
1747       BEGIN;
1748         INSERT INTO pp VALUES(3, 'three');
1749     }
1750     catchsql "$update pp SET a = 1 WHERE a = 2"
1751   } {1 {FOREIGN KEY constraint failed}}
1752   do_test fkey2-20.3.$tn.7 {
1753     execsql { COMMIT ; SELECT * FROM pp }
1754   } {2 two 3 three}
1755   do_test fkey2-20.3.$tn.8 {
1756     execsql {
1757       BEGIN;
1758         INSERT INTO cc VALUES(2, 2);
1759     }
1760     catchsql "$update cc SET d = 1 WHERE c = 1"
1761   } {1 {FOREIGN KEY constraint failed}}
1762   do_test fkey2-20.3.$tn.9 {
1763     execsql { COMMIT ; SELECT * FROM cc }
1764   } {1 2 2 2}
1765   do_test fkey2-20.3.$tn.10 {
1766     execsql { DELETE FROM cc ; DELETE FROM pp }
1767   } {}
1768 }
1769
1770 #-------------------------------------------------------------------------
1771 # The following block of tests, those prefixed with "fkey2-genfkey.", are
1772 # the same tests that were used to test the ".genfkey" command provided
1773 # by the shell tool. So these tests show that the built-in foreign key
1774 # implementation is more or less compatible with the triggers generated
1775 # by genfkey.
1776 #
1777 drop_all_tables
1778 do_test fkey2-genfkey.1.1 {
1779   execsql {
1780     CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
1781     CREATE TABLE t2(e REFERENCES t1, f);
1782     CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
1783   }
1784 } {}
1785 do_test fkey2-genfkey.1.2 {
1786   catchsql { INSERT INTO t2 VALUES(1, 2) }
1787 } {1 {FOREIGN KEY constraint failed}}
1788 do_test fkey2-genfkey.1.3 {
1789   execsql {
1790     INSERT INTO t1 VALUES(1, 2, 3);
1791     INSERT INTO t2 VALUES(1, 2);
1792   }
1793 } {}
1794 do_test fkey2-genfkey.1.4 {
1795   execsql { INSERT INTO t2 VALUES(NULL, 3) }
1796 } {}
1797 do_test fkey2-genfkey.1.5 {
1798   catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
1799 } {1 {FOREIGN KEY constraint failed}}
1800 do_test fkey2-genfkey.1.6 {
1801   execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
1802 } {}
1803 do_test fkey2-genfkey.1.7 {
1804   execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
1805 } {}
1806 do_test fkey2-genfkey.1.8 {
1807   catchsql { UPDATE t1 SET a = 10 }
1808 } {1 {FOREIGN KEY constraint failed}}
1809 do_test fkey2-genfkey.1.9 {
1810   catchsql { UPDATE t1 SET a = NULL }
1811 } {1 {datatype mismatch}}
1812 do_test fkey2-genfkey.1.10 {
1813   catchsql { DELETE FROM t1 }
1814 } {1 {FOREIGN KEY constraint failed}}
1815 do_test fkey2-genfkey.1.11 {
1816   execsql { UPDATE t2 SET e = NULL }
1817 } {}
1818 do_test fkey2-genfkey.1.12 {
1819   execsql {
1820     UPDATE t1 SET a = 10;
1821     DELETE FROM t1;
1822     DELETE FROM t2;
1823   }
1824 } {}
1825 do_test fkey2-genfkey.1.13 {
1826   execsql {
1827     INSERT INTO t3 VALUES(1, NULL, NULL);
1828     INSERT INTO t3 VALUES(1, 2, NULL);
1829     INSERT INTO t3 VALUES(1, NULL, 3);
1830   }
1831 } {}
1832 do_test fkey2-genfkey.1.14 {
1833   catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
1834 } {1 {FOREIGN KEY constraint failed}}
1835 do_test fkey2-genfkey.1.15 {
1836   execsql {
1837     INSERT INTO t1 VALUES(1, 1, 4);
1838     INSERT INTO t3 VALUES(3, 1, 4);
1839   }
1840 } {}
1841 do_test fkey2-genfkey.1.16 {
1842   catchsql { DELETE FROM t1 }
1843 } {1 {FOREIGN KEY constraint failed}}
1844 do_test fkey2-genfkey.1.17 {
1845   catchsql { UPDATE t1 SET b = 10}
1846 } {1 {FOREIGN KEY constraint failed}}
1847 do_test fkey2-genfkey.1.18 {
1848   execsql { UPDATE t1 SET a = 10}
1849 } {}
1850 do_test fkey2-genfkey.1.19 {
1851   catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
1852 } {1 {FOREIGN KEY constraint failed}}
1853
1854 drop_all_tables
1855 do_test fkey2-genfkey.2.1 {
1856   execsql {
1857     CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
1858     CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
1859     CREATE TABLE t3(g, h, i,
1860         FOREIGN KEY (h, i)
1861         REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
1862     );
1863   }
1864 } {}
1865 do_test fkey2-genfkey.2.2 {
1866   execsql {
1867     INSERT INTO t1 VALUES(1, 2, 3);
1868     INSERT INTO t1 VALUES(4, 5, 6);
1869     INSERT INTO t2 VALUES(1, 'one');
1870     INSERT INTO t2 VALUES(4, 'four');
1871   }
1872 } {}
1873 do_test fkey2-genfkey.2.3 {
1874   execsql {
1875     UPDATE t1 SET a = 2 WHERE a = 1;
1876     SELECT * FROM t2;
1877   }
1878 } {2 one 4 four}
1879 do_test fkey2-genfkey.2.4 {
1880   execsql {
1881     DELETE FROM t1 WHERE a = 4;
1882     SELECT * FROM t2;
1883   }
1884 } {2 one}
1885
1886 do_test fkey2-genfkey.2.5 {
1887   execsql {
1888     INSERT INTO t3 VALUES('hello', 2, 3);
1889     UPDATE t1 SET c = 2;
1890     SELECT * FROM t3;
1891   }
1892 } {hello 2 2}
1893 do_test fkey2-genfkey.2.6 {
1894   execsql {
1895     DELETE FROM t1;
1896     SELECT * FROM t3;
1897   }
1898 } {}
1899
1900 drop_all_tables
1901 do_test fkey2-genfkey.3.1 {
1902   execsql {
1903     CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));
1904     CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
1905     CREATE TABLE t3(g, h, i,
1906         FOREIGN KEY (h, i)
1907         REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
1908     );
1909   }
1910 } {}
1911 do_test fkey2-genfkey.3.2 {
1912   execsql {
1913     INSERT INTO t1 VALUES(1, 2, 3);
1914     INSERT INTO t1 VALUES(4, 5, 6);
1915     INSERT INTO t2 VALUES(1, 'one');
1916     INSERT INTO t2 VALUES(4, 'four');
1917   }
1918 } {}
1919 do_test fkey2-genfkey.3.3 {
1920   execsql {
1921     UPDATE t1 SET a = 2 WHERE a = 1;
1922     SELECT * FROM t2;
1923   }
1924 } {{} one 4 four}
1925 do_test fkey2-genfkey.3.4 {
1926   execsql {
1927     DELETE FROM t1 WHERE a = 4;
1928     SELECT * FROM t2;
1929   }
1930 } {{} one {} four}
1931 do_test fkey2-genfkey.3.5 {
1932   execsql {
1933     INSERT INTO t3 VALUES('hello', 2, 3);
1934     UPDATE t1 SET c = 2;
1935     SELECT * FROM t3;
1936   }
1937 } {hello {} {}}
1938 do_test fkey2-genfkey.3.6 {
1939   execsql {
1940     UPDATE t3 SET h = 2, i = 2;
1941     DELETE FROM t1;
1942     SELECT * FROM t3;
1943   }
1944 } {hello {} {}}
1945
1946 #-------------------------------------------------------------------------
1947 # Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been
1948 # fixed.
1949 #
1950 do_test fkey2-dd08e5.1.1 {
1951   execsql {
1952     PRAGMA foreign_keys=ON;
1953     CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b);
1954     CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
1955     INSERT INTO tdd08 VALUES(200,300);
1956
1957     CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b));
1958     INSERT INTO tdd08_b VALUES(100,200,300);
1959   }
1960 } {}
1961 do_test fkey2-dd08e5.1.2 {
1962   catchsql {
1963     DELETE FROM tdd08;
1964   }
1965 } {1 {FOREIGN KEY constraint failed}}
1966 do_test fkey2-dd08e5.1.3 {
1967   execsql {
1968     SELECT * FROM tdd08;
1969   }
1970 } {200 300}
1971 do_test fkey2-dd08e5.1.4 {
1972   catchsql {
1973     INSERT INTO tdd08_b VALUES(400,500,300);
1974   }
1975 } {1 {FOREIGN KEY constraint failed}}
1976 do_test fkey2-dd08e5.1.5 {
1977   catchsql {
1978     UPDATE tdd08_b SET x=x+1;
1979   }
1980 } {1 {FOREIGN KEY constraint failed}}
1981 do_test fkey2-dd08e5.1.6 {
1982   catchsql {
1983     UPDATE tdd08 SET a=a+1;
1984   }
1985 } {1 {FOREIGN KEY constraint failed}}
1986
1987 #-------------------------------------------------------------------------
1988 # Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
1989 # fixed.
1990 #
1991 do_test fkey2-ce7c13.1.1 {
1992   execsql {
1993     CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
1994     CREATE UNIQUE INDEX ice71 ON tce71(a,b);
1995     INSERT INTO tce71 VALUES(100,200);
1996     CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
1997     INSERT INTO tce72 VALUES(300,100,200);
1998     UPDATE tce71 set b = 200 where a = 100;
1999     SELECT * FROM tce71, tce72;
2000   }
2001 } {100 200 300 100 200}
2002 do_test fkey2-ce7c13.1.2 {
2003   catchsql {
2004     UPDATE tce71 set b = 201 where a = 100;
2005   }
2006 } {1 {FOREIGN KEY constraint failed}}
2007 do_test fkey2-ce7c13.1.3 {
2008   catchsql {
2009     UPDATE tce71 set a = 101 where a = 100;
2010   }
2011 } {1 {FOREIGN KEY constraint failed}}
2012 do_test fkey2-ce7c13.1.4 {
2013   execsql {
2014     CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
2015     INSERT INTO tce73 VALUES(100,200);
2016     CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
2017     INSERT INTO tce74 VALUES(300,100,200);
2018     UPDATE tce73 set b = 200 where a = 100;
2019     SELECT * FROM tce73, tce74;
2020   }
2021 } {100 200 300 100 200}
2022 do_test fkey2-ce7c13.1.5 {
2023   catchsql {
2024     UPDATE tce73 set b = 201 where a = 100;
2025   }
2026 } {1 {FOREIGN KEY constraint failed}}
2027 do_test fkey2-ce7c13.1.6 {
2028   catchsql {
2029     UPDATE tce73 set a = 101 where a = 100;
2030   }
2031 } {1 {FOREIGN KEY constraint failed}}
2032
2033 # 2015-04-16:  Foreign key errors propagate back up to the parser.
2034 #
2035 do_test fkey2-20150416-100 {
2036   db close
2037   sqlite3 db :memory:
2038   catchsql {
2039     PRAGMA foreign_keys=1;
2040     CREATE TABLE t1(x PRIMARY KEY);
2041     CREATE TABLE t(y REFERENCES t0(x)ON DELETE SET DEFAULT);
2042     CREATE TABLE t0(y REFERENCES t1 ON DELETE SET NULL);
2043     REPLACE INTO t1 SELECT(0);CREATE TABLE t2(x);CREATE TABLE t3;
2044   }
2045 } {1 {foreign key mismatch - "t" referencing "t0"}}
2046
2047 finish_test