SQLite

    const char *zCol;             /* Name of column in child table */

    pLeft = sqlite3Expr(db, TK_REGISTER, 0);
    if( pLeft ){
      pLeft->iTable = (pIdx ? (regData+pIdx->aiColumn[i]+1) : regData);
    }
    iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
    assert( iCol>=0 );


    zCol = pFKey->pFrom->aCol[iCol].zName;

    pRight = sqlite3Expr(db, TK_ID, zCol);
    pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0);
    pWhere = sqlite3ExprAnd(db, pWhere, pEq);
  }

  /* Resolve the references in the WHERE clause. */
  memset(&sNameContext, 0, sizeof(NameContext));

      Token tNew = { "new", 3 };  /* Literal "new" token */
      Token tFromCol;             /* Name of column in child table */
      Token tToCol;               /* Name of column in parent table */
      int iFromCol;               /* Idx of column in child table */
      Expr *pEq;                  /* tFromCol = OLD.tToCol */

      iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
      assert( iFromCol>=0 );
      tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
      tFromCol.z = pFKey->pFrom->aCol[iFromCol].zName;

      tToCol.n = sqlite3Strlen30(tToCol.z);
      tFromCol.n = sqlite3Strlen30(tFromCol.z);

      /* Create the expression "zFromCol = OLD.zToCol" */
      pEq = sqlite3PExpr(pParse, TK_EQ,
          sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol),

# 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-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#


proc drop_all_tables {{db db}} {
  set tbls [execsql {SELECT name FROM sqlite_master WHERE type = 'table'}]

} {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}}

#-------------------------------------------------------------------------
# The following tests, fkey2-10.*, test "foreign key mismatch" and 
# other errors.
#

set tn 1
foreach zSql [list {
  CREATE TABLE p(a PRIMARY KEY, b);
  CREATE TABLE c(x REFERENCES p(c));
}] {
  drop_all_tables

  do_test fkey2-10.1.$tn {
    execsql $zSql
    catchsql { INSERT INTO c DEFAULT VALUES }
  } {1 {foreign key mismatch}}
}

# "rowid" cannot be used as part of a child 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 {}}


#-------------------------------------------------------------------------
# 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);
    CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);

    INSERT INTO t1 VALUES(10, 100);
    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;
  }
} {}



#-------------------------------------------------------------------------
# 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.

  finish_test
  return
}
source $testdir/malloc_common.tcl

do_malloc_test fkey_malloc-1 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
  CREATE TABLE t2(x REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE);
} -sqlbody {
  INSERT INTO t1 VALUES('aaa', 1);
  INSERT INTO t2 VALUES('aaa');
  UPDATE t1 SET a = 'bbb';
  DELETE FROM t1;
}

    INSERT INTO t1 VALUES('a', 'b');
    UPDATE t1 SET a = 'c';
    DELETE FROM t2;
    INSERT INTO t2 VALUES('d', 'b');
    UPDATE t2 SET x = 'c';
  COMMIT;
}

do_malloc_test fkey_malloc-3 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE t1(x INTEGER PRIMARY KEY);
  CREATE TABLE t2(y REFERENCES t1(rowid) ON UPDATE CASCADE);
  CREATE TABLE t3(y DEFAULT 14 REFERENCES t1(x) ON UPDATE SET DEFAULT);
  CREATE TABLE t4(y REFERENCES t1 ON UPDATE SET NULL);
  INSERT INTO t1 VALUES(13);
  INSERT INTO t2 VALUES(13);
  INSERT INTO t3 VALUES(13);
  INSERT INTO t4 VALUES(13);
} -sqlbody {
  UPDATE t1 SET x = 14;
}

proc catch_fk_error {zSql} {
  set rc [catch {db eval $zSql} msg]
  if {$rc==0} {
    return $msg
  }
  if {[string match {*foreign key*} $msg]} {
    return ""
  }
  if {$msg eq "out of memory"} {
    error 1
  }
  error $msg
}

do_malloc_test fkey_malloc-4 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE t1(x INTEGER PRIMARY KEY, y UNIQUE);
  CREATE TABLE t2(z REFERENCES t1(x), a REFERENCES t1(y));
  CREATE TABLE t3(x);
  CREATE TABLE t4(z REFERENCES t3);
  CREATE TABLE t5(x, y);
  CREATE TABLE t6(z REFERENCES t5(x));
  CREATE INDEX i51 ON t5(x);
  CREATE INDEX i52 ON t5(y, x);
  INSERT INTO t1 VALUES(1, 2);
} -tclbody {
  catch_fk_error {INSERT INTO t2 VALUES(1, 3)}
  catch_fk_error {INSERT INTO t4 VALUES(2)}
  catch_fk_error {INSERT INTO t6 VALUES(2)}
}

do_malloc_test fkey_malloc-5 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE t1(x, y, PRIMARY KEY(x, y));
  CREATE TABLE t2(a, b, FOREIGN KEY(a, b) REFERENCES t1 ON UPDATE CASCADE);
  INSERT INTO t1 VALUES(1, 2);
  INSERT INTO t2 VALUES(1, 2);
} -sqlbody {
  UPDATE t1 SET x = 5;
}

finish_test