[sql] Import reference version of SQLite 3.17..

third_party/sqlite/sqlite-src-3170000/test/fkey2.test

+# 2009 September 15
+#
+# The author disclaims copyright to this source code.  In place of
+# a legal notice, here is a blessing:
+#
+#    May you do good and not evil.
+#    May you find forgiveness for yourself and forgive others.
+#    May you share freely, never taking more than you give.
+#
+#***********************************************************************
+# This file implements regression tests for SQLite library.
+#
+# This file implements tests for foreign keys.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+ifcapable {!foreignkey||!trigger} {
+  finish_test
+  return
+}
+
+#-------------------------------------------------------------------------
+# Test structure:
+#
+# fkey2-1.*: Simple tests to check that immediate and deferred foreign key 
+#            constraints work when not inside a transaction.
+#            
+# fkey2-2.*: Tests to verify that deferred foreign keys work inside
+#            explicit transactions (i.e that processing really is deferred).
+#
+# fkey2-3.*: Tests that a statement transaction is rolled back if an
+#            immediate foreign key constraint is violated.
+#
+# fkey2-4.*: Test that FK actions may recurse even when recursive triggers
+#            are disabled.
+#
+# fkey2-5.*: Check that if foreign-keys are enabled, it is not possible
+#            to write to an FK column using the incremental blob API.
+#
+# fkey2-6.*: Test that FK processing is automatically disabled when 
+#            running VACUUM.
+#
+# fkey2-7.*: Test using an IPK as the key in the child (referencing) table.
+#
+# fkey2-8.*: Test that enabling/disabling foreign key support while a 
+#            transaction is active is not possible.
+#
+# fkey2-9.*: Test SET DEFAULT actions.
+#
+# fkey2-10.*: Test errors.
+#
+# fkey2-11.*: Test CASCADE actions.
+#
+# fkey2-12.*: Test RESTRICT actions.
+#
+# fkey2-13.*: Test that FK processing is performed when a row is REPLACED by
+#             an UPDATE or INSERT statement.
+#
+# fkey2-14.*: Test the ALTER TABLE and DROP TABLE commands.
+#
+# fkey2-15.*: Test that if there are no (known) outstanding foreign key 
+#             constraint violations in the database, inserting into a parent
+#             table or deleting from a child table does not cause SQLite
+#             to check if this has repaired an outstanding violation.
+#
+# fkey2-16.*: Test that rows that refer to themselves may be inserted, 
+#             updated and deleted.
+#
+# fkey2-17.*: Test that the "count_changes" pragma does not interfere with
+#             FK constraint processing.
+# 
+# fkey2-18.*: Test that the authorization callback is invoked when processing
+#             FK constraints.
+#
+# fkey2-20.*: Test that ON CONFLICT clauses specified as part of statements
+#             do not affect the operation of FK constraints.
+#
+# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
+#            command. Recycled to test the built-in implementation.
+#
+# fkey2-dd08e5.*:  Tests to verify that ticket dd08e5a988d00decc4a543daa8d
+#                  has been fixed.
+#
+
+
+execsql { PRAGMA foreign_keys = on }
+
+set FkeySimpleSchema {
+  PRAGMA foreign_keys = on;
+  CREATE TABLE t1(a PRIMARY KEY, b);
+  CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);
+
+  CREATE TABLE t3(a PRIMARY KEY, b);
+  CREATE TABLE t4(c REFERENCES t3 /D/, d);
+
+  CREATE TABLE t7(a, b INTEGER PRIMARY KEY);
+  CREATE TABLE t8(c REFERENCES t7 /D/, d);
+
+  CREATE TABLE t9(a REFERENCES nosuchtable, b);
+  CREATE TABLE t10(a REFERENCES t9(c) /D/, b);
+}
+
+
+set FkeySimpleTests {
+  1.1  "INSERT INTO t2 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
+  1.2  "INSERT INTO t1 VALUES(1, 2)"      {0 {}}
+  1.3  "INSERT INTO t2 VALUES(1, 3)"      {0 {}}
+  1.4  "INSERT INTO t2 VALUES(2, 4)"      {1 {FOREIGN KEY constraint failed}}
+  1.5  "INSERT INTO t2 VALUES(NULL, 4)"   {0 {}}
+  1.6  "UPDATE t2 SET c=2 WHERE d=4"      {1 {FOREIGN KEY constraint failed}}
+  1.7  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
+  1.9  "UPDATE t2 SET c=1 WHERE d=4"      {0 {}}
+  1.10 "UPDATE t2 SET c=NULL WHERE d=4"   {0 {}}
+  1.11 "DELETE FROM t1 WHERE a=1"         {1 {FOREIGN KEY constraint failed}}
+  1.12 "UPDATE t1 SET a = 2"              {1 {FOREIGN KEY constraint failed}}
+  1.13 "UPDATE t1 SET a = 1"              {0 {}}
+
+  2.1  "INSERT INTO t4 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
+  2.2  "INSERT INTO t3 VALUES(1, 2)"      {0 {}}
+  2.3  "INSERT INTO t4 VALUES(1, 3)"      {0 {}}
+
+  4.1  "INSERT INTO t8 VALUES(1, 3)"      {1 {FOREIGN KEY constraint failed}}
+  4.2  "INSERT INTO t7 VALUES(2, 1)"      {0 {}}
+  4.3  "INSERT INTO t8 VALUES(1, 3)"      {0 {}}
+  4.4  "INSERT INTO t8 VALUES(2, 4)"      {1 {FOREIGN KEY constraint failed}}
+  4.5  "INSERT INTO t8 VALUES(NULL, 4)"   {0 {}}
+  4.6  "UPDATE t8 SET c=2 WHERE d=4"      {1 {FOREIGN KEY constraint failed}}
+  4.7  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
+  4.9  "UPDATE t8 SET c=1 WHERE d=4"      {0 {}}
+  4.10 "UPDATE t8 SET c=NULL WHERE d=4"   {0 {}}
+  4.11 "DELETE FROM t7 WHERE b=1"         {1 {FOREIGN KEY constraint failed}}
+  4.12 "UPDATE t7 SET b = 2"              {1 {FOREIGN KEY constraint failed}}
+  4.13 "UPDATE t7 SET b = 1"              {0 {}}
+  4.14 "INSERT INTO t8 VALUES('a', 'b')"  {1 {FOREIGN KEY constraint failed}}
+  4.15 "UPDATE t7 SET b = 5"              {1 {FOREIGN KEY constraint failed}}
+  4.16 "UPDATE t7 SET rowid = 5"          {1 {FOREIGN KEY constraint failed}}
+  4.17 "UPDATE t7 SET a = 10"             {0 {}}
+
+  5.1  "INSERT INTO t9 VALUES(1, 3)"      {1 {no such table: main.nosuchtable}}
+  5.2  "INSERT INTO t10 VALUES(1, 3)"  
+                            {1 {foreign key mismatch - "t10" referencing "t9"}}
+}
+
+do_test fkey2-1.1.0 {
+  execsql [string map {/D/ {}} $FkeySimpleSchema]
+} {}
+foreach {tn zSql res} $FkeySimpleTests {
+  do_test fkey2-1.1.$tn.1 { catchsql $zSql } $res
+  do_test fkey2-1.1.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
+  do_test fkey2-1.1.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
+  do_test fkey2-1.1.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
+  do_test fkey2-1.1.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
+  do_test fkey2-1.1.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
+  do_test fkey2-1.1.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
+}
+drop_all_tables
+
+do_test fkey2-1.2.0 {
+  execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
+} {}
+foreach {tn zSql res} $FkeySimpleTests {
+  do_test fkey2-1.2.$tn { catchsql $zSql } $res
+  do_test fkey2-1.2.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
+  do_test fkey2-1.2.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
+  do_test fkey2-1.2.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
+  do_test fkey2-1.2.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
+  do_test fkey2-1.2.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
+  do_test fkey2-1.2.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
+}
+drop_all_tables
+
+do_test fkey2-1.3.0 {
+  execsql [string map {/D/ {}} $FkeySimpleSchema]
+  execsql { PRAGMA count_changes = 1 }
+} {}
+foreach {tn zSql res} $FkeySimpleTests {
+  if {$res == "0 {}"} { set res {0 1} }
+  do_test fkey2-1.3.$tn { catchsql $zSql } $res
+  do_test fkey2-1.3.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
+  do_test fkey2-1.3.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
+  do_test fkey2-1.3.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
+  do_test fkey2-1.3.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
+  do_test fkey2-1.3.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
+  do_test fkey2-1.3.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
+}
+execsql { PRAGMA count_changes = 0 }
+drop_all_tables
+
+do_test fkey2-1.4.0 {
+  execsql [string map {/D/ {}} $FkeySimpleSchema]
+  execsql { PRAGMA count_changes = 1 }
+} {}
+foreach {tn zSql res} $FkeySimpleTests {
+  if {$res == "0 {}"} { set res {0 1} }
+  execsql BEGIN
+  do_test fkey2-1.4.$tn { catchsql $zSql } $res
+  execsql COMMIT
+}
+execsql { PRAGMA count_changes = 0 }
+drop_all_tables
+
+# Special test: When the parent key is an IPK, make sure the affinity of
+# the IPK is not applied to the child key value before it is inserted
+# into the child table.
+do_test fkey2-1.5.1 {
+  execsql {
+    CREATE TABLE i(i INTEGER PRIMARY KEY);
+    CREATE TABLE j(j REFERENCES i);
+    INSERT INTO i VALUES(35);
+    INSERT INTO j VALUES('35.0');
+    SELECT j, typeof(j) FROM j;
+  }
+} {35.0 text}
+do_test fkey2-1.5.2 {
+  catchsql { DELETE FROM i }
+} {1 {FOREIGN KEY constraint failed}}
+
+# Same test using a regular primary key with integer affinity.
+drop_all_tables
+do_test fkey2-1.6.1 {
+  execsql {
+    CREATE TABLE i(i INT UNIQUE);
+    CREATE TABLE j(j REFERENCES i(i));
+    INSERT INTO i VALUES('35.0');
+    INSERT INTO j VALUES('35.0');
+    SELECT j, typeof(j) FROM j;
+    SELECT i, typeof(i) FROM i;
+  }
+} {35.0 text 35 integer}
+do_test fkey2-1.6.2 {
+  catchsql { DELETE FROM i }
+} {1 {FOREIGN KEY constraint failed}}
+
+# Use a collation sequence on the parent key.
+drop_all_tables
+do_test fkey2-1.7.1 {
+  execsql {
+    CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
+    CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
+    INSERT INTO i VALUES('SQLite');
+    INSERT INTO j VALUES('sqlite');
+  }
+  catchsql { DELETE FROM i }
+} {1 {FOREIGN KEY constraint failed}}
+
+# Use the parent key collation even if it is default and the child key
+# has an explicit value.
+drop_all_tables
+do_test fkey2-1.7.2 {
+  execsql {
+    CREATE TABLE i(i TEXT PRIMARY KEY);        -- Colseq is "BINARY"
+    CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
+    INSERT INTO i VALUES('SQLite');
+  }
+  catchsql { INSERT INTO j VALUES('sqlite') }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-1.7.3 {
+  execsql {
+    INSERT INTO i VALUES('sqlite');
+    INSERT INTO j VALUES('sqlite');
+    DELETE FROM i WHERE i = 'SQLite';
+  }
+  catchsql { DELETE FROM i WHERE i = 'sqlite' }
+} {1 {FOREIGN KEY constraint failed}}
+
+#-------------------------------------------------------------------------
+# This section (test cases fkey2-2.*) contains tests to check that the
+# deferred foreign key constraint logic works.
+#
+proc fkey2-2-test {tn nocommit sql {res {}}} {
+  if {$res eq "FKV"} {
+    set expected {1 {FOREIGN KEY constraint failed}}
+  } else {
+    set expected [list 0 $res]
+  }
+  do_test fkey2-2.$tn [list catchsql $sql] $expected
+  if {$nocommit} {
+    do_test fkey2-2.${tn}c {
+      catchsql COMMIT
+    } {1 {FOREIGN KEY constraint failed}}
+  }
+}
+
+fkey2-2-test 1 0 {
+  CREATE TABLE node(
+    nodeid PRIMARY KEY,
+    parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
+  );
+  CREATE TABLE leaf(
+    cellid PRIMARY KEY,
+    parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
+  );
+}
+
+fkey2-2-test 1  0 "INSERT INTO node VALUES(1, 0)"       FKV
+fkey2-2-test 2  0 "BEGIN"
+fkey2-2-test 3  1   "INSERT INTO node VALUES(1, 0)"
+fkey2-2-test 4  0   "UPDATE node SET parent = NULL"
+fkey2-2-test 5  0 "COMMIT"
+fkey2-2-test 6  0 "SELECT * FROM node" {1 {}}
+
+fkey2-2-test 7  0 "BEGIN"
+fkey2-2-test 8  1   "INSERT INTO leaf VALUES('a', 2)"
+fkey2-2-test 9  1   "INSERT INTO node VALUES(2, 0)"
+fkey2-2-test 10 0   "UPDATE node SET parent = 1 WHERE nodeid = 2"
+fkey2-2-test 11 0 "COMMIT"
+fkey2-2-test 12 0 "SELECT * FROM node" {1 {} 2 1}
+fkey2-2-test 13 0 "SELECT * FROM leaf" {a 2}
+
+fkey2-2-test 14 0 "BEGIN"
+fkey2-2-test 15 1   "DELETE FROM node WHERE nodeid = 2"
+fkey2-2-test 16 0   "INSERT INTO node VALUES(2, NULL)"
+fkey2-2-test 17 0 "COMMIT"
+fkey2-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}}
+fkey2-2-test 19 0 "SELECT * FROM leaf" {a 2}
+
+fkey2-2-test 20 0 "BEGIN"
+fkey2-2-test 21 0   "INSERT INTO leaf VALUES('b', 1)"
+fkey2-2-test 22 0   "SAVEPOINT save"
+fkey2-2-test 23 0     "DELETE FROM node WHERE nodeid = 1"
+fkey2-2-test 24 0   "ROLLBACK TO save"
+fkey2-2-test 25 0 "COMMIT"
+fkey2-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}}
+fkey2-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1}
+
+fkey2-2-test 28 0 "BEGIN"
+fkey2-2-test 29 0   "INSERT INTO leaf VALUES('c', 1)"
+fkey2-2-test 30 0   "SAVEPOINT save"
+fkey2-2-test 31 0     "DELETE FROM node WHERE nodeid = 1"
+fkey2-2-test 32 1   "RELEASE save"
+fkey2-2-test 33 1   "DELETE FROM leaf WHERE cellid = 'b'"
+fkey2-2-test 34 0   "DELETE FROM leaf WHERE cellid = 'c'"
+fkey2-2-test 35 0 "COMMIT"
+fkey2-2-test 36 0 "SELECT * FROM node" {2 {}} 
+fkey2-2-test 37 0 "SELECT * FROM leaf" {a 2}
+
+fkey2-2-test 38 0 "SAVEPOINT outer"
+fkey2-2-test 39 1   "INSERT INTO leaf VALUES('d', 3)"
+fkey2-2-test 40 1 "RELEASE outer"    FKV
+fkey2-2-test 41 1   "INSERT INTO leaf VALUES('e', 3)"
+fkey2-2-test 42 0   "INSERT INTO node VALUES(3, 2)"
+fkey2-2-test 43 0 "RELEASE outer"
+
+fkey2-2-test 44 0 "SAVEPOINT outer"
+fkey2-2-test 45 1   "DELETE FROM node WHERE nodeid=3"
+fkey2-2-test 47 0   "INSERT INTO node VALUES(3, 2)"
+fkey2-2-test 48 0 "ROLLBACK TO outer"
+fkey2-2-test 49 0 "RELEASE outer"
+
+fkey2-2-test 50 0 "SAVEPOINT outer"
+fkey2-2-test 51 1   "INSERT INTO leaf VALUES('f', 4)"
+fkey2-2-test 52 1   "SAVEPOINT inner"
+fkey2-2-test 53 1     "INSERT INTO leaf VALUES('g', 4)"
+fkey2-2-test 54 1  "RELEASE outer"   FKV
+fkey2-2-test 55 1   "ROLLBACK TO inner"
+fkey2-2-test 56 0  "COMMIT"          FKV
+fkey2-2-test 57 0   "INSERT INTO node VALUES(4, NULL)"
+fkey2-2-test 58 0 "RELEASE outer"
+fkey2-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}}
+fkey2-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4}
+
+# The following set of tests check that if a statement that affects 
+# multiple rows violates some foreign key constraints, then strikes a 
+# constraint that causes the statement-transaction to be rolled back, 
+# the deferred constraint counter is correctly reset to the value it 
+# had before the statement-transaction was opened.
+#
+fkey2-2-test 61 0 "BEGIN"
+fkey2-2-test 62 0   "DELETE FROM leaf"
+fkey2-2-test 63 0   "DELETE FROM node"
+fkey2-2-test 64 1   "INSERT INTO leaf VALUES('a', 1)"
+fkey2-2-test 65 1   "INSERT INTO leaf VALUES('b', 2)"
+fkey2-2-test 66 1   "INSERT INTO leaf VALUES('c', 1)"
+do_test fkey2-2-test-67 {
+  catchsql          "INSERT INTO node SELECT parent, 3 FROM leaf"
+} {1 {UNIQUE constraint failed: node.nodeid}}
+fkey2-2-test 68 0 "COMMIT"           FKV
+fkey2-2-test 69 1   "INSERT INTO node VALUES(1, NULL)"
+fkey2-2-test 70 0   "INSERT INTO node VALUES(2, NULL)"
+fkey2-2-test 71 0 "COMMIT"
+
+fkey2-2-test 72 0 "BEGIN"
+fkey2-2-test 73 1   "DELETE FROM node"
+fkey2-2-test 74 0   "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf"
+fkey2-2-test 75 0 "COMMIT"
+
+#-------------------------------------------------------------------------
+# Test cases fkey2-3.* test that a program that executes foreign key
+# actions (CASCADE, SET DEFAULT, SET NULL etc.) or tests FK constraints
+# opens a statement transaction if required.
+#
+# fkey2-3.1.*: Test UPDATE statements.
+# fkey2-3.2.*: Test DELETE statements.
+#
+drop_all_tables
+do_test fkey2-3.1.1 {
+  execsql {
+    CREATE TABLE ab(a PRIMARY KEY, b);
+    CREATE TABLE cd(
+      c PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE, 
+      d
+    );
+    CREATE TABLE ef(
+      e REFERENCES cd ON UPDATE CASCADE, 
+      f, CHECK (e!=5)
+    );
+  }
+} {}
+do_test fkey2-3.1.2 {
+  execsql {
+    INSERT INTO ab VALUES(1, 'b');
+    INSERT INTO cd VALUES(1, 'd');
+    INSERT INTO ef VALUES(1, 'e');
+  }
+} {}
+do_test fkey2-3.1.3 {
+  catchsql { UPDATE ab SET a = 5 }
+} {1 {CHECK constraint failed: ef}}
+do_test fkey2-3.1.4 {
+  execsql { SELECT * FROM ab }
+} {1 b}
+do_test fkey2-3.1.4 {
+  execsql BEGIN;
+  catchsql { UPDATE ab SET a = 5 }
+} {1 {CHECK constraint failed: ef}}
+do_test fkey2-3.1.5 {
+  execsql COMMIT;
+  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
+} {1 b 1 d 1 e}
+
+do_test fkey2-3.2.1 {
+  execsql BEGIN;
+  catchsql { DELETE FROM ab }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-3.2.2 {
+  execsql COMMIT
+  execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
+} {1 b 1 d 1 e}
+
+#-------------------------------------------------------------------------
+# Test cases fkey2-4.* test that recursive foreign key actions 
+# (i.e. CASCADE) are allowed even if recursive triggers are disabled.
+#
+drop_all_tables
+do_test fkey2-4.1 {
+  execsql {
+    CREATE TABLE t1(
+      node PRIMARY KEY, 
+      parent REFERENCES t1 ON DELETE CASCADE
+    );
+    CREATE TABLE t2(node PRIMARY KEY, parent);
+    CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN
+      DELETE FROM t2 WHERE parent = old.node;
+    END;
+    INSERT INTO t1 VALUES(1, NULL);
+    INSERT INTO t1 VALUES(2, 1);
+    INSERT INTO t1 VALUES(3, 1);
+    INSERT INTO t1 VALUES(4, 2);
+    INSERT INTO t1 VALUES(5, 2);
+    INSERT INTO t1 VALUES(6, 3);
+    INSERT INTO t1 VALUES(7, 3);
+    INSERT INTO t2 SELECT * FROM t1;
+  }
+} {}
+do_test fkey2-4.2 {
+  execsql { PRAGMA recursive_triggers = off }
+  execsql { 
+    BEGIN;
+      DELETE FROM t1 WHERE node = 1;
+      SELECT node FROM t1;
+  }
+} {}
+do_test fkey2-4.3 {
+  execsql { 
+      DELETE FROM t2 WHERE node = 1;
+      SELECT node FROM t2;
+    ROLLBACK;
+  }
+} {4 5 6 7}
+do_test fkey2-4.4 {
+  execsql { PRAGMA recursive_triggers = on }
+  execsql { 
+    BEGIN;
+      DELETE FROM t1 WHERE node = 1;
+      SELECT node FROM t1;
+  }
+} {}
+do_test fkey2-4.3 {
+  execsql { 
+      DELETE FROM t2 WHERE node = 1;
+      SELECT node FROM t2;
+    ROLLBACK;
+  }
+} {}
+
+#-------------------------------------------------------------------------
+# Test cases fkey2-5.* verify that the incremental blob API may not
+# write to a foreign key column while foreign-keys are enabled.
+#
+drop_all_tables
+ifcapable incrblob {
+  do_test fkey2-5.1 {
+    execsql {
+      CREATE TABLE t1(a PRIMARY KEY, b);
+      CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1(a));
+      INSERT INTO t1 VALUES('hello', 'world');
+      INSERT INTO t2 VALUES('key', 'hello');
+    }
+  } {}
+  do_test fkey2-5.2 {
+    set rc [catch { set fd [db incrblob t2 b 1] } msg]
+    list $rc $msg
+  } {1 {cannot open foreign key column for writing}}
+  do_test fkey2-5.3 {
+    set rc [catch { set fd [db incrblob -readonly t2 b 1] } msg]
+    close $fd
+    set rc
+  } {0}
+  do_test fkey2-5.4 {
+    execsql { PRAGMA foreign_keys = off }
+    set rc [catch { set fd [db incrblob t2 b 1] } msg]
+    close $fd
+    set rc
+  } {0}
+  do_test fkey2-5.5 {
+    execsql { PRAGMA foreign_keys = on }
+  } {}
+}
+
+drop_all_tables
+ifcapable vacuum {
+  do_test fkey2-6.1 {
+    execsql {
+      CREATE TABLE t1(a REFERENCES t2(c), b);
+      CREATE TABLE t2(c UNIQUE, b);
+      INSERT INTO t2 VALUES(1, 2);
+      INSERT INTO t1 VALUES(1, 2);
+      VACUUM;
+    }
+  } {}
+}
+
+#-------------------------------------------------------------------------
+# Test that it is possible to use an INTEGER PRIMARY KEY as the child key
+# of a foreign constraint.
+# 
+drop_all_tables
+do_test fkey2-7.1 {
+  execsql {
+    CREATE TABLE t1(a PRIMARY KEY, b);
+    CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b);
+  }
+} {}
+do_test fkey2-7.2 {
+  catchsql { INSERT INTO t2 VALUES(1, 'A'); }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-7.3 {
+  execsql { 
+    INSERT INTO t1 VALUES(1, 2);
+    INSERT INTO t1 VALUES(2, 3);
+    INSERT INTO t2 VALUES(1, 'A');
+  }
+} {}
+do_test fkey2-7.4 {
+  execsql { UPDATE t2 SET c = 2 }
+} {}
+do_test fkey2-7.5 {
+  catchsql { UPDATE t2 SET c = 3 }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-7.6 {
+  catchsql { DELETE FROM t1 WHERE a = 2 }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-7.7 {
+  execsql { DELETE FROM t1 WHERE a = 1 }
+} {}
+do_test fkey2-7.8 {
+  catchsql { UPDATE t1 SET a = 3 }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-7.9 {
+  catchsql { UPDATE t2 SET rowid = 3 }
+} {1 {FOREIGN KEY constraint failed}}
+
+#-------------------------------------------------------------------------
+# Test that it is not possible to enable/disable FK support while a
+# transaction is open.
+# 
+drop_all_tables
+proc fkey2-8-test {tn zSql value} {
+  do_test fkey-2.8.$tn.1 [list execsql $zSql] {}
+  do_test fkey-2.8.$tn.2 { execsql "PRAGMA foreign_keys" } $value
+}
+fkey2-8-test  1 { PRAGMA foreign_keys = 0     } 0
+fkey2-8-test  2 { PRAGMA foreign_keys = 1     } 1
+fkey2-8-test  3 { BEGIN                       } 1
+fkey2-8-test  4 { PRAGMA foreign_keys = 0     } 1
+fkey2-8-test  5 { COMMIT                      } 1
+fkey2-8-test  6 { PRAGMA foreign_keys = 0     } 0
+fkey2-8-test  7 { BEGIN                       } 0
+fkey2-8-test  8 { PRAGMA foreign_keys = 1     } 0
+fkey2-8-test  9 { COMMIT                      } 0
+fkey2-8-test 10 { PRAGMA foreign_keys = 1     } 1
+fkey2-8-test 11 { PRAGMA foreign_keys = off   } 0
+fkey2-8-test 12 { PRAGMA foreign_keys = on    } 1
+fkey2-8-test 13 { PRAGMA foreign_keys = no    } 0
+fkey2-8-test 14 { PRAGMA foreign_keys = yes   } 1
+fkey2-8-test 15 { PRAGMA foreign_keys = false } 0
+fkey2-8-test 16 { PRAGMA foreign_keys = true  } 1
+
+#-------------------------------------------------------------------------
+# The following tests, fkey2-9.*, test SET DEFAULT actions.
+#
+drop_all_tables
+do_test fkey2-9.1.1 {
+  execsql {
+    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
+    CREATE TABLE t2(
+      c INTEGER PRIMARY KEY,
+      d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT
+    );
+    DELETE FROM t1;
+  }
+} {}
+do_test fkey2-9.1.2 {
+  execsql {
+    INSERT INTO t1 VALUES(1, 'one');
+    INSERT INTO t1 VALUES(2, 'two');
+    INSERT INTO t2 VALUES(1, 2);
+    SELECT * FROM t2;
+    DELETE FROM t1 WHERE a = 2;
+    SELECT * FROM t2;
+  }
+} {1 2 1 1}
+do_test fkey2-9.1.3 {
+  execsql {
+    INSERT INTO t1 VALUES(2, 'two');
+    UPDATE t2 SET d = 2;
+    DELETE FROM t1 WHERE a = 1;
+    SELECT * FROM t2;
+  }
+} {1 2}
+do_test fkey2-9.1.4 {
+  execsql { SELECT * FROM t1 }
+} {2 two}
+do_test fkey2-9.1.5 {
+  catchsql { DELETE FROM t1 }
+} {1 {FOREIGN KEY constraint failed}}
+
+do_test fkey2-9.2.1 {
+  execsql {
+    CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
+    CREATE TABLE cc(d DEFAULT 3, e DEFAULT 1, f DEFAULT 2,
+        FOREIGN KEY(f, d) REFERENCES pp 
+        ON UPDATE SET DEFAULT 
+        ON DELETE SET NULL
+    );
+    INSERT INTO pp VALUES(1, 2, 3);
+    INSERT INTO pp VALUES(4, 5, 6);
+    INSERT INTO pp VALUES(7, 8, 9);
+  }
+} {}
+do_test fkey2-9.2.2 {
+  execsql {
+    INSERT INTO cc VALUES(6, 'A', 5);
+    INSERT INTO cc VALUES(6, 'B', 5);
+    INSERT INTO cc VALUES(9, 'A', 8);
+    INSERT INTO cc VALUES(9, 'B', 8);
+    UPDATE pp SET b = 1 WHERE a = 7;
+    SELECT * FROM cc;
+  }
+} {6 A 5 6 B 5 3 A 2 3 B 2}
+do_test fkey2-9.2.3 {
+  execsql {
+    DELETE FROM pp WHERE a = 4;
+    SELECT * FROM cc;
+  }
+} {{} A {} {} B {} 3 A 2 3 B 2}
+do_execsql_test fkey2-9.3.0 {
+  CREATE TABLE t3(x PRIMARY KEY REFERENCES t3 ON DELETE SET NULL);
+  INSERT INTO t3(x) VALUES(12345);
+  DROP TABLE t3;
+} {}
+
+#-------------------------------------------------------------------------
+# The following tests, fkey2-10.*, test "foreign key mismatch" and 
+# other errors.
+#
+set tn 0
+foreach zSql [list {
+  CREATE TABLE p(a PRIMARY KEY, b);
+  CREATE TABLE c(x REFERENCES p(c));
+} {
+  CREATE TABLE c(x REFERENCES v(y));
+  CREATE VIEW v AS SELECT x AS y FROM c;
+} {
+  CREATE TABLE p(a, b, PRIMARY KEY(a, b));
+  CREATE TABLE c(x REFERENCES p);
+} {
+  CREATE TABLE p(a COLLATE binary, b);
+  CREATE UNIQUE INDEX i ON p(a COLLATE nocase);
+  CREATE TABLE c(x REFERENCES p(a));
+}] {
+  drop_all_tables
+  do_test fkey2-10.1.[incr tn] {
+    execsql $zSql
+    catchsql { INSERT INTO c DEFAULT VALUES }
+  } {/1 {foreign key mismatch - "c" referencing "."}/}
+}
+
+# "rowid" cannot be used as part of a child or parent key definition 
+# unless it happens to be the name of an explicitly declared column.
+#
+do_test fkey2-10.2.1 {
+  drop_all_tables
+  catchsql {
+    CREATE TABLE t1(a PRIMARY KEY, b);
+    CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a));
+  }
+} {1 {unknown column "rowid" in foreign key definition}}
+do_test fkey2-10.2.2 {
+  drop_all_tables
+  catchsql {
+    CREATE TABLE t1(a PRIMARY KEY, b);
+    CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a));
+  }
+} {0 {}}
+do_test fkey2-10.2.1 {
+  drop_all_tables
+  catchsql {
+    CREATE TABLE t1(a, b);
+    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
+    INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
+    INSERT INTO t2 VALUES(1, 1);
+  }
+} {1 {foreign key mismatch - "t2" referencing "t1"}}
+do_test fkey2-10.2.2 {
+  drop_all_tables
+  catchsql {
+    CREATE TABLE t1(rowid PRIMARY KEY, b);
+    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
+    INSERT INTO t1(rowid, b) VALUES(1, 1);
+    INSERT INTO t2 VALUES(1, 1);
+  }
+} {0 {}}
+
+
+#-------------------------------------------------------------------------
+# The following tests, fkey2-11.*, test CASCADE actions.
+#
+drop_all_tables
+do_test fkey2-11.1.1 {
+  execsql {
+    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, rowid, _rowid_, oid);
+    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
+
+    INSERT INTO t1 VALUES(10, 100, 'abc', 'def', 'ghi');
+    INSERT INTO t2 VALUES(10, 100);
+    UPDATE t1 SET a = 15;
+    SELECT * FROM t2;
+  }
+} {15 100}
+
+#-------------------------------------------------------------------------
+# The following tests, fkey2-12.*, test RESTRICT actions.
+#
+drop_all_tables
+do_test fkey2-12.1.1 {
+  execsql {
+    CREATE TABLE t1(a, b PRIMARY KEY);
+    CREATE TABLE t2(
+      x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED 
+    );
+    INSERT INTO t1 VALUES(1, 'one');
+    INSERT INTO t1 VALUES(2, 'two');
+    INSERT INTO t1 VALUES(3, 'three');
+  }
+} {}
+do_test fkey2-12.1.2 { 
+  execsql "BEGIN"
+  execsql "INSERT INTO t2 VALUES('two')"
+} {}
+do_test fkey2-12.1.3 { 
+  execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'"
+} {}
+do_test fkey2-12.1.4 { 
+  catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'"
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-12.1.5 { 
+  execsql "DELETE FROM t1 WHERE b = 'two'"
+} {}
+do_test fkey2-12.1.6 { 
+  catchsql "COMMIT"
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-12.1.7 { 
+  execsql {
+    INSERT INTO t1 VALUES(2, 'two');
+    COMMIT;
+  }
+} {}
+
+drop_all_tables
+do_test fkey2-12.2.1 {
+  execsql {
+    CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY);
+    CREATE TRIGGER tt1 AFTER DELETE ON t1 
+      WHEN EXISTS ( SELECT 1 FROM t2 WHERE old.x = y )
+    BEGIN
+      INSERT INTO t1 VALUES(old.x);
+    END;
+    CREATE TABLE t2(y REFERENCES t1);
+    INSERT INTO t1 VALUES('A');
+    INSERT INTO t1 VALUES('B');
+    INSERT INTO t2 VALUES('a');
+    INSERT INTO t2 VALUES('b');
+
+    SELECT * FROM t1;
+    SELECT * FROM t2;
+  }
+} {A B a b}
+do_test fkey2-12.2.2 {
+  execsql { DELETE FROM t1 }
+  execsql {
+    SELECT * FROM t1;
+    SELECT * FROM t2;
+  }
+} {A B a b}
+do_test fkey2-12.2.3 {
+  execsql {
+    DROP TABLE t2;
+    CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT);
+    INSERT INTO t2 VALUES('a');
+    INSERT INTO t2 VALUES('b');
+  }
+  catchsql { DELETE FROM t1 }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-12.2.4 {
+  execsql {
+    SELECT * FROM t1;
+    SELECT * FROM t2;
+  }
+} {A B a b}
+
+drop_all_tables
+do_test fkey2-12.3.1 {
+  execsql {
+    CREATE TABLE up(
+      c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
+      c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
+      c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
+      c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
+      PRIMARY KEY(c34, c35)
+    );
+    CREATE TABLE down(
+      c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
+      c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
+      c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
+      c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
+      FOREIGN KEY(c39, c38) REFERENCES up ON UPDATE CASCADE
+    );
+  }
+} {}
+do_test fkey2-12.3.2 {
+  execsql {
+    INSERT INTO up(c34, c35) VALUES('yes', 'no');
+    INSERT INTO down(c39, c38) VALUES('yes', 'no');
+    UPDATE up SET c34 = 'possibly';
+    SELECT c38, c39 FROM down;
+    DELETE FROM down;
+  }
+} {no possibly}
+do_test fkey2-12.3.3 {
+  catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-12.3.4 {
+  execsql { 
+    INSERT INTO up(c34, c35) VALUES('yes', 'no');
+    INSERT INTO down(c39, c38) VALUES('yes', 'no');
+  }
+  catchsql { DELETE FROM up WHERE c34 = 'yes' }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-12.3.5 {
+  execsql { 
+    DELETE FROM up WHERE c34 = 'possibly';
+    SELECT c34, c35 FROM up;
+    SELECT c39, c38 FROM down;
+  }
+} {yes no yes no}
+
+#-------------------------------------------------------------------------
+# The following tests, fkey2-13.*, test that FK processing is performed
+# when rows are REPLACEd.
+#
+drop_all_tables
+do_test fkey2-13.1.1 {
+  execsql {
+    CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c));
+    CREATE TABLE cc(d, e, f UNIQUE, FOREIGN KEY(d, e) REFERENCES pp);
+    INSERT INTO pp VALUES(1, 2, 3);
+    INSERT INTO cc VALUES(2, 3, 1);
+  }
+} {}
+foreach {tn stmt} {
+  1   "REPLACE INTO pp VALUES(1, 4, 5)"
+  2   "REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 3, 4)"
+} {
+  do_test fkey2-13.1.$tn.1 {
+    catchsql $stmt
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-13.1.$tn.2 {
+    execsql {
+      SELECT * FROM pp;
+      SELECT * FROM cc;
+    }
+  } {1 2 3 2 3 1}
+  do_test fkey2-13.1.$tn.3 {
+    execsql BEGIN;
+    catchsql $stmt
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-13.1.$tn.4 {
+    execsql {
+      COMMIT;
+      SELECT * FROM pp;
+      SELECT * FROM cc;
+    }
+  } {1 2 3 2 3 1}
+}
+do_test fkey2-13.1.3 {
+  execsql { 
+    REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 2, 3);
+    SELECT rowid, * FROM pp;
+    SELECT * FROM cc;
+  }
+} {1 2 2 3 2 3 1}
+do_test fkey2-13.1.4 {
+  execsql { 
+    REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3);
+    SELECT rowid, * FROM pp;
+    SELECT * FROM cc;
+  }
+} {2 2 2 3 2 3 1}
+
+#-------------------------------------------------------------------------
+# The following tests, fkey2-14.*, test that the "DROP TABLE" and "ALTER
+# TABLE" commands work as expected wrt foreign key constraints.
+#
+# fkey2-14.1*: ALTER TABLE ADD COLUMN
+# fkey2-14.2*: ALTER TABLE RENAME TABLE
+# fkey2-14.3*: DROP TABLE
+#
+drop_all_tables
+ifcapable altertable {
+  do_test fkey2-14.1.1 {
+    # Adding a column with a REFERENCES clause is not supported.
+    execsql { 
+      CREATE TABLE t1(a PRIMARY KEY);
+      CREATE TABLE t2(a, b);
+    }
+    catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
+  } {0 {}}
+  do_test fkey2-14.1.2 {
+    catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
+  } {0 {}}
+  do_test fkey2-14.1.3 {
+    catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
+  } {0 {}}
+  do_test fkey2-14.1.4 {
+    catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
+  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
+  do_test fkey2-14.1.5 {
+    catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
+  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
+  do_test fkey2-14.1.6 {
+    execsql { 
+      PRAGMA foreign_keys = off;
+      ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
+      PRAGMA foreign_keys = on;
+      SELECT sql FROM sqlite_master WHERE name='t2';
+    }
+  } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
+  
+  
+  # Test the sqlite_rename_parent() function directly.
+  #
+  proc test_rename_parent {zCreate zOld zNew} {
+    db eval {SELECT sqlite_rename_parent($zCreate, $zOld, $zNew)}
+  }
+  do_test fkey2-14.2.1.1 {
+    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
+  } {{CREATE TABLE t1(a REFERENCES "t3")}}
+  do_test fkey2-14.2.1.2 {
+    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
+  } {{CREATE TABLE t1(a REFERENCES t2)}}
+  do_test fkey2-14.2.1.3 {
+    test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
+  } {{CREATE TABLE t1(a REFERENCES "t3")}}
+  
+  # Test ALTER TABLE RENAME TABLE a bit.
+  #
+  do_test fkey2-14.2.2.1 {
+    drop_all_tables
+    execsql {
+      CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1);
+      CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
+      CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
+    }
+    execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
+  } [list \
+    {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)}                     \
+    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)}    \
+    {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
+  ]
+  do_test fkey2-14.2.2.2 {
+    execsql { ALTER TABLE t1 RENAME TO t4 }
+    execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
+  } [list \
+    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
+    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
+    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
+  ]
+  do_test fkey2-14.2.2.3 {
+    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-14.2.2.4 {
+    execsql { INSERT INTO t4 VALUES(1, NULL) }
+  } {}
+  do_test fkey2-14.2.2.5 {
+    catchsql { UPDATE t4 SET b = 5 }
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-14.2.2.6 {
+    catchsql { UPDATE t4 SET b = 1 }
+  } {0 {}}
+  do_test fkey2-14.2.2.7 {
+    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
+  } {}
+
+  # Repeat for TEMP tables
+  #
+  drop_all_tables
+  do_test fkey2-14.1tmp.1 {
+    # Adding a column with a REFERENCES clause is not supported.
+    execsql { 
+      CREATE TEMP TABLE t1(a PRIMARY KEY);
+      CREATE TEMP TABLE t2(a, b);
+    }
+    catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
+  } {0 {}}
+  do_test fkey2-14.1tmp.2 {
+    catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
+  } {0 {}}
+  do_test fkey2-14.1tmp.3 {
+    catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
+  } {0 {}}
+  do_test fkey2-14.1tmp.4 {
+    catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
+  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
+  do_test fkey2-14.1tmp.5 {
+    catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
+  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
+  do_test fkey2-14.1tmp.6 {
+    execsql { 
+      PRAGMA foreign_keys = off;
+      ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
+      PRAGMA foreign_keys = on;
+      SELECT sql FROM temp.sqlite_master WHERE name='t2';
+    }
+  } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
+
+  do_test fkey2-14.2tmp.1.1 {
+    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
+  } {{CREATE TABLE t1(a REFERENCES "t3")}}
+  do_test fkey2-14.2tmp.1.2 {
+    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
+  } {{CREATE TABLE t1(a REFERENCES t2)}}
+  do_test fkey2-14.2tmp.1.3 {
+    test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
+  } {{CREATE TABLE t1(a REFERENCES "t3")}}
+  
+  # Test ALTER TABLE RENAME TABLE a bit.
+  #
+  do_test fkey2-14.2tmp.2.1 {
+    drop_all_tables
+    execsql {
+      CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1);
+      CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
+      CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
+    }
+    execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
+  } [list \
+    {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)}                     \
+    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)}    \
+    {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
+  ]
+  do_test fkey2-14.2tmp.2.2 {
+    execsql { ALTER TABLE t1 RENAME TO t4 }
+    execsql { SELECT sql FROM temp.sqlite_master WHERE type = 'table'}
+  } [list \
+    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
+    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
+    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
+  ]
+  do_test fkey2-14.2tmp.2.3 {
+    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-14.2tmp.2.4 {
+    execsql { INSERT INTO t4 VALUES(1, NULL) }
+  } {}
+  do_test fkey2-14.2tmp.2.5 {
+    catchsql { UPDATE t4 SET b = 5 }
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-14.2tmp.2.6 {
+    catchsql { UPDATE t4 SET b = 1 }
+  } {0 {}}
+  do_test fkey2-14.2tmp.2.7 {
+    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
+  } {}
+
+  # Repeat for ATTACH-ed tables
+  #
+  drop_all_tables
+  do_test fkey2-14.1aux.1 {
+    # Adding a column with a REFERENCES clause is not supported.
+    execsql { 
+      ATTACH ':memory:' AS aux;
+      CREATE TABLE aux.t1(a PRIMARY KEY);
+      CREATE TABLE aux.t2(a, b);
+    }
+    catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
+  } {0 {}}
+  do_test fkey2-14.1aux.2 {
+    catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
+  } {0 {}}
+  do_test fkey2-14.1aux.3 {
+    catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
+  } {0 {}}
+  do_test fkey2-14.1aux.4 {
+    catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
+  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
+  do_test fkey2-14.1aux.5 {
+    catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
+  } {1 {Cannot add a REFERENCES column with non-NULL default value}}
+  do_test fkey2-14.1aux.6 {
+    execsql { 
+      PRAGMA foreign_keys = off;
+      ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
+      PRAGMA foreign_keys = on;
+      SELECT sql FROM aux.sqlite_master WHERE name='t2';
+    }
+  } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
+
+  do_test fkey2-14.2aux.1.1 {
+    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
+  } {{CREATE TABLE t1(a REFERENCES "t3")}}
+  do_test fkey2-14.2aux.1.2 {
+    test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
+  } {{CREATE TABLE t1(a REFERENCES t2)}}
+  do_test fkey2-14.2aux.1.3 {
+    test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
+  } {{CREATE TABLE t1(a REFERENCES "t3")}}
+  
+  # Test ALTER TABLE RENAME TABLE a bit.
+  #
+  do_test fkey2-14.2aux.2.1 {
+    drop_all_tables
+    execsql {
+      CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1);
+      CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
+      CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
+    }
+    execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
+  } [list \
+    {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)}                     \
+    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)}    \
+    {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)}  \
+  ]
+  do_test fkey2-14.2aux.2.2 {
+    execsql { ALTER TABLE t1 RENAME TO t4 }
+    execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
+  } [list \
+    {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")}                    \
+    {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)}     \
+    {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
+  ]
+  do_test fkey2-14.2aux.2.3 {
+    catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-14.2aux.2.4 {
+    execsql { INSERT INTO t4 VALUES(1, NULL) }
+  } {}
+  do_test fkey2-14.2aux.2.5 {
+    catchsql { UPDATE t4 SET b = 5 }
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-14.2aux.2.6 {
+    catchsql { UPDATE t4 SET b = 1 }
+  } {0 {}}
+  do_test fkey2-14.2aux.2.7 {
+    execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
+  } {}
+}
+
+do_test fkey-2.14.3.1 {
+  drop_all_tables
+  execsql {
+    CREATE TABLE t1(a, b REFERENCES nosuchtable);
+    DROP TABLE t1;
+  }
+} {}
+do_test fkey-2.14.3.2 {
+  execsql {
+    CREATE TABLE t1(a PRIMARY KEY, b);
+    INSERT INTO t1 VALUES('a', 1);
+    CREATE TABLE t2(x REFERENCES t1);
+    INSERT INTO t2 VALUES('a');
+  }
+} {}
+do_test fkey-2.14.3.3 {
+  catchsql { DROP TABLE t1 }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey-2.14.3.4 {
+  execsql {
+    DELETE FROM t2;
+    DROP TABLE t1;
+  }
+} {}
+do_test fkey-2.14.3.4 {
+  catchsql { INSERT INTO t2 VALUES('x') }
+} {1 {no such table: main.t1}}
+do_test fkey-2.14.3.5 {
+  execsql {
+    CREATE TABLE t1(x PRIMARY KEY);
+    INSERT INTO t1 VALUES('x');
+  }
+  execsql { INSERT INTO t2 VALUES('x') }
+} {}
+do_test fkey-2.14.3.6 {
+  catchsql { DROP TABLE t1 }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey-2.14.3.7 {
+  execsql {
+    DROP TABLE t2;
+    DROP TABLE t1;
+  }
+} {}
+do_test fkey-2.14.3.8 {
+  execsql {
+    CREATE TABLE pp(x, y, PRIMARY KEY(x, y));
+    CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
+  }
+  catchsql { INSERT INTO cc VALUES(1, 2) }
+} {1 {foreign key mismatch - "cc" referencing "pp"}}
+do_test fkey-2.14.3.9 {
+  execsql { DROP TABLE cc }
+} {}
+do_test fkey-2.14.3.10 {
+  execsql {
+    CREATE TABLE cc(a, b, 
+      FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED
+    );
+  }
+  execsql {
+    INSERT INTO pp VALUES('a', 'b');
+    INSERT INTO cc VALUES('a', 'b');
+    BEGIN;
+      DROP TABLE pp;
+      CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
+      INSERT INTO pp VALUES(1, 'a', 'b');
+    COMMIT;
+  }
+} {}
+do_test fkey-2.14.3.11 {
+  execsql { 
+    BEGIN;
+      DROP TABLE cc;
+      DROP TABLE pp;
+    COMMIT;
+  }
+} {}
+do_test fkey-2.14.3.12 {
+  execsql {
+    CREATE TABLE b1(a, b);
+    CREATE TABLE b2(a, b REFERENCES b1);
+    DROP TABLE b1;
+  }
+} {}
+do_test fkey-2.14.3.13 {
+  execsql {
+    CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED);
+    DROP TABLE b2;
+  }
+} {}
+
+# Test that nothing goes wrong when dropping a table that refers to a view.
+# Or dropping a view that an existing FK (incorrectly) refers to. Or either
+# of the above scenarios with a virtual table.
+drop_all_tables
+do_test fkey-2.14.4.1 {
+  execsql {
+    CREATE TABLE t1(x REFERENCES v); 
+    CREATE VIEW v AS SELECT * FROM t1;
+  }
+} {}
+do_test fkey-2.14.4.2 {
+  execsql {
+    DROP VIEW v;
+  }
+} {}
+ifcapable vtab {
+  register_echo_module db
+  do_test fkey-2.14.4.3 {
+    execsql { CREATE VIRTUAL TABLE v USING echo(t1) }
+  } {}
+  do_test fkey-2.14.4.2 {
+    execsql {
+      DROP TABLE v;
+    }
+  } {}
+}
+
+#-------------------------------------------------------------------------
+# The following tests, fkey2-15.*, test that unnecessary FK related scans 
+# and lookups are avoided when the constraint counters are zero.
+#
+drop_all_tables
+proc execsqlS {zSql} {
+  set ::sqlite_search_count 0
+  set ::sqlite_found_count 0
+  set res [uplevel [list execsql $zSql]]
+  concat [expr $::sqlite_found_count + $::sqlite_search_count] $res
+}
+do_test fkey2-15.1.1 {
+  execsql {
+    CREATE TABLE pp(a PRIMARY KEY, b);
+    CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED);
+    INSERT INTO pp VALUES(1, 'one');
+    INSERT INTO pp VALUES(2, 'two');
+    INSERT INTO cc VALUES('neung', 1);
+    INSERT INTO cc VALUES('song', 2);
+  }
+} {}
+do_test fkey2-15.1.2 {
+  execsqlS { INSERT INTO pp VALUES(3, 'three') }
+} {0}
+do_test fkey2-15.1.3 {
+  execsql {
+    BEGIN;
+      INSERT INTO cc VALUES('see', 4);    -- Violates deferred constraint
+  }
+  execsqlS { INSERT INTO pp VALUES(5, 'five') }
+} {2}
+do_test fkey2-15.1.4 {
+  execsql { DELETE FROM cc WHERE x = 'see' }
+  execsqlS { INSERT INTO pp VALUES(6, 'six') }
+} {0}
+do_test fkey2-15.1.5 {
+  execsql COMMIT
+} {}
+do_test fkey2-15.1.6 {
+  execsql BEGIN
+  execsqlS {
+    DELETE FROM cc WHERE x = 'neung';
+    ROLLBACK;
+  }
+} {1}
+do_test fkey2-15.1.7 {
+  execsql { 
+    BEGIN;
+    DELETE FROM pp WHERE a = 2;
+  }
+  execsqlS {
+    DELETE FROM cc WHERE x = 'neung';
+    ROLLBACK;
+  }
+} {2}
+
+#-------------------------------------------------------------------------
+# This next block of tests, fkey2-16.*, test that rows that refer to
+# themselves may be inserted and deleted.
+#
+foreach {tn zSchema} {
+  1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) }
+  2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) }
+  3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) }
+} {
+  drop_all_tables
+  do_test fkey2-16.1.$tn.1 {
+    execsql $zSchema
+    execsql { INSERT INTO self VALUES(13, 13) }
+  } {}
+  do_test fkey2-16.1.$tn.2 {
+    execsql { UPDATE self SET a = 14, b = 14 }
+  } {}
+
+  do_test fkey2-16.1.$tn.3 {
+    catchsql { UPDATE self SET b = 15 }
+  } {1 {FOREIGN KEY constraint failed}}
+
+  do_test fkey2-16.1.$tn.4 {
+    catchsql { UPDATE self SET a = 15 }
+  } {1 {FOREIGN KEY constraint failed}}
+
+  do_test fkey2-16.1.$tn.5 {
+    catchsql { UPDATE self SET a = 15, b = 16 }
+  } {1 {FOREIGN KEY constraint failed}}
+
+  do_test fkey2-16.1.$tn.6 {
+    catchsql { UPDATE self SET a = 17, b = 17 }
+  } {0 {}}
+
+  do_test fkey2-16.1.$tn.7 {
+    execsql { DELETE FROM self }
+  } {}
+  do_test fkey2-16.1.$tn.8 {
+    catchsql { INSERT INTO self VALUES(20, 21) }
+  } {1 {FOREIGN KEY constraint failed}}
+}
+
+#-------------------------------------------------------------------------
+# This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes"
+# is turned on statements that violate immediate FK constraints return
+# SQLITE_CONSTRAINT immediately, not after returning a number of rows.
+# Whereas statements that violate deferred FK constraints return the number
+# of rows before failing.
+#
+# Also test that rows modified by FK actions are not counted in either the
+# returned row count or the values returned by sqlite3_changes(). Like
+# trigger related changes, they are included in sqlite3_total_changes() though.
+#
+drop_all_tables
+do_test fkey2-17.1.1 {
+  execsql { PRAGMA count_changes = 1 }
+  execsql { 
+    CREATE TABLE one(a, b, c, UNIQUE(b, c));
+    CREATE TABLE two(d, e, f, FOREIGN KEY(e, f) REFERENCES one(b, c));
+    INSERT INTO one VALUES(1, 2, 3);
+  }
+} {1}
+do_test fkey2-17.1.2 {
+  set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy]
+  sqlite3_step $STMT
+} {SQLITE_CONSTRAINT}
+verify_ex_errcode fkey2-17.1.2b SQLITE_CONSTRAINT_FOREIGNKEY
+ifcapable autoreset {
+  do_test fkey2-17.1.3 {
+    sqlite3_step $STMT
+  } {SQLITE_CONSTRAINT}
+  verify_ex_errcode fkey2-17.1.3b SQLITE_CONSTRAINT_FOREIGNKEY
+} else {
+  do_test fkey2-17.1.3 {
+    sqlite3_step $STMT
+  } {SQLITE_MISUSE}
+}
+do_test fkey2-17.1.4 {
+  sqlite3_finalize $STMT
+} {SQLITE_CONSTRAINT}
+verify_ex_errcode fkey2-17.1.4b SQLITE_CONSTRAINT_FOREIGNKEY
+do_test fkey2-17.1.5 {
+  execsql {
+    INSERT INTO one VALUES(2, 3, 4);
+    INSERT INTO one VALUES(3, 4, 5);
+    INSERT INTO two VALUES(1, 2, 3);
+    INSERT INTO two VALUES(2, 3, 4);
+    INSERT INTO two VALUES(3, 4, 5);
+  }
+} {1 1 1 1 1}
+do_test fkey2-17.1.6 {
+  catchsql {
+    BEGIN;
+      INSERT INTO one VALUES(0, 0, 0);
+      UPDATE two SET e=e+1, f=f+1;
+  }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-17.1.7 {
+  execsql { SELECT * FROM one }
+} {1 2 3 2 3 4 3 4 5 0 0 0}
+do_test fkey2-17.1.8 {
+  execsql { SELECT * FROM two }
+} {1 2 3 2 3 4 3 4 5}
+do_test fkey2-17.1.9 {
+  execsql COMMIT
+} {}
+do_test fkey2-17.1.10 {
+  execsql {
+    CREATE TABLE three(
+      g, h, i, 
+      FOREIGN KEY(h, i) REFERENCES one(b, c) DEFERRABLE INITIALLY DEFERRED
+    );
+  }
+} {}
+do_test fkey2-17.1.11 {
+  set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy]
+  sqlite3_step $STMT
+} {SQLITE_ROW}
+do_test fkey2-17.1.12 {
+  sqlite3_column_text $STMT 0
+} {1}
+do_test fkey2-17.1.13 {
+  sqlite3_step $STMT
+} {SQLITE_CONSTRAINT}
+verify_ex_errcode fkey2-17.1.13b SQLITE_CONSTRAINT_FOREIGNKEY
+do_test fkey2-17.1.14 {
+  sqlite3_finalize $STMT
+} {SQLITE_CONSTRAINT}
+verify_ex_errcode fkey2-17.1.14b SQLITE_CONSTRAINT_FOREIGNKEY
+
+drop_all_tables
+do_test fkey2-17.2.1 {
+  execsql {
+    CREATE TABLE high("a'b!" PRIMARY KEY, b);
+    CREATE TABLE low(
+      c, 
+      "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE
+    );
+  }
+} {}
+do_test fkey2-17.2.2 {
+  execsql {
+    INSERT INTO high VALUES('a', 'b');
+    INSERT INTO low VALUES('b', 'a');
+  }
+  db changes
+} {1}
+set nTotal [db total_changes]
+do_test fkey2-17.2.3 {
+  execsql { UPDATE high SET "a'b!" = 'c' }
+} {1}
+do_test fkey2-17.2.4 {
+  db changes
+} {1}
+do_test fkey2-17.2.5 {
+  expr [db total_changes] - $nTotal
+} {2}
+do_test fkey2-17.2.6 {
+  execsql { SELECT * FROM high ; SELECT * FROM low }
+} {c b b c}
+do_test fkey2-17.2.7 {
+  execsql { DELETE FROM high }
+} {1}
+do_test fkey2-17.2.8 {
+  db changes
+} {1}
+do_test fkey2-17.2.9 {
+  expr [db total_changes] - $nTotal
+} {4}
+do_test fkey2-17.2.10 {
+  execsql { SELECT * FROM high ; SELECT * FROM low }
+} {}
+execsql { PRAGMA count_changes = 0 }
+
+#-------------------------------------------------------------------------
+# Test that the authorization callback works.
+#
+
+ifcapable auth {
+  do_test fkey2-18.1 {
+    execsql {
+      CREATE TABLE long(a, b PRIMARY KEY, c);
+      CREATE TABLE short(d, e, f REFERENCES long);
+      CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED);
+    }
+  } {}
+
+  proc auth {args} {eval lappend ::authargs [lrange $args 0 4]; return SQLITE_OK}
+  db auth auth
+
+  # An insert on the parent table must read the child key of any deferred
+  # foreign key constraints. But not the child key of immediate constraints.
+  set authargs {}
+  do_test fkey2-18.2 {
+    execsql { INSERT INTO long VALUES(1, 2, 3) }
+    set authargs
+  } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}}
+
+  # An insert on the child table of an immediate constraint must read the
+  # parent key columns (to see if it is a violation or not).
+  set authargs {}
+  do_test fkey2-18.3 {
+    execsql { INSERT INTO short VALUES(1, 3, 2) }
+    set authargs
+  } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}}
+  
+  # As must an insert on the child table of a deferred constraint.
+  set authargs {}
+  do_test fkey2-18.4 {
+    execsql { INSERT INTO mid VALUES(1, 3, 2) }
+    set authargs
+  } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}}
+
+  do_test fkey2-18.5 {
+    execsql {
+      CREATE TABLE nought(a, b PRIMARY KEY, c);
+      CREATE TABLE cross(d, e, f,
+        FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE
+      );
+    }
+    execsql { INSERT INTO nought VALUES(2, 1, 2) }
+    execsql { INSERT INTO cross VALUES(0, 1, 0) }
+    set authargs [list]
+    execsql { UPDATE nought SET b = 5 }
+    set authargs
+  } {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 {}}
+
+  do_test fkey2-18.6 {
+    execsql {SELECT * FROM cross}
+  } {0 5 0}
+
+  do_test fkey2-18.7 {
+    execsql {
+      CREATE TABLE one(a INTEGER PRIMARY KEY, b);
+      CREATE TABLE two(b, c REFERENCES one);
+      INSERT INTO one VALUES(101, 102);
+    }
+    set authargs [list]
+    execsql { INSERT INTO two VALUES(100, 101); }
+    set authargs
+  } {SQLITE_INSERT two {} main {} SQLITE_READ one a main {}}
+
+  # Return SQLITE_IGNORE to requests to read from the parent table. This
+  # causes inserts of non-NULL keys into the child table to fail.
+  #
+  rename auth {}
+  proc auth {args} {
+    if {[lindex $args 1] == "long"} {return SQLITE_IGNORE}
+    return SQLITE_OK
+  }
+  do_test fkey2-18.8 {
+    catchsql { INSERT INTO short VALUES(1, 3, 2) }
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-18.9 {
+    execsql { INSERT INTO short VALUES(1, 3, NULL) }
+  } {}
+  do_test fkey2-18.10 {
+    execsql { SELECT * FROM short }
+  } {1 3 2 1 3 {}}
+  do_test fkey2-18.11 {
+    catchsql { UPDATE short SET f = 2 WHERE f IS NULL }
+  } {1 {FOREIGN KEY constraint failed}}
+
+  db auth {}
+  unset authargs
+}
+
+
+do_test fkey2-19.1 {
+  execsql {
+    CREATE TABLE main(id INTEGER PRIMARY KEY);
+    CREATE TABLE sub(id INT REFERENCES main(id));
+    INSERT INTO main VALUES(1);
+    INSERT INTO main VALUES(2);
+    INSERT INTO sub VALUES(2);
+  }
+} {}
+do_test fkey2-19.2 {
+  set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy]
+  sqlite3_bind_int $S 1 2
+  sqlite3_step $S
+} {SQLITE_CONSTRAINT}
+verify_ex_errcode fkey2-19.2b SQLITE_CONSTRAINT_FOREIGNKEY
+do_test fkey2-19.3 {
+  sqlite3_reset $S
+} {SQLITE_CONSTRAINT}
+verify_ex_errcode fkey2-19.3b SQLITE_CONSTRAINT_FOREIGNKEY
+do_test fkey2-19.4 {
+  sqlite3_bind_int $S 1 1
+  sqlite3_step $S
+} {SQLITE_DONE}
+do_test fkey2-19.4 {
+  sqlite3_finalize $S
+} {SQLITE_OK}
+
+drop_all_tables
+do_test fkey2-20.1 {
+  execsql {
+    CREATE TABLE pp(a PRIMARY KEY, b);
+    CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp);
+  }
+} {}
+
+foreach {tn insert} {
+  1 "INSERT"
+  2 "INSERT OR IGNORE"
+  3 "INSERT OR ABORT"
+  4 "INSERT OR ROLLBACK"
+  5 "INSERT OR REPLACE"
+  6 "INSERT OR FAIL"
+} {
+  do_test fkey2-20.2.$tn.1 {
+    catchsql "$insert INTO cc VALUES(1, 2)"
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-20.2.$tn.2 {
+    execsql { SELECT * FROM cc }
+  } {}
+  do_test fkey2-20.2.$tn.3 {
+    execsql {
+      BEGIN;
+        INSERT INTO pp VALUES(2, 'two');
+        INSERT INTO cc VALUES(1, 2);
+    }
+    catchsql "$insert INTO cc VALUES(3, 4)"
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-20.2.$tn.4 {
+    execsql { COMMIT ; SELECT * FROM cc }
+  } {1 2}
+  do_test fkey2-20.2.$tn.5 {
+    execsql { DELETE FROM cc ; DELETE FROM pp }
+  } {}
+}
+
+foreach {tn update} {
+  1 "UPDATE"
+  2 "UPDATE OR IGNORE"
+  3 "UPDATE OR ABORT"
+  4 "UPDATE OR ROLLBACK"
+  5 "UPDATE OR REPLACE"
+  6 "UPDATE OR FAIL"
+} {
+  do_test fkey2-20.3.$tn.1 {
+    execsql {
+      INSERT INTO pp VALUES(2, 'two');
+      INSERT INTO cc VALUES(1, 2);
+    }
+  } {}
+  do_test fkey2-20.3.$tn.2 {
+    catchsql "$update pp SET a = 1"
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-20.3.$tn.3 {
+    execsql { SELECT * FROM pp }
+  } {2 two}
+  do_test fkey2-20.3.$tn.4 {
+    catchsql "$update cc SET d = 1"
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-20.3.$tn.5 {
+    execsql { SELECT * FROM cc }
+  } {1 2}
+  do_test fkey2-20.3.$tn.6 {
+    execsql {
+      BEGIN;
+        INSERT INTO pp VALUES(3, 'three');
+    }
+    catchsql "$update pp SET a = 1 WHERE a = 2"
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-20.3.$tn.7 {
+    execsql { COMMIT ; SELECT * FROM pp }
+  } {2 two 3 three}
+  do_test fkey2-20.3.$tn.8 {
+    execsql {
+      BEGIN;
+        INSERT INTO cc VALUES(2, 2);
+    }
+    catchsql "$update cc SET d = 1 WHERE c = 1"
+  } {1 {FOREIGN KEY constraint failed}}
+  do_test fkey2-20.3.$tn.9 {
+    execsql { COMMIT ; SELECT * FROM cc }
+  } {1 2 2 2}
+  do_test fkey2-20.3.$tn.10 {
+    execsql { DELETE FROM cc ; DELETE FROM pp }
+  } {}
+}
+
+#-------------------------------------------------------------------------
+# The following block of tests, those prefixed with "fkey2-genfkey.", are 
+# the same tests that were used to test the ".genfkey" command provided 
+# by the shell tool. So these tests show that the built-in foreign key 
+# implementation is more or less compatible with the triggers generated 
+# by genfkey.
+#
+drop_all_tables
+do_test fkey2-genfkey.1.1 {
+  execsql {
+    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
+    CREATE TABLE t2(e REFERENCES t1, f);
+    CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
+  }
+} {}
+do_test fkey2-genfkey.1.2 {
+  catchsql { INSERT INTO t2 VALUES(1, 2) }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-genfkey.1.3 {
+  execsql {
+    INSERT INTO t1 VALUES(1, 2, 3);
+    INSERT INTO t2 VALUES(1, 2);
+  }
+} {}
+do_test fkey2-genfkey.1.4 {
+  execsql { INSERT INTO t2 VALUES(NULL, 3) }
+} {}
+do_test fkey2-genfkey.1.5 {
+  catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-genfkey.1.6 {
+  execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
+} {}
+do_test fkey2-genfkey.1.7 {
+  execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
+} {}
+do_test fkey2-genfkey.1.8 {
+  catchsql { UPDATE t1 SET a = 10 }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-genfkey.1.9 {
+  catchsql { UPDATE t1 SET a = NULL }
+} {1 {datatype mismatch}}
+do_test fkey2-genfkey.1.10 {
+  catchsql { DELETE FROM t1 }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-genfkey.1.11 {
+  execsql { UPDATE t2 SET e = NULL }
+} {}
+do_test fkey2-genfkey.1.12 {
+  execsql { 
+    UPDATE t1 SET a = 10;
+    DELETE FROM t1;
+    DELETE FROM t2;
+  }
+} {}
+do_test fkey2-genfkey.1.13 {
+  execsql {
+    INSERT INTO t3 VALUES(1, NULL, NULL);
+    INSERT INTO t3 VALUES(1, 2, NULL);
+    INSERT INTO t3 VALUES(1, NULL, 3);
+  }
+} {}
+do_test fkey2-genfkey.1.14 {
+  catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-genfkey.1.15 {
+  execsql { 
+    INSERT INTO t1 VALUES(1, 1, 4);
+    INSERT INTO t3 VALUES(3, 1, 4);
+  }
+} {}
+do_test fkey2-genfkey.1.16 {
+  catchsql { DELETE FROM t1 }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-genfkey.1.17 {
+  catchsql { UPDATE t1 SET b = 10}
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-genfkey.1.18 {
+  execsql { UPDATE t1 SET a = 10}
+} {}
+do_test fkey2-genfkey.1.19 {
+  catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
+} {1 {FOREIGN KEY constraint failed}}
+
+drop_all_tables
+do_test fkey2-genfkey.2.1 {
+  execsql {
+    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
+    CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
+    CREATE TABLE t3(g, h, i, 
+        FOREIGN KEY (h, i) 
+        REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
+    );
+  }
+} {}
+do_test fkey2-genfkey.2.2 {
+  execsql {
+    INSERT INTO t1 VALUES(1, 2, 3);
+    INSERT INTO t1 VALUES(4, 5, 6);
+    INSERT INTO t2 VALUES(1, 'one');
+    INSERT INTO t2 VALUES(4, 'four');
+  }
+} {}
+do_test fkey2-genfkey.2.3 {
+  execsql {
+    UPDATE t1 SET a = 2 WHERE a = 1;
+    SELECT * FROM t2;
+  }
+} {2 one 4 four}
+do_test fkey2-genfkey.2.4 {
+  execsql {
+    DELETE FROM t1 WHERE a = 4;
+    SELECT * FROM t2;
+  }
+} {2 one}
+
+do_test fkey2-genfkey.2.5 {
+  execsql {
+    INSERT INTO t3 VALUES('hello', 2, 3);
+    UPDATE t1 SET c = 2;
+    SELECT * FROM t3;
+  }
+} {hello 2 2}
+do_test fkey2-genfkey.2.6 {
+  execsql {
+    DELETE FROM t1;
+    SELECT * FROM t3;
+  }
+} {}
+
+drop_all_tables
+do_test fkey2-genfkey.3.1 {
+  execsql {
+    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));
+    CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
+    CREATE TABLE t3(g, h, i, 
+        FOREIGN KEY (h, i) 
+        REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
+    );
+  }
+} {}
+do_test fkey2-genfkey.3.2 {
+  execsql {
+    INSERT INTO t1 VALUES(1, 2, 3);
+    INSERT INTO t1 VALUES(4, 5, 6);
+    INSERT INTO t2 VALUES(1, 'one');
+    INSERT INTO t2 VALUES(4, 'four');
+  }
+} {}
+do_test fkey2-genfkey.3.3 {
+  execsql {
+    UPDATE t1 SET a = 2 WHERE a = 1;
+    SELECT * FROM t2;
+  }
+} {{} one 4 four}
+do_test fkey2-genfkey.3.4 {
+  execsql {
+    DELETE FROM t1 WHERE a = 4;
+    SELECT * FROM t2;
+  }
+} {{} one {} four}
+do_test fkey2-genfkey.3.5 {
+  execsql {
+    INSERT INTO t3 VALUES('hello', 2, 3);
+    UPDATE t1 SET c = 2;
+    SELECT * FROM t3;
+  }
+} {hello {} {}}
+do_test fkey2-genfkey.3.6 {
+  execsql {
+    UPDATE t3 SET h = 2, i = 2;
+    DELETE FROM t1;
+    SELECT * FROM t3;
+  }
+} {hello {} {}}
+
+#-------------------------------------------------------------------------
+# Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been
+# fixed.
+#
+do_test fkey2-dd08e5.1.1 {
+  execsql {
+    PRAGMA foreign_keys=ON;
+    CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b);
+    CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
+    INSERT INTO tdd08 VALUES(200,300);
+
+    CREATE TABLE tdd08_b(w,x,y, FOREIGN KEY(x,y) REFERENCES tdd08(a,b));
+    INSERT INTO tdd08_b VALUES(100,200,300);
+  }
+} {}
+do_test fkey2-dd08e5.1.2 {
+  catchsql {
+    DELETE FROM tdd08;
+  }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-dd08e5.1.3 {
+  execsql {
+    SELECT * FROM tdd08;
+  }
+} {200 300}
+do_test fkey2-dd08e5.1.4 {
+  catchsql {
+    INSERT INTO tdd08_b VALUES(400,500,300);
+  }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-dd08e5.1.5 {
+  catchsql {
+    UPDATE tdd08_b SET x=x+1;
+  }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-dd08e5.1.6 {
+  catchsql {
+    UPDATE tdd08 SET a=a+1;
+  }
+} {1 {FOREIGN KEY constraint failed}}
+
+#-------------------------------------------------------------------------
+# Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
+# fixed.
+#
+do_test fkey2-ce7c13.1.1 {
+  execsql {
+    CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
+    CREATE UNIQUE INDEX ice71 ON tce71(a,b);
+    INSERT INTO tce71 VALUES(100,200);
+    CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
+    INSERT INTO tce72 VALUES(300,100,200);
+    UPDATE tce71 set b = 200 where a = 100;
+    SELECT * FROM tce71, tce72;
+  }
+} {100 200 300 100 200}
+do_test fkey2-ce7c13.1.2 {
+  catchsql {
+    UPDATE tce71 set b = 201 where a = 100;
+  }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-ce7c13.1.3 {
+  catchsql {
+    UPDATE tce71 set a = 101 where a = 100;
+  }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-ce7c13.1.4 {
+  execsql {
+    CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
+    INSERT INTO tce73 VALUES(100,200);
+    CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
+    INSERT INTO tce74 VALUES(300,100,200);
+    UPDATE tce73 set b = 200 where a = 100;
+    SELECT * FROM tce73, tce74;
+  }
+} {100 200 300 100 200}
+do_test fkey2-ce7c13.1.5 {
+  catchsql {
+    UPDATE tce73 set b = 201 where a = 100;
+  }
+} {1 {FOREIGN KEY constraint failed}}
+do_test fkey2-ce7c13.1.6 {
+  catchsql {
+    UPDATE tce73 set a = 101 where a = 100;
+  }
+} {1 {FOREIGN KEY constraint failed}}
+
+# 2015-04-16:  Foreign key errors propagate back up to the parser.
+#
+do_test fkey2-20150416-100 {
+  db close
+  sqlite3 db :memory:
+  catchsql {
+    PRAGMA foreign_keys=1;
+    CREATE TABLE t1(x PRIMARY KEY);
+    CREATE TABLE t(y REFERENCES t0(x)ON DELETE SET DEFAULT);
+    CREATE TABLE t0(y REFERENCES t1 ON DELETE SET NULL);
+    REPLACE INTO t1 SELECT(0);CREATE TABLE t2(x);CREATE TABLE t3;
+  }
+} {1 {foreign key mismatch - "t" referencing "t0"}}
+
+finish_test