SQLite

  int nIncr             /* Increment constraint counter by this */
){
  int i;                                    /* Iterator variable */
  Vdbe *v = sqlite3GetVdbe(pParse);         /* Vdbe to add code to */
  int iCur = pParse->nTab - 1;              /* Cursor number to use */
  int iOk = sqlite3VdbeMakeLabel(v);        /* jump here if parent key found */

  /* If nIncr is less than zero, then check at runtime if there are any
  ** outstanding constraints to resolve. If there are not, there is no need
  ** to check if deleting this row resolves any outstanding violations.
  **
  ** Check if any of the key columns in the child table row are NULL. If 
  ** any are, then the constraint is considered satisfied. No need to 
  ** search for a matching row in the parent table.  */
  if( nIncr<0 ){
    sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, iOk);
  }
  for(i=0; i<pFKey->nCol; i++){
    int iReg = aiCol[i] + regData + 1;
    sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk);
  }

  if( pIdx==0 ){
    /* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY

    sqlite3HaltConstraint(
        pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
    );
  }else{
    if( nIncr>0 && pFKey->isDeferred==0 ){
      sqlite3ParseToplevel(pParse)->mayAbort = 1;
    }
    sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
  }

  sqlite3VdbeResolveLabel(v, iOk);
}

/*
** This function is called to generate code executed when a row is deleted

  int nIncr                       /* Amount to increment deferred counter by */
){
  sqlite3 *db = pParse->db;       /* Database handle */
  int i;                          /* Iterator variable */
  Expr *pWhere = 0;               /* WHERE clause to scan with */
  NameContext sNameContext;       /* Context used to resolve WHERE clause */
  WhereInfo *pWInfo;              /* Context used by sqlite3WhereXXX() */
  int iFkIfZero = 0;              /* Address of OP_FkIfZero */
  Vdbe *v = sqlite3GetVdbe(pParse);

  if( nIncr<0 ){
    iFkIfZero = sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0);
  }

  /* Create an Expr object representing an SQL expression like:
  **
  **   <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
  **
  ** The collation sequence used for the comparison should be that of
  ** the parent key columns. The affinity of the parent key column should

    sqlite3HaltConstraint(
      pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
    );
  }else{
    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 ){
    sqlite3VdbeJumpHere(v, iFkIfZero);
  }
}

/*
** This function returns a pointer to the head of a linked list of FK
** constraints for which table pTab is the parent table. For example,
** given the following schema:
**

/*
** Register commands with the TCL interpreter.
*/
int Sqlitetest1_Init(Tcl_Interp *interp){
  extern int sqlite3_search_count;
  extern int sqlite3_found_count;
  extern int sqlite3_interrupt_count;
  extern int sqlite3_open_file_count;
  extern int sqlite3_sort_count;
  extern int sqlite3_current_time;
#if SQLITE_OS_UNIX && defined(__APPLE__)
  extern int sqlite3_hostid_num;
#endif

  }
  for(i=0; i<sizeof(aObjCmd)/sizeof(aObjCmd[0]); i++){
    Tcl_CreateObjCommand(interp, aObjCmd[i].zName, 
        aObjCmd[i].xProc, aObjCmd[i].clientData, 0);
  }
  Tcl_LinkVar(interp, "sqlite_search_count", 
      (char*)&sqlite3_search_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_found_count", 
      (char*)&sqlite3_found_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_sort_count", 
      (char*)&sqlite3_sort_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite3_max_blobsize", 
      (char*)&sqlite3_max_blobsize, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_like_count", 
      (char*)&sqlite3_like_count, TCL_LINK_INT);
  Tcl_LinkVar(interp, "sqlite_interrupt_count", 

static void updateMaxBlobsize(Mem *p){
  if( (p->flags & (MEM_Str|MEM_Blob))!=0 && p->n>sqlite3_max_blobsize ){
    sqlite3_max_blobsize = p->n;
  }
}
#endif

/*
** The next global variable is incremented each type the OP_Found opcode
** is executed. This is used to test whether or not the foreign key
** operation implemented using OP_FkIsZero is working. This variable
** has no function other than to help verify the correct operation of the
** library.
*/
#ifdef SQLITE_TEST
int sqlite3_found_count = 0;
#endif

/*
** Test a register to see if it exceeds the current maximum blob size.
** If it does, record the new maximum blob size.
*/
#if defined(SQLITE_TEST) && !defined(SQLITE_OMIT_BUILTIN_TEST)
# define UPDATE_MAX_BLOBSIZE(P)  updateMaxBlobsize(P)
#else

case OP_NotFound:       /* jump, in3 */
case OP_Found: {        /* jump, in3 */
  int alreadyExists;
  VdbeCursor *pC;
  int res;
  UnpackedRecord *pIdxKey;
  char aTempRec[ROUND8(sizeof(UnpackedRecord)) + sizeof(Mem)*3 + 7];

#ifdef SQLITE_TEST
  sqlite3_found_count++;
#endif

  alreadyExists = 0;
  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
  pC = p->apCsr[pOp->p1];
  assert( pC!=0 );
  if( ALWAYS(pC->pCursor!=0) ){

}

#endif /* #ifndef SQLITE_OMIT_TRIGGER */

#ifndef SQLITE_OMIT_FOREIGN_KEY
/* Opcode: FkCounter P1 P2 * * *
**
** Increment a "constraint counter" by P2 (P2 may be negative or positive).
** If P1 is non-zero, the database constraint counter is incremented 
** (deferred foreign key constraints). Otherwise, if P1 is zero, the 
** statement counter is incremented (immediate foreign key constraints).
*/
case OP_FkCounter: {
  if( pOp->p1 ){
    db->nDeferredCons += pOp->p2;
  }else{
    p->nFkConstraint += pOp->p2;
  }
  break;
}

/* Opcode: FkIfZero P1 P2 * * *
**
** This opcode tests if a foreign key constraint-counter is currently zero.
** If so, jump to instruction P2. Otherwise, fall through to the next 
** instruction.
**
** If P1 is non-zero, then the jump is taken if the database constraint-counter
** is zero (the one that counts deferred constraint violations). If P1 is
** zero, the jump is taken if the statement constraint-counter is zero
** (immediate foreign key constraint violations).
*/
case OP_FkIfZero: {         /* jump */
  if( pOp->p1 ){
    if( db->nDeferredCons==0 ) pc = pOp->p2-1;
  }else{
    if( p->nFkConstraint==0 ) pc = pOp->p2-1;
  }
  break;
}
#endif /* #ifndef SQLITE_OMIT_FOREIGN_KEY */

#ifndef SQLITE_OMIT_AUTOINCREMENT
/* Opcode: MemMax P1 P2 * * *

  memset(&sIter, 0, sizeof(sIter));
  sIter.v = v;

  while( (pOp = opIterNext(&sIter))!=0 ){
    int opcode = pOp->opcode;
    if( opcode==OP_Destroy || opcode==OP_VUpdate || opcode==OP_VRename 
#ifndef SQLITE_OMIT_FOREIGN_KEY
     || (opcode==OP_FkCounter && pOp->p1==0 && pOp->p2==1) 
#endif
     || ((opcode==OP_Halt || opcode==OP_HaltIfNull) 
      && (pOp->p1==SQLITE_CONSTRAINT && pOp->p2==OE_Abort))
    ){
      hasAbort = 1;
      break;
    }

# 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-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'}]

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

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

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


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