SQLite

  int *aiCol = 0;                     /* Value to return via *paiCol */
  int nCol = pFKey->nCol;             /* Number of columns in parent key */
  char *zKey = pFKey->aCol[0].zCol;   /* Name of left-most parent key column */

  /* The caller is responsible for zeroing output parameters. */
  assert( ppIdx && *ppIdx==0 );
  assert( !paiCol || *paiCol==0 );
  assert( pParse );

  /* If this is a non-composite (single column) foreign key, check if it 
  ** maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx 
  ** and *paiCol set to zero and return early. 
  **
  ** Otherwise, for a composite foreign key (more than one column), allocate
  ** space for the aiCol array (returned via output parameter *paiCol).

          if( j==nCol ) break;
        }
        if( i==nCol ) break;      /* pIdx is usable */
      }
    }
  }

  if( !pIdx ){
    if( !pParse->disableTriggers ){
      sqlite3ErrorMsg(pParse, "foreign key mismatch");
    }
    sqlite3DbFree(pParse->db, aiCol);
    return 1;
  }

  sqlite3ResolveExprNames(&sNameContext, pWhere);

  /* Create VDBE to loop through the entries in pSrc that match the WHERE
  ** clause. If the constraint is not deferred, throw an exception for
  ** each row found. Otherwise, for deferred constraints, increment the
  ** deferred constraint counter by nIncr for each row selected.  */
  pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0);







  if( nIncr>0 && pFKey->isDeferred==0 ){
    sqlite3ParseToplevel(pParse)->mayAbort = 1;
  }
  sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);

  if( pWInfo ){
    sqlite3WhereEnd(pWInfo);
  }

  /* Clean up the WHERE clause constructed above. */
  sqlite3ExprDelete(db, pWhere);
  if( iFkIfZero ){

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

  execsql { PRAGMA foreign_keys = OFF }
  foreach {t type} [execsql {
    SELECT name, type FROM sqlite_master 
    WHERE type IN('table', 'view') AND name NOT like 'sqlite_%'
  }] {
    execsql "DROP $type $t"
  }
  execsql { PRAGMA foreign_keys = ON }
}

execsql { PRAGMA foreign_keys = on }

set FkeySimpleSchema {

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 cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
  }
  catchsql { INSERT INTO cc VALUES(1, 2) }
} {1 {foreign key mismatch}}
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} {

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

do_malloc_test fkey_malloc-6 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE t1(
    x PRIMARY KEY, 
    y REFERENCES t1 ON DELETE RESTRICT ON UPDATE SET DEFAULT
  );
  INSERT INTO t1 VALUES('abc', 'abc');
  INSERT INTO t1 VALUES('def', 'def');
} -sqlbody {
  INSERT INTO t1 VALUES('ghi', 'ghi');
  DELETE FROM t1 WHERE rowid>1;
  UPDATE t1 SET x='jkl', y='jkl';
}

do_malloc_test fkey_malloc-7 -sqlprep {
  PRAGMA foreign_keys = 1;
  CREATE TABLE x(a, b, PRIMARY KEY(a, b));
  CREATE TABLE y(c, d,
    FOREIGN KEY(d, c) REFERENCES x DEFERRABLE INITIALLY DEFERRED
  );
  CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);
} -sqlbody {
  DROP TABLE y;
  DROP TABLE x;
}

finish_test