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