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