SQLite

    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
    ** column of the parent table (table pTab).  */
    int iMustBeInt;               /* Address of MustBeInt instruction */
    int regTemp = sqlite3GetTempReg(pParse);

    /* Invoke MustBeInt to coerce the child key value to an integer (i.e. 
    ** apply the affinity of the parent key). If this fails, then there
    ** is no matching parent key. Before using MustBeInt, make a copy of
    ** the value. Otherwise, the value inserted into the child key column
    ** will have INTEGER affinity applied to it, which may not be correct.  */
    sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[0]+1+regData, regTemp);
    iMustBeInt = sqlite3VdbeAddOp2(v, OP_MustBeInt, regTemp, 0);

    /* If the parent table is the same as the child table, and we are about
    ** to increment the constraint-counter (i.e. this is an INSERT operation), 
    ** then check if the row being inserted matches itself. If so, do not
    ** increment the constraint-counter.  */
    if( pTab==pFKey->pFrom && nIncr==1 ){
      sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk, regTemp);
    }

    sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp);
    sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
    sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
    sqlite3VdbeJumpHere(v, iMustBeInt);
    sqlite3ReleaseTempReg(pParse, regTemp);



  }else{
    int nCol = pFKey->nCol;
    int regTemp = sqlite3GetTempRange(pParse, nCol);
    int regRec = sqlite3GetTempReg(pParse);
    KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);

    sqlite3VdbeAddOp3(v, OP_OpenRead, iCur, pIdx->tnum, iDb);
    sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
    for(i=0; i<nCol; i++){
      sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[i]+1+regData, regTemp+i);
    }

    /* If the parent table is the same as the child table, and we are about
    ** to increment the constraint-counter (i.e. this is an INSERT operation), 
    ** then check if the row being inserted matches itself. If so, do not
    ** increment the constraint-counter.  */
    if( pTab==pFKey->pFrom && nIncr==1 ){
      int iJump = sqlite3VdbeCurrentAddr(v) + nCol + 1;
      for(i=0; i<nCol; i++){
        int iChild = aiCol[i]+1+regData;
        int iParent = pIdx->aiColumn[i]+1+regData;
        sqlite3VdbeAddOp3(v, OP_Ne, iChild, iJump, iParent);
      }
      sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
    }

    sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec);
    sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0);
    sqlite3VdbeAddOp3(v, OP_Found, iCur, iOk, regRec);

    sqlite3ReleaseTempReg(pParse, regRec);
    sqlite3ReleaseTempRange(pParse, regTemp, nCol);
  }

  if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
    /* Special case: If this is an INSERT statement that will insert exactly
    ** one row into the table, raise a constraint immediately instead of

**
** These operations are identified in the comment at the top of this file 
** (fkey.c) as "I.2" and "D.2".
*/
static void fkScanChildren(
  Parse *pParse,                  /* Parse context */
  SrcList *pSrc,                  /* SrcList containing the table to scan */
  Table *pTab,
  Index *pIdx,                    /* Foreign key index */
  FKey *pFKey,                    /* Foreign key relationship */
  int *aiCol,                     /* Map from pIdx cols to child table cols */
  int regData,                    /* Referenced table data starts here */
  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);

  assert( !pIdx || pIdx->pTable==pTab );

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

  /* Create an Expr object representing an SQL expression like:
  **

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

  /* If the child table is the same as the parent table, and this scan
  ** is taking place as part of a DELETE operation (operation D.2), omit the
  ** row being deleted from the scan by adding ($rowid != rowid) to the WHERE 
  ** clause, where $rowid is the rowid of the row being deleted.  */
  if( pTab==pFKey->pFrom && nIncr>0 ){
    Expr *pEq;                    /* Expression (pLeft = pRight) */
    Expr *pLeft;                  /* Value from parent table row */
    Expr *pRight;                 /* Column ref to child table */
    pLeft = sqlite3Expr(db, TK_REGISTER, 0);
    pRight = sqlite3Expr(db, TK_COLUMN, 0);
    if( pLeft && pRight ){
      pLeft->iTable = regData;
      pLeft->affinity = SQLITE_AFF_INTEGER;
      pRight->iTable = pSrc->a[0].iCursor;
      pRight->iColumn = -1;
    }
    pEq = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0);
    pWhere = sqlite3ExprAnd(db, pWhere, pEq);
  }

  /* Resolve the references in the WHERE clause. */
  memset(&sNameContext, 0, sizeof(NameContext));
  sNameContext.pSrcList = pSrc;
  sNameContext.pParse = pParse;
  sqlite3ResolveExprNames(&sNameContext, pWhere);

** the lookaside buffer belonging to database handle dbMem.
*/
static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){
  if( p ){
    TriggerStep *pStep = p->step_list;
    sqlite3ExprDelete(dbMem, pStep->pWhere);
    sqlite3ExprListDelete(dbMem, pStep->pExprList);
    sqlite3SelectDelete(dbMem, pStep->pSelect);
    sqlite3ExprDelete(dbMem, p->pWhen);
    sqlite3DbFree(dbMem, p);
  }
}

/*
** This function is called when inserting, deleting or updating a row of
** table pTab to generate VDBE code to perform foreign key constraint 
** processing for the operation.
**
** For a DELETE operation, parameter regOld is passed the index of the
** first register in an array of (pTab->nCol+1) registers containing the
** rowid of the row being deleted, followed by each of the column values
** of the row being deleted, from left to right. Parameter regNew is passed
** zero in this case.
**






** For an INSERT operation, regOld is passed zero and regNew is passed the
** first register of an array of (pTab->nCol+1) registers containing the new
** row data.
**
** For an UPDATE operation, this function is called twice. Once before
** the original record is deleted from the table using the calling convention
** described for DELETE. Then again after the original record is deleted
** but before the new record is inserted using the INSERT convention. In
** both cases parameter pChanges is passed the list of columns being 
** updated by the statement.
*/
void sqlite3FkCheck(
  Parse *pParse,                  /* Parse context */
  Table *pTab,                    /* Row is being deleted from this table */ 
  ExprList *pChanges,             /* Changed columns if this is an UPDATE */
  int regOld,                     /* Previous row data is stored here */
  int regNew                      /* New row data is stored here */
){
  sqlite3 *db = pParse->db;       /* Database handle */
  Vdbe *v;                        /* VM to write code to */
  FKey *pFKey;                    /* Used to iterate through FKs */
  int iDb;                        /* Index of database containing pTab */
  const char *zDb;                /* Name of database containing pTab */

  assert( ( pChanges &&  (regOld==0)!=(regNew==0))    /* UPDATE operation */
       || (!pChanges && !regOld &&  regNew)           /* INSERT operation */
       || (!pChanges &&  regOld && !regNew)           /* DELETE operation */
  );

  /* If foreign-keys are disabled, this function is a no-op. */
  if( (db->flags&SQLITE_ForeignKeys)==0 ) return;

    ** is required for the sqlite3WhereXXX() interface.  */
    pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
    if( pSrc ){
      pSrc->a->pTab = pFKey->pFrom;
      pSrc->a->pTab->nRef++;
      pSrc->a->iCursor = pParse->nTab++;
  


















      if( regNew!=0 ){
        fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1);
      }
      if( regOld!=0 ){
        /* If there is a RESTRICT action configured for the current operation
        ** on the parent table of this FK, then throw an exception 
        ** immediately if the FK constraint is violated, even if this is a
        ** deferred trigger. That's what RESTRICT means. To defer checking
        ** the constraint, the FK should specify NO ACTION (represented
        ** using OE_None). NO ACTION is the default.  */
        fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1);


      }
  
      if( pChanges ){
        sqlite3VdbeJumpHere(v, iGoto);
      }
      sqlite3SrcListDelete(db, pSrc);
    }

  int action;                     /* One of OE_None, OE_Cascade etc. */
  Trigger *pTrigger;              /* Trigger definition to return */
  int iAction = (pChanges!=0);    /* 1 for UPDATE, 0 for DELETE */

  action = pFKey->aAction[iAction];
  pTrigger = pFKey->apTrigger[iAction];




  if( action!=OE_None && !pTrigger ){
    u8 enableLookaside;           /* Copy of db->lookaside.bEnabled */
    char const *zFrom;            /* Name of child table */
    int nFrom;                    /* Length in bytes of zFrom */
    Index *pIdx = 0;              /* Parent key index for this FK */
    int *aiCol = 0;               /* child table cols -> parent key cols */
    TriggerStep *pStep;           /* First (only) step of trigger program */
    Expr *pWhere = 0;             /* WHERE clause of trigger step */
    ExprList *pList = 0;          /* Changes list if ON UPDATE CASCADE */
    Select *pSelect = 0;          /* If RESTRICT, "SELECT RAISE(...)" */
    int i;                        /* Iterator variable */
    Expr *pWhen = 0;              /* WHEN clause for the trigger */

    if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
    assert( aiCol || pFKey->nCol==1 );

    for(i=0; i<pFKey->nCol; i++){

              sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
              sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol),
              0),
            0);
        pWhen = sqlite3ExprAnd(db, pWhen, pEq);
      }
  
      if( action!=OE_Restrict && (action!=OE_Cascade || pChanges) ){
        Expr *pNew;
        if( action==OE_Cascade ){
          pNew = sqlite3PExpr(pParse, TK_DOT, 
            sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
            sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
          , 0);
        }else if( action==OE_SetDflt ){

          pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
        }
        pList = sqlite3ExprListAppend(pParse, pList, pNew);
        sqlite3ExprListSetName(pParse, pList, &tFromCol, 0);
      }
    }
    sqlite3DbFree(db, aiCol);

    zFrom = pFKey->pFrom->zName;
    nFrom = sqlite3Strlen30(zFrom);

    if( action==OE_Restrict ){
      Token tFrom;
      Expr *pRaise; 

      tFrom.z = zFrom;
      tFrom.n = nFrom;
      pRaise = sqlite3Expr(db, TK_RAISE, "foreign key constraint failed");
      if( pRaise ){
        pRaise->affinity = OE_Abort;
      }
      pSelect = sqlite3SelectNew(pParse, 
          sqlite3ExprListAppend(pParse, 0, pRaise),
          sqlite3SrcListAppend(db, 0, &tFrom, 0),
          pWhere,
          0, 0, 0, 0, 0, 0
      );
      pWhere = 0;
    }

    /* In the current implementation, pTab->dbMem==0 for all tables except
    ** for temporary tables used to describe subqueries.  And temporary
    ** tables do not have foreign key constraints.  Hence, pTab->dbMem
    ** should always be 0 there.
    */
    enableLookaside = db->lookaside.bEnabled;
    db->lookaside.bEnabled = 0;



    pTrigger = (Trigger *)sqlite3DbMallocZero(db, 
        sizeof(Trigger) +         /* struct Trigger */
        sizeof(TriggerStep) +     /* Single step in trigger program */
        nFrom + 1                 /* Space for pStep->target.z */
    );
    if( pTrigger ){
      pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
      pStep->target.z = (char *)&pStep[1];
      pStep->target.n = nFrom;
      memcpy((char *)pStep->target.z, zFrom, nFrom);
  
      pStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
      pStep->pExprList = sqlite3ExprListDup(db, pList, EXPRDUP_REDUCE);
      pStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
      if( pWhen ){
        pWhen = sqlite3PExpr(pParse, TK_NOT, pWhen, 0, 0);
        pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE);
      }
    }

    /* Re-enable the lookaside buffer, if it was disabled earlier. */
    db->lookaside.bEnabled = enableLookaside;

    sqlite3ExprDelete(db, pWhere);
    sqlite3ExprDelete(db, pWhen);
    sqlite3ExprListDelete(db, pList);
    sqlite3SelectDelete(db, pSelect);
    if( db->mallocFailed==1 ){
      fkTriggerDelete(db, pTrigger);
      return 0;
    }

    switch( action ){
      case OE_Restrict:
        pStep->op = TK_SELECT; 
        break;
      case OE_Cascade: 
        if( !pChanges ){ 
          pStep->op = TK_DELETE; 
          break; 
        }
      default:
        pStep->op = TK_UPDATE;
    }
    pStep->pTrig = pTrigger;
    pTrigger->pSchema = pTab->pSchema;
    pTrigger->pTabSchema = pTab->pSchema;
    pFKey->apTrigger[iAction] = pTrigger;
    pTrigger->op = (pChanges ? TK_UPDATE : TK_DELETE);
  }

  if( !isView ){

    /* Do constraint checks. */
    sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid,
        aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0);

    /* Do FK constraint checks. */
    sqlite3FkCheck(pParse, pTab, pChanges, regOldRowid, 0);

    /* Delete the index entries associated with the current record.  */
    j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
    sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
  
    /* If changing the record number, delete the old record.  */
    if( hasFK || chngRowid ){
      sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0);
    }
    sqlite3VdbeJumpHere(v, j1);

    sqlite3FkCheck(pParse, pTab, pChanges, 0, regNewRowid);
  
    /* Insert the new index entries and the new record. */
    sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0);

    /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
    ** handle rows (possibly in other tables) that refer via a foreign key
    ** to the row just updated. */ 

#
# 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-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'}]
  foreach t [execsql {
    SELECT name FROM sqlite_master 
    WHERE type = 'table' AND name NOT like 'sqlite_%'
  }] {
    execsql "DROP TABLE $t"
  }
}

execsql { PRAGMA foreign_keys = on }

if 0 {
execsql {
  CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1);
  INSERT INTO t1 VALUES('aaa', 'aaa');
}
puts XXX
explain { UPDATE t1 SET a = 'bbb' }
execsql { PRAGMA vdbe_trace = 1 }
execsql { UPDATE t1 SET a = 'bbb' }
exit
}

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

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


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