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: ef}}
 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: ef}}
 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     }
 959     catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
 960   } {0 {}}
 961   do_test fkey2-14.1.2 {
 962     catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
 963   } {0 {}}
 964   do_test fkey2-14.1.3 {
 965     catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
 966   } {0 {}}
 967   do_test fkey2-14.1.4 {
 968     catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
 969   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
 970   do_test fkey2-14.1.5 {
 971     catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
 972   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
 973   do_test fkey2-14.1.6 {
 974     execsql {
 975       PRAGMA foreign_keys = off;
 976       ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
 977       PRAGMA foreign_keys = on;
 978       SELECT sql FROM sqlite_master WHERE name='t2';
 979     }
 980   } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
 981
 982
 983   # Test the sqlite_rename_parent() function directly.
 984   #
 985   proc test_rename_parent {zCreate zOld zNew} {
 986     db eval {SELECT sqlite_rename_table(
 987         'main', 'table', 't1', $zCreate, $zOld, $zNew, 0
 988     )}
 989   }
 990   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS 1
 991   do_test fkey2-14.2.1.1 {
 992     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
 993   } {{CREATE TABLE t1(a REFERENCES "t3")}}
 994   do_test fkey2-14.2.1.2 {
 995     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
 996   } {{CREATE TABLE t1(a REFERENCES t2)}}
 997   do_test fkey2-14.2.1.3 {
 998     test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
 999   } {{CREATE TABLE t1(a REFERENCES "t3")}}
1000   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS 0
1001
1002   # Test ALTER TABLE RENAME TABLE a bit.
1003   #
1004   do_test fkey2-14.2.2.1 {
1005     drop_all_tables
1006     execsql {
1007       CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1);
1008       CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
1009       CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
1010     }
1011     execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
1012   } [list \
1013     {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)}                     \
1014     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)}    \
1015     {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
1016   ]
1017   do_test fkey2-14.2.2.2 {
1018     execsql { ALTER TABLE t1 RENAME TO t4 }
1019     execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
1020   } [list \
1021     {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
1022     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
1023     {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
1024   ]
1025   do_test fkey2-14.2.2.3 {
1026     catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
1027   } {1 {FOREIGN KEY constraint failed}}
1028   do_test fkey2-14.2.2.4 {
1029     execsql { INSERT INTO t4 VALUES(1, NULL) }
1030   } {}
1031   do_test fkey2-14.2.2.5 {
1032     catchsql { UPDATE t4 SET b = 5 }
1033   } {1 {FOREIGN KEY constraint failed}}
1034   do_test fkey2-14.2.2.6 {
1035     catchsql { UPDATE t4 SET b = 1 }
1036   } {0 {}}
1037   do_test fkey2-14.2.2.7 {
1038     execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
1039   } {}
1040
1041   # Repeat for TEMP tables
1042   #
1043   drop_all_tables
1044   do_test fkey2-14.1tmp.1 {
1045     # Adding a column with a REFERENCES clause is not supported.
1046     execsql {
1047       CREATE TEMP TABLE t1(a PRIMARY KEY);
1048       CREATE TEMP TABLE t2(a, b);
1049     }
1050     catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
1051   } {0 {}}
1052   do_test fkey2-14.1tmp.2 {
1053     catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
1054   } {0 {}}
1055   do_test fkey2-14.1tmp.3 {
1056     catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
1057   } {0 {}}
1058   do_test fkey2-14.1tmp.4 {
1059     catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
1060   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1061   do_test fkey2-14.1tmp.5 {
1062     catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
1063   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1064   do_test fkey2-14.1tmp.6 {
1065     execsql {
1066       PRAGMA foreign_keys = off;
1067       ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
1068       PRAGMA foreign_keys = on;
1069       SELECT sql FROM temp.sqlite_master WHERE name='t2';
1070     }
1071   } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
1072
1073   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS 1
1074   do_test fkey2-14.2tmp.1.1 {
1075     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
1076   } {{CREATE TABLE t1(a REFERENCES "t3")}}
1077   do_test fkey2-14.2tmp.1.2 {
1078     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
1079   } {{CREATE TABLE t1(a REFERENCES t2)}}
1080   do_test fkey2-14.2tmp.1.3 {
1081     test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
1082   } {{CREATE TABLE t1(a REFERENCES "t3")}}
1083   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS 0
1084
1085   # Test ALTER TABLE RENAME TABLE a bit.
1086   #
1087   do_test fkey2-14.2tmp.2.1 {
1088     drop_all_tables
1089     execsql {
1090       CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1);
1091       CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
1092       CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
1093     }
1094     execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
1095   } [list \
1096     {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)}                     \
1097     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)}    \
1098     {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
1099   ]
1100   do_test fkey2-14.2tmp.2.2 {
1101     execsql { ALTER TABLE t1 RENAME TO t4 }
1102     execsql { SELECT sql FROM temp.sqlite_master WHERE type = 'table'}
1103   } [list \
1104     {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
1105     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
1106     {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
1107   ]
1108   do_test fkey2-14.2tmp.2.3 {
1109     catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
1110   } {1 {FOREIGN KEY constraint failed}}
1111   do_test fkey2-14.2tmp.2.4 {
1112     execsql { INSERT INTO t4 VALUES(1, NULL) }
1113   } {}
1114   do_test fkey2-14.2tmp.2.5 {
1115     catchsql { UPDATE t4 SET b = 5 }
1116   } {1 {FOREIGN KEY constraint failed}}
1117   do_test fkey2-14.2tmp.2.6 {
1118     catchsql { UPDATE t4 SET b = 1 }
1119   } {0 {}}
1120   do_test fkey2-14.2tmp.2.7 {
1121     execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
1122   } {}
1123
1124   # Repeat for ATTACH-ed tables
1125   #
1126   drop_all_tables
1127   do_test fkey2-14.1aux.1 {
1128     # Adding a column with a REFERENCES clause is not supported.
1129     execsql {
1130       ATTACH ':memory:' AS aux;
1131       CREATE TABLE aux.t1(a PRIMARY KEY);
1132       CREATE TABLE aux.t2(a, b);
1133     }
1134     catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
1135   } {0 {}}
1136   do_test fkey2-14.1aux.2 {
1137     catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
1138   } {0 {}}
1139   do_test fkey2-14.1aux.3 {
1140     catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
1141   } {0 {}}
1142   do_test fkey2-14.1aux.4 {
1143     catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
1144   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1145   do_test fkey2-14.1aux.5 {
1146     catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
1147   } {1 {Cannot add a REFERENCES column with non-NULL default value}}
1148   do_test fkey2-14.1aux.6 {
1149     execsql {
1150       PRAGMA foreign_keys = off;
1151       ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
1152       PRAGMA foreign_keys = on;
1153       SELECT sql FROM aux.sqlite_master WHERE name='t2';
1154     }
1155   } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
1156
1157   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS 1
1158   do_test fkey2-14.2aux.1.1 {
1159     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
1160   } {{CREATE TABLE t1(a REFERENCES "t3")}}
1161   do_test fkey2-14.2aux.1.2 {
1162     test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
1163   } {{CREATE TABLE t1(a REFERENCES t2)}}
1164   do_test fkey2-14.2aux.1.3 {
1165     test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
1166   } {{CREATE TABLE t1(a REFERENCES "t3")}}
1167   sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS 0
1168
1169   # Test ALTER TABLE RENAME TABLE a bit.
1170   #
1171   do_test fkey2-14.2aux.2.1 {
1172     drop_all_tables
1173     execsql {
1174       CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1);
1175       CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
1176       CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
1177     }
1178     execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
1179   } [list \
1180     {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)}                     \
1181     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)}    \
1182     {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
1183   ]
1184   do_test fkey2-14.2aux.2.2 {
1185     execsql { ALTER TABLE t1 RENAME TO t4 }
1186     execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
1187   } [list \
1188     {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
1189     {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
1190     {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
1191   ]
1192   do_test fkey2-14.2aux.2.3 {
1193     catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
1194   } {1 {FOREIGN KEY constraint failed}}
1195   do_test fkey2-14.2aux.2.4 {
1196     execsql { INSERT INTO t4 VALUES(1, NULL) }
1197   } {}
1198   do_test fkey2-14.2aux.2.5 {
1199     catchsql { UPDATE t4 SET b = 5 }
1200   } {1 {FOREIGN KEY constraint failed}}
1201   do_test fkey2-14.2aux.2.6 {
1202     catchsql { UPDATE t4 SET b = 1 }
1203   } {0 {}}
1204   do_test fkey2-14.2aux.2.7 {
1205     execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
1206   } {}
1207 }
1208
1209 do_test fkey-2.14.3.1 {
1210   drop_all_tables
1211   execsql {
1212     CREATE TABLE t1(a, b REFERENCES nosuchtable);
1213     DROP TABLE t1;
1214   }
1215 } {}
1216 do_test fkey-2.14.3.2 {
1217   execsql {
1218     CREATE TABLE t1(a PRIMARY KEY, b);
1219     INSERT INTO t1 VALUES('a', 1);
1220     CREATE TABLE t2(x REFERENCES t1);
1221     INSERT INTO t2 VALUES('a');
1222   }
1223 } {}
1224 do_test fkey-2.14.3.3 {
1225   catchsql { DROP TABLE t1 }
1226 } {1 {FOREIGN KEY constraint failed}}
1227 do_test fkey-2.14.3.4 {
1228   execsql {
1229     DELETE FROM t2;
1230     DROP TABLE t1;
1231   }
1232 } {}
1233 do_test fkey-2.14.3.4 {
1234   catchsql { INSERT INTO t2 VALUES('x') }
1235 } {1 {no such table: main.t1}}
1236 do_test fkey-2.14.3.5 {
1237   execsql {
1238     CREATE TABLE t1(x PRIMARY KEY);
1239     INSERT INTO t1 VALUES('x');
1240   }
1241   execsql { INSERT INTO t2 VALUES('x') }
1242 } {}
1243 do_test fkey-2.14.3.6 {
1244   catchsql { DROP TABLE t1 }
1245 } {1 {FOREIGN KEY constraint failed}}
1246 do_test fkey-2.14.3.7 {
1247   execsql {
1248     DROP TABLE t2;
1249     DROP TABLE t1;
1250   }
1251 } {}
1252 do_test fkey-2.14.3.8 {
1253   execsql {
1254     CREATE TABLE pp(x, y, PRIMARY KEY(x, y));
1255     CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
1256   }
1257   catchsql { INSERT INTO cc VALUES(1, 2) }
1258 } {1 {foreign key mismatch - "cc" referencing "pp"}}
1259 do_test fkey-2.14.3.9 {
1260   execsql { DROP TABLE cc }
1261 } {}
1262 do_test fkey-2.14.3.10 {
1263   execsql {
1264     CREATE TABLE cc(a, b,
1265       FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED
1266     );
1267   }
1268   execsql {
1269     INSERT INTO pp VALUES('a', 'b');
1270     INSERT INTO cc VALUES('a', 'b');
1271     BEGIN;
1272       DROP TABLE pp;
1273       CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
1274       INSERT INTO pp VALUES(1, 'a', 'b');
1275     COMMIT;
1276   }
1277 } {}
1278 do_test fkey-2.14.3.11 {
1279   execsql {
1280     BEGIN;
1281       DROP TABLE cc;
1282       DROP TABLE pp;
1283     COMMIT;
1284   }
1285 } {}
1286 do_test fkey-2.14.3.12 {
1287   execsql {
1288     CREATE TABLE b1(a, b);
1289     CREATE TABLE b2(a, b REFERENCES b1);
1290     DROP TABLE b1;
1291   }
1292 } {}
1293 do_test fkey-2.14.3.13 {
1294   execsql {
1295     CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED);
1296     DROP TABLE b2;
1297   }
1298 } {}
1299
1300 # Test that nothing goes wrong when dropping a table that refers to a view.
1301 # Or dropping a view that an existing FK (incorrectly) refers to. Or either
1302 # of the above scenarios with a virtual table.
1303 drop_all_tables
1304 do_test fkey-2.14.4.1 {
1305   execsql {
1306     CREATE TABLE t1(x REFERENCES v);
1307     CREATE VIEW v AS SELECT * FROM t1;
1308   }
1309 } {}
1310 do_test fkey-2.14.4.2 {
1311   execsql {
1312     DROP VIEW v;
1313   }
1314 } {}
1315 ifcapable vtab {
1316   register_echo_module db
1317   do_test fkey-2.14.4.3 {
1318     execsql { CREATE VIRTUAL TABLE v USING echo(t1) }
1319   } {}
1320   do_test fkey-2.14.4.2 {
1321     execsql {
1322       DROP TABLE v;
1323     }
1324   } {}
1325 }
1326
1327 #-------------------------------------------------------------------------
1328 # The following tests, fkey2-15.*, test that unnecessary FK related scans
1329 # and lookups are avoided when the constraint counters are zero.
1330 #
1331 drop_all_tables
1332 proc execsqlS {zSql} {
1333   set ::sqlite_search_count 0
1334   set ::sqlite_found_count 0
1335   set res [uplevel [list execsql $zSql]]
1336   concat [expr $::sqlite_found_count + $::sqlite_search_count] $res
1337 }
1338 do_test fkey2-15.1.1 {
1339   execsql {
1340     CREATE TABLE pp(a PRIMARY KEY, b);
1341     CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED);
1342     INSERT INTO pp VALUES(1, 'one');
1343     INSERT INTO pp VALUES(2, 'two');
1344     INSERT INTO cc VALUES('neung', 1);
1345     INSERT INTO cc VALUES('song', 2);
1346   }
1347 } {}
1348 do_test fkey2-15.1.2 {
1349   execsqlS { INSERT INTO pp VALUES(3, 'three') }
1350 } {0}
1351 do_test fkey2-15.1.3 {
1352   execsql {
1353     BEGIN;
1354       INSERT INTO cc VALUES('see', 4);    -- Violates deferred constraint
1355   }
1356   execsqlS { INSERT INTO pp VALUES(5, 'five') }
1357 } {2}
1358 do_test fkey2-15.1.4 {
1359   execsql { DELETE FROM cc WHERE x = 'see' }
1360   execsqlS { INSERT INTO pp VALUES(6, 'six') }
1361 } {0}
1362 do_test fkey2-15.1.5 {
1363   execsql COMMIT
1364 } {}
1365 do_test fkey2-15.1.6 {
1366   execsql BEGIN
1367   execsqlS {
1368     DELETE FROM cc WHERE x = 'neung';
1369     ROLLBACK;
1370   }
1371 } {1}
1372 do_test fkey2-15.1.7 {
1373   execsql {
1374     BEGIN;
1375     DELETE FROM pp WHERE a = 2;
1376   }
1377   execsqlS {
1378     DELETE FROM cc WHERE x = 'neung';
1379     ROLLBACK;
1380   }
1381 } {2}
1382
1383 #-------------------------------------------------------------------------
1384 # This next block of tests, fkey2-16.*, test that rows that refer to
1385 # themselves may be inserted and deleted.
1386 #
1387 foreach {tn zSchema} {
1388   1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) }
1389   2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) }
1390   3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) }
1391 } {
1392   drop_all_tables
1393   do_test fkey2-16.1.$tn.1 {
1394     execsql $zSchema
1395     execsql { INSERT INTO self VALUES(13, 13) }
1396   } {}
1397   do_test fkey2-16.1.$tn.2 {
1398     execsql { UPDATE self SET a = 14, b = 14 }
1399   } {}
1400
1401   do_test fkey2-16.1.$tn.3 {
1402     catchsql { UPDATE self SET b = 15 }
1403   } {1 {FOREIGN KEY constraint failed}}
1404
1405   do_test fkey2-16.1.$tn.4 {
1406     catchsql { UPDATE self SET a = 15 }
1407   } {1 {FOREIGN KEY constraint failed}}
1408
1409   do_test fkey2-16.1.$tn.5 {
1410     catchsql { UPDATE self SET a = 15, b = 16 }
1411   } {1 {FOREIGN KEY constraint failed}}
1412
1413   do_test fkey2-16.1.$tn.6 {
1414     catchsql { UPDATE self SET a = 17, b = 17 }
1415   } {0 {}}
1416
1417   do_test fkey2-16.1.$tn.7 {
1418     execsql { DELETE FROM self }
1419   } {}
1420   do_test fkey2-16.1.$tn.8 {
1421     catchsql { INSERT INTO self VALUES(20, 21) }
1422   } {1 {FOREIGN KEY constraint failed}}
1423 }
1424
1425 #-------------------------------------------------------------------------
1426 # This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes"
1427 # is turned on statements that violate immediate FK constraints return
1428 # SQLITE_CONSTRAINT immediately, not after returning a number of rows.
1429 # Whereas statements that violate deferred FK constraints return the number
1430 # of rows before failing.
1431 #
1432 # Also test that rows modified by FK actions are not counted in either the
1433 # returned row count or the values returned by sqlite3_changes(). Like
1434 # trigger related changes, they are included in sqlite3_total_changes() though.
1435 #
1436 drop_all_tables
1437 do_test fkey2-17.1.1 {
1438   execsql { PRAGMA count_changes = 1 }
1439   execsql {
1440     CREATE TABLE one(a, b, c, UNIQUE(b, c));
1441     CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c));
1442     INSERT INTO one VALUES(1, 2, 3);
1443   }
1444 } {1}
1445 do_test fkey2-17.1.2 {
1446   set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy]
1447   sqlite3_step $STMT
1448 } {SQLITE_CONSTRAINT}
1449 verify_ex_errcode fkey2-17.1.2b SQLITE_CONSTRAINT_FOREIGNKEY
1450 ifcapable autoreset {
1451   do_test fkey2-17.1.3 {
1452     sqlite3_step $STMT
1453   } {SQLITE_CONSTRAINT}
1454   verify_ex_errcode fkey2-17.1.3b SQLITE_CONSTRAINT_FOREIGNKEY
1455 } else {
1456   do_test fkey2-17.1.3 {
1457     sqlite3_step $STMT
1458   } {SQLITE_MISUSE}
1459 }
1460 do_test fkey2-17.1.4 {
1461   sqlite3_finalize $STMT
1462 } {SQLITE_CONSTRAINT}
1463 verify_ex_errcode fkey2-17.1.4b SQLITE_CONSTRAINT_FOREIGNKEY
1464 do_test fkey2-17.1.5 {
1465   execsql {
1466     INSERT INTO one VALUES(2, 3, 4);
1467     INSERT INTO one VALUES(3, 4, 5);
1468     INSERT INTO two VALUES(1, 2, 3);
1469     INSERT INTO two VALUES(2, 3, 4);
1470     INSERT INTO two VALUES(3, 4, 5);
1471   }
1472 } {1 1 1 1 1}
1473 do_test fkey2-17.1.6 {
1474   catchsql {
1475     BEGIN;
1476       INSERT INTO one VALUES(0, 0, 0);
1477       UPDATE two SET e=e+1, f=f+1;
1478   }
1479 } {1 {FOREIGN KEY constraint failed}}
1480 do_test fkey2-17.1.7 {
1481   execsql { SELECT * FROM one }
1482 } {1 2 3 2 3 4 3 4 5 0 0 0}
1483 do_test fkey2-17.1.8 {
1484   execsql { SELECT * FROM two }
1485 } {1 2 3 2 3 4 3 4 5}
1486 do_test fkey2-17.1.9 {
1487   execsql COMMIT
1488 } {}
1489 do_test fkey2-17.1.10 {
1490   execsql {
1491     CREATE TABLE three(
1492       g, h, i,
1493       FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED
1494     );
1495   }
1496 } {}
1497 do_test fkey2-17.1.11 {
1498   set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy]
1499   sqlite3_step $STMT
1500 } {SQLITE_ROW}
1501 do_test fkey2-17.1.12 {
1502   sqlite3_column_text $STMT 0
1503 } {1}
1504 do_test fkey2-17.1.13 {
1505   sqlite3_step $STMT
1506 } {SQLITE_CONSTRAINT}
1507 verify_ex_errcode fkey2-17.1.13b SQLITE_CONSTRAINT_FOREIGNKEY
1508 do_test fkey2-17.1.14 {
1509   sqlite3_finalize $STMT
1510 } {SQLITE_CONSTRAINT}
1511 verify_ex_errcode fkey2-17.1.14b SQLITE_CONSTRAINT_FOREIGNKEY
1512
1513 drop_all_tables
1514 do_test fkey2-17.2.1 {
1515   execsql {
1516     CREATE TABLE high("a'b!" PRIMARY KEY, b);
1517     CREATE TABLE low(
1518       c,
1519       "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE
1520     );
1521   }
1522 } {}
1523 do_test fkey2-17.2.2 {
1524   execsql {
1525     INSERT INTO high VALUES('a', 'b');
1526     INSERT INTO low VALUES('b', 'a');
1527   }
1528   db changes
1529 } {1}
1530 set nTotal [db total_changes]
1531 do_test fkey2-17.2.3 {
1532   execsql { UPDATE high SET "a'b!" = 'c' }
1533 } {1}
1534 do_test fkey2-17.2.4 {
1535   db changes
1536 } {1}
1537 do_test fkey2-17.2.5 {
1538   expr [db total_changes] - $nTotal
1539 } {2}
1540 do_test fkey2-17.2.6 {
1541   execsql { SELECT * FROM high ; SELECT * FROM low }
1542 } {c b b c}
1543 do_test fkey2-17.2.7 {
1544   execsql { DELETE FROM high }
1545 } {1}
1546 do_test fkey2-17.2.8 {
1547   db changes
1548 } {1}
1549 do_test fkey2-17.2.9 {
1550   expr [db total_changes] - $nTotal
1551 } {4}
1552 do_test fkey2-17.2.10 {
1553   execsql { SELECT * FROM high ; SELECT * FROM low }
1554 } {}
1555 execsql { PRAGMA count_changes = 0 }
1556
1557 #-------------------------------------------------------------------------
1558 # Test that the authorization callback works.
1559 #
1560
1561 ifcapable auth {
1562   do_test fkey2-18.1 {
1563     execsql {
1564       CREATE TABLE long(a, b PRIMARY KEY, c);
1565       CREATE TABLE short(d, e, f REFERENCES long);
1566       CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED);
1567     }
1568   } {}
1569
1570   proc auth {args} {eval lappend ::authargs [lrange $args 0 4]; return SQLITE_OK}
1571   db auth auth
1572
1573   # An insert on the parent table must read the child key of any deferred
1574   # foreign key constraints. But not the child key of immediate constraints.
1575   set authargs {}
1576   do_test fkey2-18.2 {
1577     execsql { INSERT INTO long VALUES(1, 2, 3) }
1578     set authargs
1579   } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}}
1580
1581   # An insert on the child table of an immediate constraint must read the
1582   # parent key columns (to see if it is a violation or not).
1583   set authargs {}
1584   do_test fkey2-18.3 {
1585     execsql { INSERT INTO short VALUES(1, 3, 2) }
1586     set authargs
1587   } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}}
1588
1589   # As must an insert on the child table of a deferred constraint.
1590   set authargs {}
1591   do_test fkey2-18.4 {
1592     execsql { INSERT INTO mid VALUES(1, 3, 2) }
1593     set authargs
1594   } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}}
1595
1596   do_test fkey2-18.5 {
1597     execsql {
1598       CREATE TABLE nought(a, b PRIMARY KEY, c);
1599       CREATE TABLE cross(d, e, f,
1600         FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE
1601       );
1602     }
1603     execsql { INSERT INTO nought VALUES(2, 1, 2) }
1604     execsql { INSERT INTO cross VALUES(0, 1, 0) }
1605     set authargs [list]
1606     execsql { UPDATE nought SET b = 5 }
1607     set authargs
1608   } {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 {}}
1609
1610   do_test fkey2-18.6 {
1611     execsql {SELECT * FROM cross}
1612   } {0 5 0}
1613
1614   do_test fkey2-18.7 {
1615     execsql {
1616       CREATE TABLE one(a INTEGER PRIMARY KEY, b);
1617       CREATE TABLE two(b, c REFERENCES one);
1618       INSERT INTO one VALUES(101, 102);
1619     }
1620     set authargs [list]
1621     execsql { INSERT INTO two VALUES(100, 101); }
1622     set authargs
1623   } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}}
1624
1625   # Return SQLITE_IGNORE to requests to read from the parent table. This
1626   # causes inserts of non-NULL keys into the child table to fail.
1627   #
1628   rename auth {}
1629   proc auth {args} {
1630     if {[lindex $args 1] == "long"} {return SQLITE_IGNORE}
1631     return SQLITE_OK
1632   }
1633   do_test fkey2-18.8 {
1634     catchsql { INSERT INTO short VALUES(1, 3, 2) }
1635   } {1 {FOREIGN KEY constraint failed}}
1636   do_test fkey2-18.9 {
1637     execsql { INSERT INTO short VALUES(1, 3, NULL) }
1638   } {}
1639   do_test fkey2-18.10 {
1640     execsql { SELECT * FROM short }
1641   } {1 3 2 1 3 {}}
1642   do_test fkey2-18.11 {
1643     catchsql { UPDATE short SET f = 2 WHERE f IS NULL }
1644   } {1 {FOREIGN KEY constraint failed}}
1645
1646   db auth {}
1647   unset authargs
1648 }
1649
1650
1651 do_test fkey2-19.1 {
1652   execsql {
1653     CREATE TABLE main(id INTEGER PRIMARY KEY);
1654     CREATE TABLE sub(id INT REFERENCES main(id));
1655     INSERT INTO main VALUES(1);
1656     INSERT INTO main VALUES(2);
1657     INSERT INTO sub VALUES(2);
1658   }
1659 } {}
1660 do_test fkey2-19.2 {
1661   set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy]
1662   sqlite3_bind_int $S 1 2
1663   sqlite3_step $S
1664 } {SQLITE_CONSTRAINT}
1665 verify_ex_errcode fkey2-19.2b SQLITE_CONSTRAINT_FOREIGNKEY
1666 do_test fkey2-19.3 {
1667   sqlite3_reset $S
1668 } {SQLITE_CONSTRAINT}
1669 verify_ex_errcode fkey2-19.3b SQLITE_CONSTRAINT_FOREIGNKEY
1670 do_test fkey2-19.4 {
1671   sqlite3_bind_int $S 1 1
1672   sqlite3_step $S
1673 } {SQLITE_DONE}
1674 do_test fkey2-19.4 {
1675   sqlite3_finalize $S
1676 } {SQLITE_OK}
1677
1678 drop_all_tables
1679 do_test fkey2-20.1 {
1680   execsql {
1681     CREATE TABLE pp(a PRIMARY KEY, b);
1682     CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp);
1683   }
1684 } {}
1685
1686 foreach {tn insert} {
1687   1 "INSERT"
1688   2 "INSERT OR IGNORE"
1689   3 "INSERT OR ABORT"
1690   4 "INSERT OR ROLLBACK"
1691   5 "INSERT OR REPLACE"
1692   6 "INSERT OR FAIL"
1693 } {
1694   do_test fkey2-20.2.$tn.1 {
1695     catchsql "$insert INTO cc VALUES(1, 2)"
1696   } {1 {FOREIGN KEY constraint failed}}
1697   do_test fkey2-20.2.$tn.2 {
1698     execsql { SELECT * FROM cc }
1699   } {}
1700   do_test fkey2-20.2.$tn.3 {
1701     execsql {
1702       BEGIN;
1703         INSERT INTO pp VALUES(2, 'two');
1704         INSERT INTO cc VALUES(1, 2);
1705     }
1706     catchsql "$insert INTO cc VALUES(3, 4)"
1707   } {1 {FOREIGN KEY constraint failed}}
1708   do_test fkey2-20.2.$tn.4 {
1709     execsql { COMMIT ; SELECT * FROM cc }
1710   } {1 2}
1711   do_test fkey2-20.2.$tn.5 {
1712     execsql { DELETE FROM cc ; DELETE FROM pp }
1713   } {}
1714 }
1715
1716 foreach {tn update} {
1717   1 "UPDATE"
1718   2 "UPDATE OR IGNORE"
1719   3 "UPDATE OR ABORT"
1720   4 "UPDATE OR ROLLBACK"
1721   5 "UPDATE OR REPLACE"
1722   6 "UPDATE OR FAIL"
1723 } {
1724   do_test fkey2-20.3.$tn.1 {
1725     execsql {
1726       INSERT INTO pp VALUES(2, 'two');
1727       INSERT INTO cc VALUES(1, 2);
1728     }
1729   } {}
1730   do_test fkey2-20.3.$tn.2 {
1731     catchsql "$update pp SET a = 1"
1732   } {1 {FOREIGN KEY constraint failed}}
1733   do_test fkey2-20.3.$tn.3 {
1734     execsql { SELECT * FROM pp }
1735   } {2 two}
1736   do_test fkey2-20.3.$tn.4 {
1737     catchsql "$update cc SET d = 1"
1738   } {1 {FOREIGN KEY constraint failed}}
1739   do_test fkey2-20.3.$tn.5 {
1740     execsql { SELECT * FROM cc }
1741   } {1 2}
1742   do_test fkey2-20.3.$tn.6 {
1743     execsql {
1744       BEGIN;
1745         INSERT INTO pp VALUES(3, 'three');
1746     }
1747     catchsql "$update pp SET a = 1 WHERE a = 2"
1748   } {1 {FOREIGN KEY constraint failed}}
1749   do_test fkey2-20.3.$tn.7 {
1750     execsql { COMMIT ; SELECT * FROM pp }
1751   } {2 two 3 three}
1752   do_test fkey2-20.3.$tn.8 {
1753     execsql {
1754       BEGIN;
1755         INSERT INTO cc VALUES(2, 2);
1756     }
1757     catchsql "$update cc SET d = 1 WHERE c = 1"
1758   } {1 {FOREIGN KEY constraint failed}}
1759   do_test fkey2-20.3.$tn.9 {
1760     execsql { COMMIT ; SELECT * FROM cc }
1761   } {1 2 2 2}
1762   do_test fkey2-20.3.$tn.10 {
1763     execsql { DELETE FROM cc ; DELETE FROM pp }
1764   } {}
1765 }
1766
1767 #-------------------------------------------------------------------------
1768 # The following block of tests, those prefixed with "fkey2-genfkey.", are
1769 # the same tests that were used to test the ".genfkey" command provided
1770 # by the shell tool. So these tests show that the built-in foreign key
1771 # implementation is more or less compatible with the triggers generated
1772 # by genfkey.
1773 #
1774 drop_all_tables
1775 do_test fkey2-genfkey.1.1 {
1776   execsql {
1777     CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
1778     CREATE TABLE t2(e REFERENCES t1, f);
1779     CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
1780   }
1781 } {}
1782 do_test fkey2-genfkey.1.2 {
1783   catchsql { INSERT INTO t2 VALUES(1, 2) }
1784 } {1 {FOREIGN KEY constraint failed}}
1785 do_test fkey2-genfkey.1.3 {
1786   execsql {
1787     INSERT INTO t1 VALUES(1, 2, 3);
1788     INSERT INTO t2 VALUES(1, 2);
1789   }
1790 } {}
1791 do_test fkey2-genfkey.1.4 {
1792   execsql { INSERT INTO t2 VALUES(NULL, 3) }
1793 } {}
1794 do_test fkey2-genfkey.1.5 {
1795   catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
1796 } {1 {FOREIGN KEY constraint failed}}
1797 do_test fkey2-genfkey.1.6 {
1798   execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
1799 } {}
1800 do_test fkey2-genfkey.1.7 {
1801   execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
1802 } {}
1803 do_test fkey2-genfkey.1.8 {
1804   catchsql { UPDATE t1 SET a = 10 }
1805 } {1 {FOREIGN KEY constraint failed}}
1806 do_test fkey2-genfkey.1.9 {
1807   catchsql { UPDATE t1 SET a = NULL }
1808 } {1 {datatype mismatch}}
1809 do_test fkey2-genfkey.1.10 {
1810   catchsql { DELETE FROM t1 }
1811 } {1 {FOREIGN KEY constraint failed}}
1812 do_test fkey2-genfkey.1.11 {
1813   execsql { UPDATE t2 SET e = NULL }
1814 } {}
1815 do_test fkey2-genfkey.1.12 {
1816   execsql {
1817     UPDATE t1 SET a = 10;
1818     DELETE FROM t1;
1819     DELETE FROM t2;
1820   }
1821 } {}
1822 do_test fkey2-genfkey.1.13 {
1823   execsql {
1824     INSERT INTO t3 VALUES(1, NULL, NULL);
1825     INSERT INTO t3 VALUES(1, 2, NULL);
1826     INSERT INTO t3 VALUES(1, NULL, 3);
1827   }
1828 } {}
1829 do_test fkey2-genfkey.1.14 {
1830   catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
1831 } {1 {FOREIGN KEY constraint failed}}
1832 do_test fkey2-genfkey.1.15 {
1833   execsql {
1834     INSERT INTO t1 VALUES(1, 1, 4);
1835     INSERT INTO t3 VALUES(3, 1, 4);
1836   }
1837 } {}
1838 do_test fkey2-genfkey.1.16 {
1839   catchsql { DELETE FROM t1 }
1840 } {1 {FOREIGN KEY constraint failed}}
1841 do_test fkey2-genfkey.1.17 {
1842   catchsql { UPDATE t1 SET b = 10}
1843 } {1 {FOREIGN KEY constraint failed}}
1844 do_test fkey2-genfkey.1.18 {
1845   execsql { UPDATE t1 SET a = 10}
1846 } {}
1847 do_test fkey2-genfkey.1.19 {
1848   catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
1849 } {1 {FOREIGN KEY constraint failed}}
1850
1851 drop_all_tables
1852 do_test fkey2-genfkey.2.1 {
1853   execsql {
1854     CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
1855     CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
1856     CREATE TABLE t3(g, h, i,
1857         FOREIGN KEY (h, i)
1858         REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
1859     );
1860   }
1861 } {}
1862 do_test fkey2-genfkey.2.2 {
1863   execsql {
1864     INSERT INTO t1 VALUES(1, 2, 3);
1865     INSERT INTO t1 VALUES(4, 5, 6);
1866     INSERT INTO t2 VALUES(1, 'one');
1867     INSERT INTO t2 VALUES(4, 'four');
1868   }
1869 } {}
1870 do_test fkey2-genfkey.2.3 {
1871   execsql {
1872     UPDATE t1 SET a = 2 WHERE a = 1;
1873     SELECT * FROM t2;
1874   }
1875 } {2 one 4 four}
1876 do_test fkey2-genfkey.2.4 {
1877   execsql {
1878     DELETE FROM t1 WHERE a = 4;
1879     SELECT * FROM t2;
1880   }
1881 } {2 one}
1882
1883 do_test fkey2-genfkey.2.5 {
1884   execsql {
1885     INSERT INTO t3 VALUES('hello', 2, 3);
1886     UPDATE t1 SET c = 2;
1887     SELECT * FROM t3;
1888   }
1889 } {hello 2 2}
1890 do_test fkey2-genfkey.2.6 {
1891   execsql {
1892     DELETE FROM t1;
1893     SELECT * FROM t3;
1894   }
1895 } {}
1896
1897 drop_all_tables
1898 do_test fkey2-genfkey.3.1 {
1899   execsql {
1900     CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));
1901     CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
1902     CREATE TABLE t3(g, h, i,
1903         FOREIGN KEY (h, i)
1904         REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
1905     );
1906   }
1907 } {}
1908 do_test fkey2-genfkey.3.2 {
1909   execsql {
1910     INSERT INTO t1 VALUES(1, 2, 3);
1911     INSERT INTO t1 VALUES(4, 5, 6);
1912     INSERT INTO t2 VALUES(1, 'one');
1913     INSERT INTO t2 VALUES(4, 'four');
1914   }
1915 } {}
1916 do_test fkey2-genfkey.3.3 {
1917   execsql {
1918     UPDATE t1 SET a = 2 WHERE a = 1;
1919     SELECT * FROM t2;
1920   }
1921 } {{} one 4 four}
1922 do_test fkey2-genfkey.3.4 {
1923   execsql {
1924     DELETE FROM t1 WHERE a = 4;
1925     SELECT * FROM t2;
1926   }
1927 } {{} one {} four}
1928 do_test fkey2-genfkey.3.5 {
1929   execsql {
1930     INSERT INTO t3 VALUES('hello', 2, 3);
1931     UPDATE t1 SET c = 2;
1932     SELECT * FROM t3;
1933   }
1934 } {hello {} {}}
1935 do_test fkey2-genfkey.3.6 {
1936   execsql {
1937     UPDATE t3 SET h = 2, i = 2;
1938     DELETE FROM t1;
1939     SELECT * FROM t3;
1940   }
1941 } {hello {} {}}
1942
1943 #-------------------------------------------------------------------------
1944 # Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been
1945 # fixed.
1946 #
1947 do_test fkey2-dd08e5.1.1 {
1948   execsql {
1949     PRAGMA foreign_keys=ON;
1950     CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b);
1951     CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
1952     INSERT INTO tdd08 VALUES(200,300);
1953
1954     CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b));
1955     INSERT INTO tdd08_b VALUES(100,200,300);
1956   }
1957 } {}
1958 do_test fkey2-dd08e5.1.2 {
1959   catchsql {
1960     DELETE FROM tdd08;
1961   }
1962 } {1 {FOREIGN KEY constraint failed}}
1963 do_test fkey2-dd08e5.1.3 {
1964   execsql {
1965     SELECT * FROM tdd08;
1966   }
1967 } {200 300}
1968 do_test fkey2-dd08e5.1.4 {
1969   catchsql {
1970     INSERT INTO tdd08_b VALUES(400,500,300);
1971   }
1972 } {1 {FOREIGN KEY constraint failed}}
1973 do_test fkey2-dd08e5.1.5 {
1974   catchsql {
1975     UPDATE tdd08_b SET x=x+1;
1976   }
1977 } {1 {FOREIGN KEY constraint failed}}
1978 do_test fkey2-dd08e5.1.6 {
1979   catchsql {
1980     UPDATE tdd08 SET a=a+1;
1981   }
1982 } {1 {FOREIGN KEY constraint failed}}
1983
1984 #-------------------------------------------------------------------------
1985 # Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
1986 # fixed.
1987 #
1988 do_test fkey2-ce7c13.1.1 {
1989   execsql {
1990     CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
1991     CREATE UNIQUE INDEX ice71 ON tce71(a,b);
1992     INSERT INTO tce71 VALUES(100,200);
1993     CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
1994     INSERT INTO tce72 VALUES(300,100,200);
1995     UPDATE tce71 set b = 200 where a = 100;
1996     SELECT * FROM tce71, tce72;
1997   }
1998 } {100 200 300 100 200}
1999 do_test fkey2-ce7c13.1.2 {
2000   catchsql {
2001     UPDATE tce71 set b = 201 where a = 100;
2002   }
2003 } {1 {FOREIGN KEY constraint failed}}
2004 do_test fkey2-ce7c13.1.3 {
2005   catchsql {
2006     UPDATE tce71 set a = 101 where a = 100;
2007   }
2008 } {1 {FOREIGN KEY constraint failed}}
2009 do_test fkey2-ce7c13.1.4 {
2010   execsql {
2011     CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
2012     INSERT INTO tce73 VALUES(100,200);
2013     CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
2014     INSERT INTO tce74 VALUES(300,100,200);
2015     UPDATE tce73 set b = 200 where a = 100;
2016     SELECT * FROM tce73, tce74;
2017   }
2018 } {100 200 300 100 200}
2019 do_test fkey2-ce7c13.1.5 {
2020   catchsql {
2021     UPDATE tce73 set b = 201 where a = 100;
2022   }
2023 } {1 {FOREIGN KEY constraint failed}}
2024 do_test fkey2-ce7c13.1.6 {
2025   catchsql {
2026     UPDATE tce73 set a = 101 where a = 100;
2027   }
2028 } {1 {FOREIGN KEY constraint failed}}
2029
2030 # 2015-04-16:  Foreign key errors propagate back up to the parser.
2031 #
2032 do_test fkey2-20150416-100 {
2033   db close
2034   sqlite3 db :memory:
2035   catchsql {
2036     PRAGMA foreign_keys=1;
2037     CREATE TABLE t1(x PRIMARY KEY);
2038     CREATE TABLE t(y REFERENCES t0(x)ON DELETE SET DEFAULT);
2039     CREATE TABLE t0(y REFERENCES t1 ON DELETE SET NULL);
2040     REPLACE INTO t1 SELECT(0);CREATE TABLE t2(x);CREATE TABLE t3;
2041   }
2042 } {1 {foreign key mismatch - "t" referencing "t0"}}
2043
2044 finish_test