Check in implementation of foreign key constraints.

FossilOrigin-Name: d5d399811876391642937edeb9e8434dd9e356f5

17 files changed, 1059 insertions, 100 deletions

diff --git a/src/build.c b/src/build.c
index c23d92fb9..0fbe07e19 100644
--- a/src/build.c
+++ b/src/build.c
@@ -504,7 +504,6 @@ static void sqliteResetColumnNames(Table *pTable){
 */
 void sqlite3DeleteTable(Table *pTable){
   Index *pIndex, *pNext;
-  FKey *pFKey, *pNextFKey;
   sqlite3 *db;
 
   if( pTable==0 ) return;
@@ -526,13 +525,8 @@ void sqlite3DeleteTable(Table *pTable){
     sqlite3DeleteIndex(pIndex);
   }
 
-#ifndef SQLITE_OMIT_FOREIGN_KEY
-  /* Delete all foreign keys associated with this table. */
-  for(pFKey=pTable->pFKey; pFKey; pFKey=pNextFKey){
-    pNextFKey = pFKey->pNextFrom;
-    sqlite3DbFree(db, pFKey);
-  }
-#endif
+  /* Delete any foreign keys attached to this table. */
+  sqlite3FkDelete(pTable);
 
   /* Delete the Table structure itself.
   */
@@ -1174,7 +1168,11 @@ void sqlite3AddPrimaryKey(
        "INTEGER PRIMARY KEY");
 #endif
   }else{
-    sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, sortOrder, 0);
+    Index *p;
+    p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, sortOrder, 0);
+    if( p ){
+      p->autoIndex = 2;
+    }
     pList = 0;
   }
 
@@ -2153,6 +2151,7 @@ void sqlite3CreateForeignKey(
   sqlite3 *db = pParse->db;
 #ifndef SQLITE_OMIT_FOREIGN_KEY
   FKey *pFKey = 0;
+  FKey *pNextTo;
   Table *p = pParse->pNewTable;
   int nByte;
   int i;
@@ -2231,6 +2230,16 @@ void sqlite3CreateForeignKey(
   pFKey->updateConf = (u8)((flags >> 8 ) & 0xff);
   pFKey->insertConf = (u8)((flags >> 16 ) & 0xff);
 
+  pNextTo = (FKey *)sqlite3HashInsert(&p->pSchema->fkeyHash, 
+      pFKey->zTo, sqlite3Strlen30(pFKey->zTo), (void *)pFKey
+  );
+  if( pNextTo==pFKey ) goto fk_end;
+  if( pNextTo ){
+    assert( pNextTo->pPrevTo==0 );
+    pFKey->pNextTo = pNextTo;
+    pNextTo->pPrevTo = pFKey;
+  }
+
   /* Link the foreign key to the table as the last step.
   */
   p->pFKey = pFKey;
@@ -2350,8 +2359,12 @@ static void sqlite3RefillIndex(Parse *pParse, Index *pIndex, int memRootPage){
 ** pList is a list of columns to be indexed.  pList will be NULL if this
 ** is a primary key or unique-constraint on the most recent column added
 ** to the table currently under construction.  
+**
+** If the index is created successfully, return a pointer to the new Index
+** structure. This is used by sqlite3AddPrimaryKey() to mark the index
+** as the tables primary key (Index.autoIndex==2).
 */
-void sqlite3CreateIndex(
+Index *sqlite3CreateIndex(
   Parse *pParse,     /* All information about this parse */
   Token *pName1,     /* First part of index name. May be NULL */
   Token *pName2,     /* Second part of index name. May be NULL */
@@ -2363,6 +2376,7 @@ void sqlite3CreateIndex(
   int sortOrder,     /* Sort order of primary key when pList==NULL */
   int ifNotExist     /* Omit error if index already exists */
 ){
+  Index *pRet = 0;     /* Pointer to return */
   Table *pTab = 0;     /* Table to be indexed */
   Index *pIndex = 0;   /* The index to be created */
   char *zName = 0;     /* Name of the index */
@@ -2798,6 +2812,7 @@ void sqlite3CreateIndex(
       pIndex->pNext = pOther->pNext;
       pOther->pNext = pIndex;
     }
+    pRet = pIndex;
     pIndex = 0;
   }
 
@@ -2810,7 +2825,7 @@ exit_create_index:
   sqlite3ExprListDelete(db, pList);
   sqlite3SrcListDelete(db, pTblName);
   sqlite3DbFree(db, zName);
-  return;
+  return pRet;
 }
 
 /*
diff --git a/src/callback.c b/src/callback.c
index 492e4206f..80dc693d2 100644
--- a/src/callback.c
+++ b/src/callback.c
@@ -423,6 +423,7 @@ void sqlite3SchemaFree(void *p){
     sqlite3DeleteTable(pTab);
   }
   sqlite3HashClear(&temp1);
+  sqlite3HashClear(&pSchema->fkeyHash);
   pSchema->pSeqTab = 0;
   pSchema->flags &= ~DB_SchemaLoaded;
 }
@@ -444,6 +445,7 @@ Schema *sqlite3SchemaGet(sqlite3 *db, Btree *pBt){
     sqlite3HashInit(&p->tblHash);
     sqlite3HashInit(&p->idxHash);
     sqlite3HashInit(&p->trigHash);
+    sqlite3HashInit(&p->fkeyHash);
     p->enc = SQLITE_UTF8;
   }
   return p;
diff --git a/src/delete.c b/src/delete.c
index 7e5b7bfc4..a384ade73 100644
--- a/src/delete.c
+++ b/src/delete.c
@@ -308,7 +308,7 @@ void sqlite3DeleteFrom(
     goto delete_from_cleanup;
   }
   if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
-  sqlite3BeginWriteOperation(pParse, (pTrigger?1:0), iDb);
+  sqlite3BeginWriteOperation(pParse, 1, iDb);
 
   /* If we are trying to delete from a view, realize that view into
   ** a ephemeral table.
@@ -341,7 +341,9 @@ void sqlite3DeleteFrom(
   ** It is easier just to erase the whole table. Prior to version 3.6.5,
   ** this optimization caused the row change count (the value returned by 
   ** API function sqlite3_count_changes) to be set incorrectly.  */
-  if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) ){
+  if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) 
+   && 0==sqlite3FkRequired(pParse, pTab, 0)
+  ){
     assert( !isView );
     sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt,
                       pTab->zName, P4_STATIC);
@@ -446,7 +448,7 @@ delete_from_cleanup:
 ** These are the requirements:
 **
 **   1.  A read/write cursor pointing to pTab, the table containing the row
-**       to be deleted, must be opened as cursor number "base".
+**       to be deleted, must be opened as cursor number $iCur.
 **
 **   2.  Read/write cursors for all indices of pTab must be open as
 **       cursor number base+i for the i-th index.
@@ -481,13 +483,13 @@ void sqlite3GenerateRowDelete(
  
   /* If there are any triggers to fire, allocate a range of registers to
   ** use for the old.* references in the triggers.  */
-  if( pTrigger ){
+  if( sqlite3FkRequired(pParse, pTab, 0) || pTrigger ){
     u32 mask;                     /* Mask of OLD.* columns in use */
     int iCol;                     /* Iterator used while populating OLD.* */
 
     /* TODO: Could use temporary registers here. Also could attempt to
     ** avoid copying the contents of the rowid register.  */
-    mask = sqlite3TriggerOldmask(pParse, pTrigger, TK_DELETE, 0, pTab, onconf);
+    mask = sqlite3TriggerOldmask(pParse, pTrigger, 0, pTab, onconf);
     iOld = pParse->nMem+1;
     pParse->nMem += (1 + pTab->nCol);
 
@@ -495,14 +497,14 @@ void sqlite3GenerateRowDelete(
     ** used by any BEFORE and AFTER triggers that exist.  */
     sqlite3VdbeAddOp2(v, OP_Copy, iRowid, iOld);
     for(iCol=0; iCol<pTab->nCol; iCol++){
-      if( mask==0xffffffff || mask&(1<<iCol) ){
+      if( 1 || mask==0xffffffff || mask&(1<<iCol) ){
         int iTarget = iOld + iCol + 1;
         sqlite3VdbeAddOp3(v, OP_Column, iCur, iCol, iTarget);
         sqlite3ColumnDefault(v, pTab, iCol, iTarget);
       }
     }
 
-    /* Invoke any BEFORE trigger programs */
+    /* Invoke BEFORE DELETE trigger programs. */
     sqlite3CodeRowTrigger(pParse, pTrigger, 
         TK_DELETE, 0, TRIGGER_BEFORE, pTab, -1, iOld, onconf, iLabel
     );
@@ -512,6 +514,11 @@ void sqlite3GenerateRowDelete(
     ** being deleted. Do not attempt to delete the row a second time, and 
     ** do not fire AFTER triggers.  */
     sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);
+
+    /* Do FK processing. This call checks that any FK constraints that
+    ** refer to this table (i.e. constraints attached to other tables) 
+    ** are not violated by deleting this row.  */
+    sqlite3FkCheck(pParse, pTab, 0, iOld, 0);
   }
 
   /* Delete the index and table entries. Skip this step if pTab is really
@@ -525,12 +532,15 @@ void sqlite3GenerateRowDelete(
     }
   }
 
-  /* Invoke AFTER triggers. */
-  if( pTrigger ){
-    sqlite3CodeRowTrigger(pParse, pTrigger, 
-        TK_DELETE, 0, TRIGGER_AFTER, pTab, -1, iOld, onconf, iLabel
-    );
-  }
+  /* 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 deleted. */ 
+  sqlite3FkActions(pParse, pTab, 0, iOld);
+
+  /* Invoke AFTER DELETE trigger programs. */
+  sqlite3CodeRowTrigger(pParse, pTrigger, 
+      TK_DELETE, 0, TRIGGER_AFTER, pTab, -1, iOld, onconf, iLabel
+  );
 
   /* Jump here if the row had already been deleted before any BEFORE
   ** trigger programs were invoked. Or if a trigger program throws a 
diff --git a/src/fkey.c b/src/fkey.c
new file mode 100644
index 000000000..1d9895aff
--- /dev/null
+++ b/src/fkey.c
@@ -0,0 +1,784 @@
+/*
+**
+** 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 contains code used by the compiler to add foreign key
+** support to compiled SQL statements.
+*/
+#include "sqliteInt.h"
+
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+
+/*
+** Deferred and Immediate FKs
+** --------------------------
+**
+** Foreign keys in SQLite come in two flavours: deferred and immediate.
+** If an immediate foreign key constraint is violated, an OP_Halt is 
+** executed and the current statement transaction rolled back. If a 
+** deferred foreign key constraint is violated, no action is taken 
+** immediately. However if the application attempts to commit the 
+** transaction before fixing the constraint violation, the attempt fails.
+**
+** Deferred constraints are implemented using a simple counter associated
+** with the database handle. The counter is set to zero each time a 
+** database transaction is opened. Each time a statement is executed 
+** that causes a foreign key violation, the counter is incremented. Each
+** time a statement is executed that removes an existing violation from
+** the database, the counter is decremented. When the transaction is
+** committed, the commit fails if the current value of the counter is
+** greater than zero. This scheme has two big drawbacks:
+**
+**   * When a commit fails due to a deferred foreign key constraint, 
+**     there is no way to tell which foreign constraint is not satisfied,
+**     or which row it is not satisfied for.
+**
+**   * If the database contains foreign key violations when the 
+**     transaction is opened, this may cause the mechanism to malfunction.
+**
+** Despite these problems, this approach is adopted as it seems simpler
+** than the alternatives.
+**
+** INSERT operations:
+**
+**   I.1) For each FK for which the table is the referencing table, search
+**        the referenced table for a match. If none is found, throw an 
+**        exception for an immediate FK, or increment the counter for a
+**        deferred FK.
+**
+**   I.2) For each deferred FK for which the table is the referenced table, 
+**        search the referencing table for rows that correspond to the new
+**        row in the referenced table. Decrement the counter for each row
+**        found (as the constraint is now satisfied).
+**
+** DELETE operations:
+**
+**   D.1) For each deferred FK for which the table is the referencing table, 
+**        search the referenced table for a row that corresponds to the 
+**        deleted row in the referencing table. If such a row is not found, 
+**        decrement the counter.
+**
+**   D.2) For each FK for which the table is the referenced table, search 
+**        the referencing table for rows that correspond to the deleted row 
+**        in the referenced table. For each found, throw an exception for an
+**        immediate FK, or increment the counter for a deferred FK.
+**
+** UPDATE operations:
+**
+**   An UPDATE command requires that all 4 steps above are taken, but only
+**   for FK constraints for which the affected columns are actually 
+**   modified (values must be compared at runtime).
+**
+** Note that I.1 and D.1 are very similar operations, as are I.2 and D.2.
+** This simplifies the implementation a bit.
+**
+** For the purposes of immediate FK constraints, the OR REPLACE conflict
+** resolution is considered to delete rows before the new row is inserted.
+** If a delete caused by OR REPLACE violates an FK constraint, an exception
+** is thrown, even if the FK constraint would be satisfied after the new 
+** row is inserted.
+**
+** TODO: How should dropping a table be handled? How should renaming a 
+** table be handled?
+*/
+
+/*
+** Query API Notes
+** ---------------
+**
+** Before coding an UPDATE or DELETE row operation, the code-generator
+** for those two operations needs to know whether or not the operation
+** requires any FK processing and, if so, which columns of the original
+** row are required by the FK processing VDBE code (i.e. if FKs were
+** implemented using triggers, which of the old.* columns would be 
+** accessed). No information is required by the code-generator before
+** coding an INSERT operation.
+**
+*/
+
+/*
+** VDBE Calling Convention
+** -----------------------
+**
+** Example:
+**
+**   For the following INSERT statement:
+**
+**     CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c);
+**     INSERT INTO t1 VALUES(1, 2, 3.1);
+**
+**   Register (x):        2    (type integer)
+**   Register (x+1):      1    (type integer)
+**   Register (x+2):      NULL (type NULL)
+**   Register (x+3):      3.1  (type real)
+*/
+
+/*
+** ON UPDATE and ON DELETE clauses
+** -------------------------------
+*/
+
+/*
+** Externally accessible module functions
+** --------------------------------------
+**
+**   sqlite3FkRequired()
+**   sqlite3FkOldmask()
+**
+**   sqlite3FkCheck()
+**   sqlite3FkActions()
+**
+**   sqlite3FkDelete()
+** 
+*/
+
+/*
+** A foreign key constraint requires that the key columns in the referenced
+** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
+** Given that pTo is the referenced table for foreign key constraint
+** pFKey, check that the columns in pTo are indeed subject to a such a
+** constraint. If they are not, return non-zero and leave an error in pParse.
+**
+** If an error does not occur, return zero.
+*/
+static int locateFkeyIndex(
+  Parse *pParse,                  /* Parse context to store any error in */
+  Table *pTo,                     /* Referenced table */
+  FKey *pFKey,                    /* Foreign key to find index for */
+  Index **ppIdx,                  /* OUT: Unique index on referenced table */
+  int **paiCol                    /* OUT: Map of index columns in pFKey */
+){
+  Index *pIdx = 0;
+  int *aiCol = 0;
+  int nCol = pFKey->nCol;
+  char *zFirst = pFKey->aCol[0].zCol;
+
+  /* The caller is responsible for zeroing output parameters. */
+  assert( ppIdx && *ppIdx==0 );
+  assert( !paiCol || *paiCol==0 );
+
+  /* If this is a non-composite (single column) foreign key, check if it 
+  ** maps to the INTEGER PRIMARY KEY of table pTo. 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).
+  ** Non-composite foreign keys do not require the aiCol array.
+  */
+  if( nCol==1 ){
+    /* The FK maps to the IPK if any of the following are true:
+    **
+    **   1) The FK is explicitly mapped to "rowid", "oid" or "_rowid_", or
+    **   2) There is an explicit INTEGER PRIMARY KEY column and the FK is
+    **      implicitly mapped to the primary key of table pTo, or
+    **   3) The FK is explicitly mapped to a column declared as INTEGER
+    **      PRIMARY KEY.
+    */
+    if( zFirst && sqlite3IsRowid(zFirst) ) return 0;
+    if( pTo->iPKey>=0 ){
+      if( !zFirst ) return 0;
+      if( !sqlite3StrICmp(pTo->aCol[pTo->iPKey].zName, zFirst) ) return 0;
+    }
+  }else if( paiCol ){
+    assert( nCol>1 );
+    aiCol = (int *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int));
+    if( !aiCol ) return 1;
+    *paiCol = aiCol;
+  }
+
+  for(pIdx=pTo->pIndex; pIdx; pIdx=pIdx->pNext){
+    if( pIdx->nColumn==nCol && pIdx->onError!=OE_None ){ 
+      /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number
+      ** of columns. If each indexed column corresponds to a foreign key
+      ** column of pFKey, then this index is a winner.  */
+
+      if( zFirst==0 ){
+        /* If zFirst is NULL, then this foreign key is implicitly mapped to 
+        ** the PRIMARY KEY of table pTo. The PRIMARY KEY index may be 
+        ** identified by the test (Index.autoIndex==2).  */
+        if( pIdx->autoIndex==2 ){
+          if( aiCol ) memcpy(aiCol, pIdx->aiColumn, sizeof(int)*nCol);
+          break;
+        }
+      }else{
+        /* If zFirst is non-NULL, then this foreign key was declared to
+        ** map to an explicit list of columns in table pTo. Check if this
+        ** index matches those columns.  */
+        int i, j;
+        for(i=0; i<nCol; i++){
+          char *zIdxCol = pTo->aCol[pIdx->aiColumn[i]].zName;
+          for(j=0; j<nCol; j++){
+            if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){
+              if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom;
+              break;
+            }
+          }
+          if( j==nCol ) break;
+        }
+        if( i==nCol ) break;      /* pIdx is usable */
+      }
+    }
+  }
+
+  if( pParse && !pIdx ){
+    sqlite3ErrorMsg(pParse, "foreign key mismatch");
+    sqlite3DbFree(pParse->db, aiCol);
+    return 1;
+  }
+
+  *ppIdx = pIdx;
+  return 0;
+}
+
+static void fkCheckReference(
+  Parse *pParse,        /* Parse context */
+  int iDb,              /* Index of database housing pTab */
+  Table *pTab,          /* Table referenced by FK pFKey */
+  Index *pIdx,          /* Index ensuring uniqueness of FK in pTab */
+  FKey *pFKey,          /* Foreign key to check */
+  int *aiCol,           /* Map from FK column to referencing table column */
+  int regData,          /* Address of array containing referencing row */
+  int nIncr             /* If deferred FK, increment counter by this */
+){
+  int i;
+  Vdbe *v = sqlite3GetVdbe(pParse);
+  int iCur = pParse->nTab - 1;
+  int iOk = sqlite3VdbeMakeLabel(v);
+
+  assert( pFKey->isDeferred || nIncr==1 );
+
+  /* Check if any of the key columns in the referencing table are 
+  ** NULL. If any are, then the constraint is satisfied. No need
+  ** to search for a matching row in the referenced table.  */
+  for(i=0; i<pFKey->nCol; i++){
+    int iReg = pFKey->aCol[i].iFrom + regData + 1;
+    sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk);
+  }
+
+  if( pIdx==0 ){
+    /* If pIdx is NULL, then the foreign key constraint references the
+    ** INTEGER PRIMARY KEY column in the referenced table (table pTab).  */
+    int iReg = pFKey->aCol[0].iFrom + regData + 1;
+    sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
+    sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iReg);
+    sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
+    sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
+  }else{
+    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);
+
+    if( aiCol ){
+      int nCol = pFKey->nCol;
+      int regTemp = sqlite3GetTempRange(pParse, nCol);
+      for(i=0; i<nCol; i++){ 
+        sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[i]+1+regData, regTemp+i);
+      }
+      sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec);
+      sqlite3ReleaseTempRange(pParse, regTemp, nCol);
+    }else{
+      int iReg = pFKey->aCol[0].iFrom + regData + 1;
+      sqlite3VdbeAddOp3(v, OP_MakeRecord, iReg, 1, regRec);
+      sqlite3IndexAffinityStr(v, pIdx);
+    }
+
+    sqlite3VdbeAddOp3(v, OP_Found, iCur, iOk, regRec);
+    sqlite3ReleaseTempReg(pParse, regRec);
+  }
+
+  if( pFKey->isDeferred ){
+    assert( nIncr==1 || nIncr==-1 );
+    sqlite3VdbeAddOp1(v, OP_DeferredCons, nIncr);
+  }else{
+    sqlite3HaltConstraint(
+        pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
+    );
+  }
+
+  sqlite3VdbeResolveLabel(v, iOk);
+}
+
+static void fkScanReferences(
+  Parse *pParse,                  /* Parse context */
+  SrcList *pSrc,                  /* SrcList containing the table to scan */
+  Index *pIdx,                    /* Foreign key index */
+  FKey *pFKey,                    /* Foreign key relationship */
+  int *aiCol,                     /* Map from FK to referenced table columns */
+  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() */
+
+  for(i=0; i<pFKey->nCol; i++){
+    Expr *pLeft;                  /* Value from deleted row */
+    Expr *pRight;                 /* Column ref to referencing table */
+    Expr *pEq;                    /* Expression (pLeft = pRight) */
+    int iCol;                     /* Index of column in referencing table */ 
+    const char *zCol;             /* Name of column in referencing 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;
+    if( iCol<0 ){
+      zCol = "rowid";
+    }else{
+      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));
+  sNameContext.pSrcList = pSrc;
+  sNameContext.pParse = pParse;
+  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( pFKey->isDeferred && nIncr ){
+    assert( nIncr==1 || nIncr==-1 );
+    sqlite3VdbeAddOp1(pParse->pVdbe, OP_DeferredCons, nIncr);
+  }else{
+    assert( nIncr==1 || nIncr==0 );
+    sqlite3HaltConstraint(
+      pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
+    );
+  }
+  sqlite3WhereEnd(pWInfo);
+
+  /* Clean up the WHERE clause constructed above. */
+  sqlite3ExprDelete(db, pWhere);
+}
+
+/*
+** This function returns a pointer to the head of a linked list of FK
+** constraints that refer to the table passed as an argument. For example,
+** given the following schema:
+**
+**   CREATE TABLE t1(a PRIMARY KEY);
+**   CREATE TABLE t2(b REFERENCES t1(a);
+**
+** Calling this function with table "t1" as an argument returns a pointer
+** to the FKey structure representing the foreign key constraint on table
+** "t2". Calling this function with "t2" as the argument would return a
+** NULL pointer (as there are no FK constraints that refer to t2).
+*/
+static FKey *fkRefering(Table *pTab){
+  int nName = sqlite3Strlen30(pTab->zName);
+  return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName);
+}
+
+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 &&  regNew)           /* 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;
+
+  v = sqlite3GetVdbe(pParse);
+  iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
+  zDb = db->aDb[iDb].zName;
+
+  /* Loop through all the foreign key constraints attached to the table. */
+  for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
+    Table *pTo;                   /* Table referenced by this FK */
+    Index *pIdx = 0;              /* Index on key columns in pTo */
+    int *aiCol = 0;
+
+    if( pFKey->isDeferred==0 && regNew==0 ) continue;
+
+    /* Find the table this foreign key references. Also find a unique 
+    ** index on the referenced table that corresponds to the key columns. 
+    ** If either of these things cannot be located, set an error in pParse
+    ** and return early.  */
+    pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);
+    if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiCol) ) return;
+    assert( pFKey->nCol==1 || (aiCol && pIdx) );
+
+    /* If the key does not overlap with the pChanges list, skip this FK. */
+    if( pChanges ){
+      /* TODO */
+    }
+
+    /* Take a shared-cache advisory read-lock on the referenced table.
+    ** Allocate a cursor to use to search the unique index on the FK 
+    ** columns in the referenced table.  */
+    sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName);
+    pParse->nTab++;
+
+    if( regOld!=0 && pFKey->isDeferred ){
+      fkCheckReference(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1);
+    }
+    if( regNew!=0 ){
+      fkCheckReference(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1);
+    }
+
+    sqlite3DbFree(db, aiCol);
+  }
+
+  /* Loop through all the foreign key constraints that refer to this table */
+  for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){
+    int iGoto;                    /* Address of OP_Goto instruction */
+    Index *pIdx = 0;              /* Foreign key index for pFKey */
+    SrcList *pSrc;
+    int *aiCol = 0;
+
+    /* For immediate constraints, skip this scan if:
+    **
+    **   1) this is an INSERT operation, or
+    **   2) an UPDATE operation and the FK action is a trigger-action, or
+    **   3) a DELETE operation and the FK action is a trigger-action.
+    **
+    ** A "trigger-action" is one of CASCADE, SET DEFAULT or SET NULL.
+    */
+    if( pFKey->isDeferred==0 ){
+      if( regOld==0 ) continue;                                     /* 1 */
+      if( regNew!=0 && pFKey->updateConf>OE_Restrict ) continue;    /* 2 */
+      if( regNew==0 && pFKey->deleteConf>OE_Restrict ) continue;    /* 3 */
+    }
+
+    if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return;
+    assert( aiCol || pFKey->nCol==1 );
+
+    /* Check if this update statement has modified any of the key columns
+    ** for this foreign key constraint. If it has not, there is no need
+    ** to search the referencing table for rows in violation. This is
+    ** just an optimization. Things would work fine without this check.  */
+    if( pChanges ){
+      /* TODO */
+    }
+
+    /* Create a SrcList structure containing a single table (the table 
+    ** the foreign key that refers to this table is attached to). This
+    ** is required for the sqlite3WhereXXX() interface.  */
+    pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
+    if( !pSrc ) return;
+    pSrc->a->pTab = pFKey->pFrom;
+    pSrc->a->pTab->nRef++;
+    pSrc->a->iCursor = pParse->nTab++;
+
+    /* If this is an UPDATE, and none of the columns associated with this
+    ** FK have been modified, do not scan the referencing table. Unlike
+    ** the compile-time test implemented above, this is not just an 
+    ** optimization. It is required so that immediate foreign keys do not 
+    ** throw exceptions when the user executes a statement like:
+    **
+    **     UPDATE refd_table SET refd_column = refd_column
+    */
+    if( pChanges ){
+      int i;
+      int iJump = sqlite3VdbeCurrentAddr(v) + pFKey->nCol + 1;
+      for(i=0; i<pFKey->nCol; i++){
+        int iOff = (pIdx ? pIdx->aiColumn[i] : -1) + 1;
+        sqlite3VdbeAddOp3(v, OP_Ne, regOld+iOff, iJump, regNew+iOff);
+      }
+      iGoto = sqlite3VdbeAddOp0(v, OP_Goto);
+    }
+
+    if( regNew!=0 && pFKey->isDeferred ){
+      fkScanReferences(pParse, pSrc, pIdx, pFKey, aiCol, regNew, -1);
+    }
+    if( regOld!=0 ){
+      /* If there is a RESTRICT action configured for the current operation
+      ** on the referenced 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.  */
+      fkScanReferences(pParse, pSrc, pIdx, pFKey, aiCol, regOld, 
+          (pChanges!=0 && pFKey->updateConf!=OE_Restrict)
+       || (pChanges==0 && pFKey->deleteConf!=OE_Restrict)
+      );
+    }
+
+    if( pChanges ){
+      sqlite3VdbeJumpHere(v, iGoto);
+    }
+    sqlite3SrcListDelete(db, pSrc);
+    sqlite3DbFree(db, aiCol);
+  }
+}
+
+#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
+
+/*
+** This function is called before generating code to update or delete a 
+** row contained in table pTab. If the operation is an update, then 
+** pChanges is a pointer to the list of columns to modify. If this is a 
+** delete, then pChanges is NULL.
+*/
+u32 sqlite3FkOldmask(
+  Parse *pParse,                  /* Parse context */
+  Table *pTab,                    /* Table being modified */
+  ExprList *pChanges              /* Non-NULL for UPDATE operations */
+){
+  u32 mask = 0;
+  if( pParse->db->flags&SQLITE_ForeignKeys ){
+    FKey *p;
+    int i;
+    for(p=pTab->pFKey; p; p=p->pNextFrom){
+      if( pChanges || p->isDeferred ){
+        for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
+      }
+    }
+    for(p=fkRefering(pTab); p; p=p->pNextTo){
+      Index *pIdx = 0;
+      locateFkeyIndex(0, pTab, p, &pIdx, 0);
+      if( pIdx ){
+        for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
+      }
+    }
+  }
+  return mask;
+}
+
+/*
+** This function is called before generating code to update or delete a 
+** row contained in table pTab. If the operation is an update, then 
+** pChanges is a pointer to the list of columns to modify. If this is a 
+** delete, then pChanges is NULL.
+**
+** If any foreign key processing will be required, this function returns
+** true. If there is no foreign key related processing, this function 
+** returns false.
+*/
+int sqlite3FkRequired(
+  Parse *pParse,                  /* Parse context */
+  Table *pTab,                    /* Table being modified */
+  ExprList *pChanges              /* Non-NULL for UPDATE operations */
+){
+  if( pParse->db->flags&SQLITE_ForeignKeys ){
+    FKey *p;
+    for(p=pTab->pFKey; p; p=p->pNextFrom){
+      if( pChanges || p->isDeferred ) return 1;
+    }
+    if( fkRefering(pTab) ) return 1;
+  }
+  return 0;
+}
+
+static Trigger *fkActionTrigger(
+  Parse *pParse,
+  Table *pTab,                    /* Table being updated or deleted from */
+  FKey *pFKey,                    /* Foreign key to get action for */
+  ExprList *pChanges              /* Change-list for UPDATE, NULL for DELETE */
+){
+  sqlite3 *db = pParse->db;       /* Database handle */
+  int action;
+  Trigger *pTrigger;
+
+  if( pChanges ){
+    action = pFKey->updateConf;
+    pTrigger = pFKey->pOnUpdate;
+  }else{
+    action = pFKey->deleteConf;
+    pTrigger = pFKey->pOnDelete;
+  }
+
+  assert( OE_SetNull>OE_Restrict && OE_SetDflt>OE_Restrict );
+  assert( OE_Cascade>OE_Restrict && OE_None<OE_Restrict );
+
+  if( action>OE_Restrict && !pTrigger ){
+    char const *zFrom;            /* Name of referencing table */
+    int nFrom;                    /* Length in bytes of zFrom */
+    Index *pIdx = 0;
+    int *aiCol = 0;
+    TriggerStep *pStep;
+    sqlite3 *dbMem = pTab->dbMem;
+    Expr *pWhere = 0;
+    ExprList *pList = 0;
+    int i;
+
+    if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
+    assert( aiCol || pFKey->nCol==1 );
+
+    assert( dbMem==0 || dbMem==pParse->db );
+    zFrom = pFKey->pFrom->zName;
+    nFrom = sqlite3Strlen30(zFrom);
+    pTrigger = (Trigger *)sqlite3DbMallocZero(dbMem, 
+        sizeof(Trigger) +         /* struct Trigger */
+        sizeof(TriggerStep) +     /* Single step in trigger program */
+        nFrom + 1                 /* Space for pStep->target.z */
+    );
+    if( !pTrigger ){
+      pParse->db->mallocFailed = 1;
+      return 0;
+    }
+    pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
+    pStep->target.z = (char *)&pStep[1];
+    pStep->target.n = nFrom;
+    memcpy((char *)pStep->target.z, zFrom, nFrom);
+
+    for(i=0; i<pFKey->nCol; i++){
+      Expr *pEq;
+      int iFromCol;               /* Idx of column in referencing table */
+      Token tFromCol;             /* Name of column in referencing table */
+      Token tToCol;               /* Name of column in referenced table */
+      Token tOld = { "old", 3 };  /* Literal "old" token */
+      Token tNew = { "new", 3 };  /* Literal "new" token */
+
+      iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
+      tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
+      tFromCol.z = iFromCol<0 ? "oid" : 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),
+          sqlite3PExpr(pParse, TK_DOT, 
+            sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
+            sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
+          , 0)
+      , 0);
+      pWhere = sqlite3ExprAnd(pParse->db, pWhere, pEq);
+
+      if( 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 ){
+          Expr *pDflt = pIdx ? 0 : pTab->aCol[pIdx->aiColumn[i]].pDflt;
+          if( pDflt ){
+            pNew = sqlite3ExprDup(db, pDflt, 0);
+          }else{
+            pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
+          }
+        }else{
+          pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
+        }
+        pList = sqlite3ExprListAppend(pParse, pList, pNew);
+        sqlite3ExprListSetName(pParse, pList, &tFromCol, 0);
+      }
+    }
+    sqlite3DbFree(pParse->db, aiCol);
+
+    pStep->pWhere = sqlite3ExprDup(dbMem, pWhere, EXPRDUP_REDUCE);
+    pStep->pExprList = sqlite3ExprListDup(dbMem, pList, EXPRDUP_REDUCE);
+    sqlite3ExprDelete(pParse->db, pWhere);
+    sqlite3ExprListDelete(pParse->db, pList);
+
+    pStep->op = (action!=OE_Cascade || pChanges) ? TK_UPDATE : TK_DELETE;
+    pStep->pTrig = pTrigger;
+    pTrigger->pSchema = pTab->pSchema;
+    pTrigger->pTabSchema = pTab->pSchema;
+
+    if( pChanges ){
+      pFKey->pOnUpdate = pTrigger;
+      pTrigger->op = TK_UPDATE;
+      pStep->op = TK_UPDATE;
+    }else{
+      pFKey->pOnDelete = pTrigger;
+      pTrigger->op = TK_DELETE;
+      pStep->op = (action==OE_Cascade)?TK_DELETE:TK_UPDATE;
+    }
+  }
+
+  return pTrigger;
+}
+
+static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){
+  if( p ){
+    TriggerStep *pStep = p->step_list;
+    sqlite3ExprDelete(dbMem, pStep->pWhere);
+    sqlite3ExprListDelete(dbMem, pStep->pExprList);
+    sqlite3DbFree(dbMem, p);
+  }
+}
+
+/*
+** This function is called when deleting or updating a row to implement
+** any required CASCADE, SET NULL or SET DEFAULT actions.
+*/
+void sqlite3FkActions(
+  Parse *pParse,                  /* Parse context */
+  Table *pTab,                    /* Table being updated or deleted from */
+  ExprList *pChanges,             /* Change-list for UPDATE, NULL for DELETE */
+  int regOld                      /* Address of array containing old row */
+){
+  /* If foreign-key support is enabled, iterate through all FKs that 
+  ** refer to table pTab. If there is an action associated with the FK 
+  ** for this operation (either update or delete), invoke the associated 
+  ** trigger sub-program.  */
+  if( pParse->db->flags&SQLITE_ForeignKeys ){
+    FKey *pFKey;                  /* Iterator variable */
+    for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){
+      Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges);
+      if( pAction ){
+        sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0);
+      }
+    }
+  }
+}
+
+/*
+** Free all memory associated with foreign key definitions attached to
+** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash
+** hash table.
+*/
+void sqlite3FkDelete(Table *pTab){
+  FKey *pFKey;                    /* Iterator variable */
+  FKey *pNext;                    /* Copy of pFKey->pNextFrom */
+
+  for(pFKey=pTab->pFKey; pFKey; pFKey=pNext){
+
+    /* Remove the FK from the fkeyHash hash table. */
+    if( pFKey->pPrevTo ){
+      pFKey->pPrevTo->pNextTo = pFKey->pNextTo;
+    }else{
+      void *data = (void *)pFKey->pNextTo;
+      const char *z = (data ? pFKey->pNextTo->zTo : pFKey->zTo);
+      sqlite3HashInsert(&pTab->pSchema->fkeyHash, z, sqlite3Strlen30(z), data);
+    }
+    if( pFKey->pNextTo ){
+      pFKey->pNextTo->pPrevTo = pFKey->pPrevTo;
+    }
+
+    /* Delete any triggers created to implement actions for this FK. */
+    fkTriggerDelete(pTab->dbMem, pFKey->pOnDelete);
+    fkTriggerDelete(pTab->dbMem, pFKey->pOnUpdate);
+
+    /* Delete the memory allocated for the FK structure. */
+    pNext = pFKey->pNextFrom;
+    sqlite3DbFree(pTab->dbMem, pFKey);
+  }
+}
+
+#endif
diff --git a/src/insert.c b/src/insert.c
index f27d50f23..071f65d56 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -979,6 +979,7 @@ void sqlite3Insert(
       sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx,
           keyColumn>=0, 0, onError, endOfLoop, &isReplace
       );
+      sqlite3FkCheck(pParse, pTab, 0, 0, regIns);
       sqlite3CompleteInsertion(
           pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0
       );
@@ -1377,7 +1378,7 @@ void sqlite3GenerateConstraintChecks(
     sqlite3VdbeJumpHere(v, j3);
     sqlite3ReleaseTempReg(pParse, regR);
   }
-
+  
   if( pbMayReplace ){
     *pbMayReplace = seenReplace;
   }
diff --git a/src/main.c b/src/main.c
index c904d4011..6f5e98e8a 100644
--- a/src/main.c
+++ b/src/main.c
@@ -731,6 +731,9 @@ void sqlite3RollbackAll(sqlite3 *db){
     sqlite3ResetInternalSchema(db, 0);
   }
 
+  /* Any deferred constraint violations have now been resolved. */
+  db->nDeferredCons = 0;
+
   /* If one has been configured, invoke the rollback-hook callback */
   if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){
     db->xRollbackCallback(db->pRollbackArg);
diff --git a/src/parse.y b/src/parse.y
index 39af6c0ce..f3ea25e08 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -196,9 +196,9 @@ id(A) ::= INDEXED(X).    {A = X;}
 // This obviates the need for the "id" nonterminal.
 //
 %fallback ID
-  ABORT AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW CONFLICT
-  DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
-  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH PLAN
+  ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
+  CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
+  IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
   QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK
   SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL
 %ifdef SQLITE_OMIT_COMPOUND_SELECT
@@ -314,20 +314,20 @@ autoinc(X) ::= AUTOINCR.  {X = 1;}
 // check fails.
 //
 %type refargs {int}
-refargs(A) ::= .                     { A = OE_Restrict * 0x010101; }
+refargs(A) ::= .                     { A = OE_None * 0x010101; }
 refargs(A) ::= refargs(X) refarg(Y). { A = (X & ~Y.mask) | Y.value; }
 %type refarg {struct {int value; int mask;}}
 refarg(A) ::= MATCH nm.              { A.value = 0;     A.mask = 0x000000; }
 refarg(A) ::= ON DELETE refact(X).   { A.value = X;     A.mask = 0x0000ff; }
 refarg(A) ::= ON UPDATE refact(X).   { A.value = X<<8;  A.mask = 0x00ff00; }
-refarg(A) ::= ON INSERT refact(X).   { A.value = X<<16; A.mask = 0xff0000; }
 %type refact {int}
 refact(A) ::= SET NULL.              { A = OE_SetNull; }
 refact(A) ::= SET DEFAULT.           { A = OE_SetDflt; }
 refact(A) ::= CASCADE.               { A = OE_Cascade; }
 refact(A) ::= RESTRICT.              { A = OE_Restrict; }
+refact(A) ::= NO ACTION.             { A = OE_None; }
 %type defer_subclause {int}
-defer_subclause(A) ::= NOT DEFERRABLE init_deferred_pred_opt(X).  {A = X;}
+defer_subclause(A) ::= NOT DEFERRABLE init_deferred_pred_opt.     {A = 0;}
 defer_subclause(A) ::= DEFERRABLE init_deferred_pred_opt(X).      {A = X;}
 %type init_deferred_pred_opt {int}
 init_deferred_pred_opt(A) ::= .                       {A = 0;}
diff --git a/src/pragma.c b/src/pragma.c
index 60f214a0c..e2ad17d76 100644
--- a/src/pragma.c
+++ b/src/pragma.c
@@ -191,6 +191,9 @@ static int flagPragma(Parse *pParse, const char *zLeft, const char *zRight){
     ** flag if there are any active statements. */
     { "read_uncommitted",         SQLITE_ReadUncommitted },
     { "recursive_triggers",       SQLITE_RecTriggers },
+
+    /* TODO: Prevent this flag from being set if not in auto-commit mode? */
+    { "foreign_keys",             SQLITE_ForeignKeys },
   };
   int i;
   const struct sPragmaType *p;
@@ -231,11 +234,12 @@ static int flagPragma(Parse *pParse, const char *zLeft, const char *zRight){
 static const char *actionName(u8 action){
   const char *zName;
   switch( action ){
-    case OE_SetNull:  zName = "SET NULL";            break;
-    case OE_SetDflt:  zName = "SET DEFAULT";         break;
-    case OE_Cascade:  zName = "CASCADE";             break;
-    default:          zName = "RESTRICT";  
-                      assert( action==OE_Restrict ); break;
+    case OE_SetNull:  zName = "SET NULL";        break;
+    case OE_SetDflt:  zName = "SET DEFAULT";     break;
+    case OE_Cascade:  zName = "CASCADE";         break;
+    case OE_Restrict: zName = "RESTRICT";        break;
+    default:          zName = "NO ACTION";  
+                      assert( action==OE_None ); break;
   }
   return zName;
 }
diff --git a/src/shell.c b/src/shell.c
index 68835723a..137ce3574 100644
--- a/src/shell.c
+++ b/src/shell.c
@@ -851,7 +851,7 @@ static int genfkey_create_triggers(
       "  /delete_action/\n"
       "END;\n"
 
-      /* The "BEFORE DELETE ON <referenced>" trigger. This trigger's job 
+      /* The "AFTER UPDATE ON <referenced>" trigger. This trigger's job 
       ** is to detect when the key columns of a row in the referenced table 
       ** to which one or more rows in the referencing table correspond are
       ** updated. The action taken depends on the value of the 'ON UPDATE' 
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index fa5aaa0b4..b59e2bf5b 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -670,6 +670,7 @@ struct Schema {
   Hash tblHash;        /* All tables indexed by name */
   Hash idxHash;        /* All (named) indices indexed by name */
   Hash trigHash;       /* All triggers indexed by name */
+  Hash fkeyHash;       /* All foreign keys by referenced table name */
   Table *pSeqTab;      /* The sqlite_sequence table used by AUTOINCREMENT */
   u8 file_format;      /* Schema format version for this file */
   u8 enc;              /* Text encoding used by this database */
@@ -860,6 +861,9 @@ struct sqlite3 {
   int nSavepoint;               /* Number of non-transaction savepoints */
   int nStatement;               /* Number of nested statement-transactions  */
   u8 isTransactionSavepoint;    /* True if the outermost savepoint is a TS */
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+  i64 nDeferredCons;            /* Net deferred constraints this transaction. */
+#endif
 
 #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
   /* The following variables are all protected by the STATIC_MASTER 
@@ -916,6 +920,7 @@ struct sqlite3 {
 #define SQLITE_RecoveryMode   0x00040000  /* Ignore schema errors */
 #define SQLITE_ReverseOrder   0x00100000  /* Reverse unordered SELECTs */
 #define SQLITE_RecTriggers    0x00200000  /* Enable recursive triggers */
+#define SQLITE_ForeignKeys    0x00400000  /* Enforce foreign key constraints  */
 
 /*
 ** Possible values for the sqlite.magic field.
@@ -1002,6 +1007,7 @@ struct FuncDef {
 */
 struct Savepoint {
   char *zName;                        /* Savepoint name (nul-terminated) */
+  int nDeferredCons;                  /* Number of deferred fk violations */
   Savepoint *pNext;                   /* Parent savepoint (if any) */
 };
 
@@ -1281,11 +1287,15 @@ struct FKey {
   Table *pFrom;     /* The table that contains the REFERENCES clause */
   FKey *pNextFrom;  /* Next foreign key in pFrom */
   char *zTo;        /* Name of table that the key points to */
+  FKey *pNextTo;    /* Next foreign key on table named zTo */
+  FKey *pPrevTo;    /* Previous foreign key on table named zTo */
   int nCol;         /* Number of columns in this key */
   u8 isDeferred;    /* True if constraint checking is deferred till COMMIT */
   u8 updateConf;    /* How to resolve conflicts that occur on UPDATE */
   u8 deleteConf;    /* How to resolve conflicts that occur on DELETE */
   u8 insertConf;    /* How to resolve conflicts that occur on INSERT */
+  Trigger *pOnUpdate;  /* Trigger for AFTER UPDATE ON zTo */
+  Trigger *pOnDelete;  /* Trigger for AFTER DELETE ON zTo */
   struct sColMap {  /* Mapping of columns in pFrom to columns in zTo */
     int iFrom;         /* Index of column in pFrom */
     char *zCol;        /* Name of column in zTo.  If 0 use PRIMARY KEY */
@@ -2586,7 +2596,7 @@ void sqlite3SrcListShiftJoinType(SrcList*);
 void sqlite3SrcListAssignCursors(Parse*, SrcList*);
 void sqlite3IdListDelete(sqlite3*, IdList*);
 void sqlite3SrcListDelete(sqlite3*, SrcList*);
-void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
+Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
                         Token*, int, int);
 void sqlite3DropIndex(Parse*, SrcList*, int);
 int sqlite3Select(Parse*, Select*, SelectDest*);
@@ -2694,6 +2704,7 @@ void sqlite3MaterializeView(Parse*, Table*, Expr*, int);
   Trigger *sqlite3TriggerList(Parse *, Table *);
   void sqlite3CodeRowTrigger(Parse*, Trigger *, int, ExprList*, int, Table *,
                             int, int, int, int);
+  void sqlite3CodeRowTriggerDirect(Parse *, Trigger *, Table *, int, int, int);
   void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
   void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*);
   TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*);
@@ -2703,7 +2714,7 @@ void sqlite3MaterializeView(Parse*, Table*, Expr*, int);
   TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*);
   void sqlite3DeleteTrigger(sqlite3*, Trigger*);
   void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*);
-  u32 sqlite3TriggerOldmask(Parse*,Trigger*,int,ExprList*,Table*,int);
+  u32 sqlite3TriggerOldmask(Parse*,Trigger*,ExprList*,Table*,int);
 # define sqlite3ParseToplevel(p) ((p)->pToplevel ? (p)->pToplevel : (p))
 #else
 # define sqlite3TriggersExist(B,C,D,E,F) 0
@@ -2713,7 +2724,7 @@ void sqlite3MaterializeView(Parse*, Table*, Expr*, int);
 # define sqlite3CodeRowTrigger(A,B,C,D,E,F,G,H,I,J)
 # define sqlite3TriggerList(X, Y) 0
 # define sqlite3ParseToplevel(p) p
-# define sqlite3TriggerOldmask(A,B,C,D,E,F) 0
+# define sqlite3TriggerOldmask(A,B,C,D,E) 0
 #endif
 
 int sqlite3JoinType(Parse*, Token*, Token*, Token*);
@@ -2931,7 +2942,19 @@ CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
 int sqlite3TempInMemory(const sqlite3*);
 VTable *sqlite3GetVTable(sqlite3*, Table*);
 
-
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+  void sqlite3FkCheck(Parse*, Table*, ExprList*, int, int);
+  void sqlite3FkActions(Parse*, Table*, ExprList*, int);
+  void sqlite3FkDelete(Table*);
+  int sqlite3FkRequired(Parse*, Table*, ExprList*);
+  u32 sqlite3FkOldmask(Parse*, Table*, ExprList*);
+#else
+  #define sqlite3FkCheck(a,b,c,d,e)
+  #define sqlite3FkActions(a,b,c,d)
+  #define sqlite3FkDelete(a)
+  #define sqlite3FkRequired(a,b,c) 0
+  #define sqlite3FkOldmask(a,b,c)  0
+#endif
 
 /*
 ** Available fault injectors.  Should be numbered beginning with 0.
diff --git a/src/trigger.c b/src/trigger.c
index f06226734..79f7f486d 100644
--- a/src/trigger.c
+++ b/src/trigger.c
@@ -795,7 +795,7 @@ static TriggerPrg *codeRowTrigger(
   Parse *pSubParse;           /* Parse context for sub-vdbe */
   int iEndTrigger = 0;        /* Label to jump to if WHEN is false */
 
-  assert( pTab==tableOfTrigger(pTrigger) );
+  assert( pTrigger->zName==0 || pTab==tableOfTrigger(pTrigger) );
 
   /* Allocate the TriggerPrg and SubProgram objects. To ensure that they
   ** are freed if an error occurs, link them into the Parse.pTriggerPrg 
@@ -896,7 +896,7 @@ static TriggerPrg *getRowTrigger(
   Parse *pRoot = sqlite3ParseToplevel(pParse);
   TriggerPrg *pPrg;
 
-  assert( pTab==tableOfTrigger(pTrigger) );
+  assert( pTrigger->zName==0 || pTab==tableOfTrigger(pTrigger) );
 
   /* It may be that this trigger has already been coded (or is in the
   ** process of being coded). If this is the case, then an entry with
@@ -915,6 +915,38 @@ static TriggerPrg *getRowTrigger(
   return pPrg;
 }
 
+void sqlite3CodeRowTriggerDirect(
+  Parse *pParse,       /* Parse context */
+  Trigger *p,          /* Trigger to code */
+  Table *pTab,         /* The table to code triggers from */
+  int oldIdx,          /* The indice of the "old" row to access */
+  int orconf,          /* ON CONFLICT policy */
+  int ignoreJump       /* Instruction to jump to for RAISE(IGNORE) */
+){
+  Vdbe *v = sqlite3GetVdbe(pParse); /* Main VM */
+  TriggerPrg *pPrg;
+  pPrg = getRowTrigger(pParse, p, pTab, orconf);
+  assert( pPrg || pParse->nErr || pParse->db->mallocFailed );
+
+  /* Code the OP_Program opcode in the parent VDBE. P4 of the OP_Program 
+  ** is a pointer to the sub-vdbe containing the trigger program.  */
+  if( pPrg ){
+    sqlite3VdbeAddOp3(v, OP_Program, oldIdx, ignoreJump, ++pParse->nMem);
+    pPrg->pProgram->nRef++;
+    sqlite3VdbeChangeP4(v, -1, (const char *)pPrg->pProgram, P4_SUBPROGRAM);
+    VdbeComment(
+        (v, "Call: %s.%s", (p->zName?p->zName:"fkey"), onErrorText(orconf)));
+
+    /* Set the P5 operand of the OP_Program instruction to non-zero if
+    ** recursive invocation of this trigger program is disallowed. Recursive
+    ** invocation is disallowed if (a) the sub-program is really a trigger,
+    ** not a foreign key action, and (b) the flag to enable recursive triggers
+    ** is clear.  */
+    sqlite3VdbeChangeP5(v, p->zName && !(pParse->db->flags&SQLITE_RecTriggers));
+
+  }
+}
+
 /*
 ** This is called to code FOR EACH ROW triggers.
 **
@@ -976,19 +1008,7 @@ void sqlite3CodeRowTrigger(
      && p->tr_tm==tr_tm 
      && checkColumnOverlap(p->pColumns,pChanges)
     ){
-      Vdbe *v = sqlite3GetVdbe(pParse); /* Main VM */
-      TriggerPrg *pPrg;
-      pPrg = getRowTrigger(pParse, p, pTab, orconf);
-      assert( pPrg || pParse->nErr || pParse->db->mallocFailed );
-
-      /* Code the OP_Program opcode in the parent VDBE. P4 of the OP_Program 
-      ** is a pointer to the sub-vdbe containing the trigger program.  */
-      if( pPrg ){
-        sqlite3VdbeAddOp3(v, OP_Program, oldIdx, ignoreJump, ++pParse->nMem);
-        pPrg->pProgram->nRef++;
-        sqlite3VdbeChangeP4(v, -1, (const char *)pPrg->pProgram, P4_SUBPROGRAM);
-        VdbeComment((v, "Call: %s.%s", p->zName, onErrorText(orconf)));
-      }
+      sqlite3CodeRowTriggerDirect(pParse, p, pTab, oldIdx, orconf, ignoreJump);
     }
   }
 }
@@ -1015,15 +1035,14 @@ void sqlite3CodeRowTrigger(
 u32 sqlite3TriggerOldmask(
   Parse *pParse,       /* Parse context */
   Trigger *pTrigger,   /* List of triggers on table pTab */
-  int op,              /* Either TK_UPDATE or TK_DELETE */
   ExprList *pChanges,  /* Changes list for any UPDATE OF triggers */
   Table *pTab,         /* The table to code triggers from */
   int orconf           /* Default ON CONFLICT policy for trigger steps */
 ){
+  const int op = pChanges ? TK_UPDATE : TK_DELETE;
   u32 mask = 0;
   Trigger *p;
 
-  assert(op==TK_UPDATE || op==TK_DELETE);
   for(p=pTrigger; p; p=p->pNext){
     if( p->op==op && checkColumnOverlap(p->pColumns,pChanges) ){
       TriggerPrg *pPrg;
diff --git a/src/update.c b/src/update.c
index f0880de40..c5463bc73 100644
--- a/src/update.c
+++ b/src/update.c
@@ -115,12 +115,12 @@ void sqlite3Update(
   int iDb;               /* Database containing the table being updated */
   int j1;                /* Addresses of jump instructions */
   int okOnePass;         /* True for one-pass algorithm without the FIFO */
+  int hasFK;             /* True if foreign key processing is required */
 
 #ifndef SQLITE_OMIT_TRIGGER
   int isView;                  /* Trying to update a view */
   Trigger *pTrigger;           /* List of triggers on pTab, if required */
 #endif
-  u32 oldmask = 0;        /* Mask of OLD.* columns in use */
 
   /* Register Allocations */
   int regRowCount = 0;   /* A count of rows changed */
@@ -159,6 +159,8 @@ void sqlite3Update(
 # define isView 0
 #endif
 
+  hasFK = sqlite3FkRequired(pParse, pTab, pChanges);
+
   if( sqlite3ViewGetColumnNames(pParse, pTab) ){
     goto update_cleanup;
   }
@@ -273,11 +275,11 @@ void sqlite3Update(
 
   /* Allocate required registers. */
   regOldRowid = regNewRowid = ++pParse->nMem;
-  if( pTrigger ){
+  if( pTrigger || hasFK ){
     regOld = pParse->nMem + 1;
     pParse->nMem += pTab->nCol;
   }
-  if( chngRowid || pTrigger ){
+  if( chngRowid || pTrigger || hasFK ){
     regNewRowid = ++pParse->nMem;
   }
   regNew = pParse->nMem + 1;
@@ -289,10 +291,6 @@ void sqlite3Update(
     sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
   }
 
-  /* If there are any triggers, set oldmask and new_col_mask. */
-  oldmask = sqlite3TriggerOldmask(
-      pParse, pTrigger, TK_UPDATE, pChanges, pTab, onError);
-
   /* If we are trying to update a view, realize that view into
   ** a ephemeral table.
   */
@@ -378,9 +376,21 @@ void sqlite3Update(
   ** for example, then jump to the next iteration of the RowSet loop.  */
   sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid);
 
+  /* If the record number will change, set register regNewRowid to
+  ** contain the new value. If the record number is not being modified,
+  ** then regNewRowid is the same register as regOldRowid, which is
+  ** already populated.  */
+  assert( chngRowid || pTrigger || hasFK || regOldRowid==regNewRowid );
+  if( chngRowid ){
+    sqlite3ExprCode(pParse, pRowidExpr, regNewRowid);
+    sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid);
+  }
+
   /* If there are triggers on this table, populate an array of registers 
   ** with the required old.* column data.  */
-  if( pTrigger ){
+  if( hasFK || pTrigger ){
+    u32 oldmask = sqlite3FkOldmask(pParse, pTab, pChanges);
+    oldmask |= sqlite3TriggerOldmask(pParse, pTrigger, pChanges, pTab, onError);
     for(i=0; i<pTab->nCol; i++){
       if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){
         sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regOld+i);
@@ -389,18 +399,9 @@ void sqlite3Update(
         sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i);
       }
     }
-  }
-
-  /* If the record number will change, set register regNewRowid to
-  ** contain the new value. If the record number is not being modified,
-  ** then regNewRowid is the same register as regOldRowid, which is
-  ** already populated.  */
-  assert( chngRowid || pTrigger || regOldRowid==regNewRowid );
-  if( chngRowid ){
-    sqlite3ExprCode(pParse, pRowidExpr, regNewRowid);
-    sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid);
-  }else if( pTrigger ){
-    sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid);
+    if( chngRowid==0 ){
+      sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid);
+    }
   }
 
   /* Populate the array of registers beginning at regNew with the new
@@ -443,6 +444,9 @@ void sqlite3Update(
     sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid,
         aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0);
 
+    /* Do FK constraint checks. */
+    sqlite3FkCheck(pParse, pTab, pChanges, regOldRowid, regNewRowid);
+
     /* Delete the index entries associated with the current record.  */
     j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
     sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
@@ -455,6 +459,11 @@ void sqlite3Update(
   
     /* 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. */ 
+    sqlite3FkActions(pParse, pTab, pChanges, regOldRowid);
   }
 
   /* Increment the row counter 
diff --git a/src/vdbe.c b/src/vdbe.c
index e25280a9e..f0e1c2215 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -872,10 +872,12 @@ case OP_Halt: {
     sqlite3SetString(&p->zErrMsg, db, "%s", pOp->p4.z);
   }
   rc = sqlite3VdbeHalt(p);
-  assert( rc==SQLITE_BUSY || rc==SQLITE_OK );
+  assert( rc==SQLITE_BUSY || rc==SQLITE_OK || rc==SQLITE_ERROR );
   if( rc==SQLITE_BUSY ){
     p->rc = rc = SQLITE_BUSY;
   }else{
+    assert( rc==SQLITE_OK || p->rc==SQLITE_CONSTRAINT );
+    assert( rc==SQLITE_OK || db->nDeferredCons>0 );
     rc = p->rc ? SQLITE_ERROR : SQLITE_DONE;
   }
   goto vdbe_return;
@@ -2507,6 +2509,7 @@ case OP_Savepoint: {
     
         /* Link the new savepoint into the database handle's list. */
         pNew->pNext = db->pSavepoint;
+        pNew->nDeferredCons = db->nDeferredCons;
         db->pSavepoint = pNew;
       }
     }
@@ -2545,6 +2548,9 @@ case OP_Savepoint: {
       */
       int isTransaction = pSavepoint->pNext==0 && db->isTransactionSavepoint;
       if( isTransaction && p1==SAVEPOINT_RELEASE ){
+        if( (rc = sqlite3VdbeCheckDeferred(p))!=SQLITE_OK ){
+          goto vdbe_return;
+        }
         db->autoCommit = 1;
         if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
           p->pc = pc;
@@ -2577,7 +2583,10 @@ case OP_Savepoint: {
         db->nSavepoint--;
       }
 
-      /* If it is a RELEASE, then destroy the savepoint being operated on too */
+      /* If it is a RELEASE, then destroy the savepoint being operated on 
+      ** too. If it is a ROLLBACK TO, then set the number of deferred 
+      ** constraint violations present in the database to the value stored
+      ** when the savepoint was created.  */
       if( p1==SAVEPOINT_RELEASE ){
         assert( pSavepoint==db->pSavepoint );
         db->pSavepoint = pSavepoint->pNext;
@@ -2585,6 +2594,8 @@ case OP_Savepoint: {
         if( !isTransaction ){
           db->nSavepoint--;
         }
+      }else{
+        db->nDeferredCons = pSavepoint->nDeferredCons;
       }
     }
   }
@@ -2633,6 +2644,8 @@ case OP_AutoCommit: {
       assert( desiredAutoCommit==1 );
       sqlite3RollbackAll(db);
       db->autoCommit = 1;
+    }else if( (rc = sqlite3VdbeCheckDeferred(p))!=SQLITE_OK ){
+      goto vdbe_return;
     }else{
       db->autoCommit = (u8)desiredAutoCommit;
       if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
@@ -2720,6 +2733,11 @@ case OP_Transaction: {
         p->iStatement = db->nSavepoint + db->nStatement;
       }
       rc = sqlite3BtreeBeginStmt(pBt, p->iStatement);
+
+      /* Store the current value of the database handles deferred constraint
+      ** counter. If the statement transaction needs to be rolled back,
+      ** the value of this counter needs to be restored too.  */
+      p->nStmtDefCons = db->nDeferredCons;
     }
   }
   break;
@@ -4733,18 +4751,18 @@ case OP_Program: {        /* jump */
   pRt = &p->aMem[pOp->p3];
   assert( pProgram->nOp>0 );
   
-  /* If the SQLITE_RecTriggers flag is clear, then recursive invocation of
-  ** triggers is disabled for backwards compatibility (flag set/cleared by
-  ** the "PRAGMA recursive_triggers" command). 
+  /* If the p5 flag is clear, then recursive invocation of triggers is 
+  ** disabled for backwards compatibility (p5 is set if this sub-program
+  ** is really a trigger, not a foreign key action, and the flag set
+  ** and cleared by the "PRAGMA recursive_triggers" command is clear).
   ** 
   ** It is recursive invocation of triggers, at the SQL level, that is 
   ** disabled. In some cases a single trigger may generate more than one 
   ** SubProgram (if the trigger may be executed with more than one different 
   ** ON CONFLICT algorithm). SubProgram structures associated with a
   ** single trigger all have the same value for the SubProgram.token 
-  ** variable.
-  */
-  if( 0==(db->flags&SQLITE_RecTriggers) ){
+  ** variable.  */
+  if( pOp->p5 ){
     t = pProgram->token;
     for(pFrame=p->pFrame; pFrame && pFrame->token!=t; pFrame=pFrame->pParent);
     if( pFrame ) break;
@@ -4842,6 +4860,18 @@ case OP_Param: {           /* out2-prerelease */
 
 #endif /* #ifndef SQLITE_OMIT_TRIGGER */
 
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+/* Opcode: DeferredCons P1 * * * *
+**
+** Increment the database handles "deferred constraint violation" counter
+** by P1 (P1 may be negative or positive).
+*/
+case OP_DeferredCons: {
+  db->nDeferredCons += pOp->p1;
+  break;
+}
+#endif /* #ifndef SQLITE_OMIT_FOREIGN_KEY */
+
 #ifndef SQLITE_OMIT_AUTOINCREMENT
 /* Opcode: MemMax P1 P2 * * *
 **
diff --git a/src/vdbeInt.h b/src/vdbeInt.h
index ecf8dcd54..0c3ffadc8 100644
--- a/src/vdbeInt.h
+++ b/src/vdbeInt.h
@@ -315,6 +315,7 @@ struct Vdbe {
   int aCounter[2];        /* Counters used by sqlite3_stmt_status() */
   char *zSql;             /* Text of the SQL statement that generated this */
   void *pFree;            /* Free this when deleting the vdbe */
+  i64 nStmtDefCons;       /* Number of def. constraints when stmt started */
   int iStatement;         /* Statement number (or 0 if has not opened stmt) */
 #ifdef SQLITE_DEBUG
   FILE *trace;            /* Write an execution trace here, if not NULL */
@@ -387,6 +388,12 @@ int sqlite3VdbeFrameRestore(VdbeFrame *);
 int sqlite3VdbeReleaseBuffers(Vdbe *p);
 #endif
 
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+int sqlite3VdbeCheckDeferred(Vdbe *);
+#else
+# define sqlite3VdbeCheckDeferred(p) 0
+#endif
+
 #ifndef SQLITE_OMIT_SHARED_CACHE
 void sqlite3VdbeMutexArrayEnter(Vdbe *p);
 #else
diff --git a/src/vdbeapi.c b/src/vdbeapi.c
index 634c29c45..66dfd7152 100644
--- a/src/vdbeapi.c
+++ b/src/vdbeapi.c
@@ -323,6 +323,8 @@ static int sqlite3Step(Vdbe *p){
       db->u1.isInterrupted = 0;
     }
 
+    assert( db->writeVdbeCnt>0 || db->autoCommit==0 || db->nDeferredCons==0 );
+
 #ifndef SQLITE_OMIT_TRACE
     if( db->xProfile && !db->init.busy ){
       double rNow;
diff --git a/src/vdbeaux.c b/src/vdbeaux.c
index c169b3727..fdf223e3b 100644
--- a/src/vdbeaux.c
+++ b/src/vdbeaux.c
@@ -1895,6 +1895,13 @@ int sqlite3VdbeCloseStatement(Vdbe *p, int eOp){
     }
     db->nStatement--;
     p->iStatement = 0;
+
+    /* If the statement transaction is being rolled back, also restore the 
+    ** database handles deferred constraint counter to the value it had when 
+    ** the statement transaction was opened.  */
+    if( eOp==SAVEPOINT_ROLLBACK ){
+      db->nDeferredCons = p->nStmtDefCons;
+    }
   }
   return rc;
 }
@@ -1927,6 +1934,28 @@ void sqlite3VdbeMutexArrayEnter(Vdbe *p){
 #endif
 
 /*
+** This function is called when a transaction opened by the database 
+** handle associated with the VM passed as an argument is about to be 
+** committed. If there are outstanding deferred foreign key constraint
+** violations, return SQLITE_ERROR. Otherwise, SQLITE_OK.
+**
+** If there are outstanding FK violations and this function returns 
+** SQLITE_ERROR, set the result of the VM to SQLITE_CONSTRAINT and write
+** an error message to it. Then return SQLITE_ERROR.
+*/
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+int sqlite3VdbeCheckDeferred(Vdbe *p){
+  sqlite3 *db = p->db;
+  if( db->nDeferredCons ){
+    p->rc = SQLITE_CONSTRAINT;
+    sqlite3SetString(&p->zErrMsg, db, "foreign key constraint failed");
+    return SQLITE_ERROR;
+  }
+  return SQLITE_OK;
+}
+#endif
+
+/*
 ** This routine is called the when a VDBE tries to halt.  If the VDBE
 ** has made changes and is in autocommit mode, then commit those
 ** changes.  If a rollback is needed, then do the rollback.
@@ -2012,10 +2041,14 @@ int sqlite3VdbeHalt(Vdbe *p){
      && db->writeVdbeCnt==(p->readOnly==0) 
     ){
       if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){
-        /* The auto-commit flag is true, and the vdbe program was 
-        ** successful or hit an 'OR FAIL' constraint. This means a commit 
-        ** is required.
-        */
+        if( sqlite3VdbeCheckDeferred(p) ){
+          sqlite3BtreeMutexArrayLeave(&p->aMutex);
+          return SQLITE_ERROR;
+        }
+        /* The auto-commit flag is true, the vdbe program was successful 
+        ** or hit an 'OR FAIL' constraint and there are no deferred foreign
+        ** key constraints to hold up the transaction. This means a commit 
+        ** is required.  */
         rc = vdbeCommit(db, p);
         if( rc==SQLITE_BUSY ){
           sqlite3BtreeMutexArrayLeave(&p->aMutex);
@@ -2024,6 +2057,7 @@ int sqlite3VdbeHalt(Vdbe *p){
           p->rc = rc;
           sqlite3RollbackAll(db);
         }else{
+          db->nDeferredCons = 0;
           sqlite3CommitInternalChanges(db);
         }
       }else{
diff --git a/src/vdbeblob.c b/src/vdbeblob.c
index 009234f4e..394335a92 100644
--- a/src/vdbeblob.c
+++ b/src/vdbeblob.c
@@ -144,25 +144,41 @@ int sqlite3_blob_open(
     }
 
     /* If the value is being opened for writing, check that the
-    ** column is not indexed. It is against the rules to open an
-    ** indexed column for writing.
-    */
+    ** column is not indexed, and that it is not part of a foreign key. 
+    ** It is against the rules to open a column to which either of these
+    ** descriptions applies for writing.  */
     if( flags ){
+      const char *zFault = 0;
       Index *pIdx;
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+      if( db->flags&SQLITE_ForeignKeys ){
+        FKey *pFKey;
+        for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
+          int j;
+          for(j=0; j<pFKey->nCol; j++){
+            if( pFKey->aCol[j].iFrom==iCol ){
+              zFault = "foreign key";
+            }
+          }
+        }
+      }
+#endif
       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
         int j;
         for(j=0; j<pIdx->nColumn; j++){
           if( pIdx->aiColumn[j]==iCol ){
-            sqlite3DbFree(db, zErr);
-            zErr = sqlite3MPrintf(db,
-                             "cannot open indexed column for writing");
-            rc = SQLITE_ERROR;
-            (void)sqlite3SafetyOff(db);
-            sqlite3BtreeLeaveAll(db);
-            goto blob_open_out;
+            zFault = "indexed";
           }
         }
       }
+      if( zFault ){
+        sqlite3DbFree(db, zErr);
+        zErr = sqlite3MPrintf(db, "cannot open %s column for writing", zFault);
+        rc = SQLITE_ERROR;
+        (void)sqlite3SafetyOff(db);
+        sqlite3BtreeLeaveAll(db);
+        goto blob_open_out;
+      }
     }
 
     v = sqlite3VdbeCreate(db);