Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -534,11 +534,11 @@ 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, iOld, 0); + sqlite3FkCheck(pParse, pTab, iOld, 0, 0, 0); } /* Delete the index and table entries. Skip this step if pTab is really ** a view (in which case the only effect of the DELETE statement is to ** fire the INSTEAD OF triggers). */ @@ -551,11 +551,11 @@ } /* 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); + sqlite3FkActions(pParse, pTab, 0, iOld, 0, 0); /* Invoke AFTER DELETE trigger programs. */ sqlite3CodeRowTrigger(pParse, pTrigger, TK_DELETE, 0, TRIGGER_AFTER, pTab, iOld, onconf, iLabel ); Index: src/fkey.c ================================================================== --- src/fkey.c +++ src/fkey.c @@ -680,10 +680,74 @@ sqlite3VdbeResolveLabel(v, iSkip); } } } + +/* +** The second argument points to an FKey object representing a foreign key +** for which pTab is the child table. An UPDATE statement against pTab +** is currently being processed. For each column of the table that is +** actually updated, the corresponding element in the aChange[] array +** is zero or greater (if a column is unmodified the corresponding element +** is set to -1). If the rowid column is modified by the UPDATE statement +** the bChngRowid argument is non-zero. +** +** This function returns true if any of the columns that are part of the +** child key for FK constraint *p are modified. +*/ +static int fkChildIsModified( + Table *pTab, /* Table being updated */ + FKey *p, /* Foreign key for which pTab is the child */ + int *aChange, /* Array indicating modified columns */ + int bChngRowid /* True if rowid is modified by this update */ +){ + int i; + for(i=0; inCol; i++){ + int iChildKey = p->aCol[i].iFrom; + if( aChange[iChildKey]>=0 ) return 1; + if( iChildKey==pTab->iPKey && bChngRowid ) return 1; + } + return 0; +} + +/* +** The second argument points to an FKey object representing a foreign key +** for which pTab is the parent table. An UPDATE statement against pTab +** is currently being processed. For each column of the table that is +** actually updated, the corresponding element in the aChange[] array +** is zero or greater (if a column is unmodified the corresponding element +** is set to -1). If the rowid column is modified by the UPDATE statement +** the bChngRowid argument is non-zero. +** +** This function returns true if any of the columns that are part of the +** parent key for FK constraint *p are modified. +*/ +static int fkParentIsModified( + Table *pTab, + FKey *p, + int *aChange, + int bChngRowid +){ + int i; + for(i=0; inCol; i++){ + char *zKey = p->aCol[i].zCol; + int iKey; + for(iKey=0; iKeynCol; iKey++){ + if( aChange[iKey]>=0 || (iKey==pTab->iPKey && bChngRowid) ){ + Column *pCol = &pTab->aCol[iKey]; + if( zKey ){ + if( 0==sqlite3StrICmp(pCol->zName, zKey) ) return 1; + }else if( pCol->colFlags & COLFLAG_PRIMKEY ){ + return 1; + } + } + } + } + return 0; +} + /* ** 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. ** @@ -704,11 +768,13 @@ */ void sqlite3FkCheck( Parse *pParse, /* Parse context */ Table *pTab, /* Row is being deleted from this table */ int regOld, /* Previous row data is stored here */ - int regNew /* New row data is stored here */ + int regNew, /* New row data is stored here */ + int *aChange, /* Array indicating UPDATEd columns (or 0) */ + int bChngRowid /* True if rowid is UPDATEd */ ){ sqlite3 *db = pParse->db; /* Database handle */ FKey *pFKey; /* Used to iterate through FKs */ int iDb; /* Index of database containing pTab */ const char *zDb; /* Name of database containing pTab */ @@ -731,10 +797,17 @@ int *aiFree = 0; int *aiCol; int iCol; int i; int isIgnore = 0; + + if( aChange + && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0 + && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0 + ){ + continue; + } /* Find the parent table of this foreign key. Also find a unique index ** on the parent key columns in the parent table. If either of these ** schema items cannot be located, set an error in pParse and return ** early. */ @@ -813,10 +886,14 @@ /* Loop through all the foreign key constraints that refer to this table */ for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ Index *pIdx = 0; /* Foreign key index for pFKey */ SrcList *pSrc; int *aiCol = 0; + + if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){ + continue; + } if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs) && !pParse->pToplevel && !pParse->isMultiWrite ){ assert( regOld==0 && regNew!=0 ); @@ -886,10 +963,11 @@ } } } return mask; } + /* ** This function is called before generating code to update or delete a ** row contained in table pTab. If the operation is a DELETE, then ** parameter aChange is passed a NULL value. For an UPDATE, aChange points @@ -916,36 +994,20 @@ ** foreign key constraint. */ return (sqlite3FkReferences(pTab) || pTab->pFKey); }else{ /* This is an UPDATE. Foreign key processing is only required if the ** operation modifies one or more child or parent key columns. */ - int i; FKey *p; /* Check if any child key columns are being modified. */ for(p=pTab->pFKey; p; p=p->pNextFrom){ - for(i=0; inCol; i++){ - int iChildKey = p->aCol[i].iFrom; - if( aChange[iChildKey]>=0 ) return 1; - if( iChildKey==pTab->iPKey && chngRowid ) return 1; - } + if( fkChildIsModified(pTab, p, aChange, chngRowid) ) return 1; } /* Check if any parent key columns are being modified. */ for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ - for(i=0; inCol; i++){ - char *zKey = p->aCol[i].zCol; - int iKey; - for(iKey=0; iKeynCol; iKey++){ - Column *pCol = &pTab->aCol[iKey]; - if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey) - : (pCol->colFlags & COLFLAG_PRIMKEY)!=0) ){ - if( aChange[iKey]>=0 ) return 1; - if( iKey==pTab->iPKey && chngRowid ) return 1; - } - } - } + if( fkParentIsModified(pTab, p, aChange, chngRowid) ) return 1; } } } return 0; } @@ -1167,22 +1229,26 @@ */ 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 */ + int regOld, /* Address of array containing old row */ + int *aChange, /* Array indicating UPDATEd columns (or 0) */ + int bChngRowid /* True if rowid is UPDATEd */ ){ /* 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 = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ - Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges); - if( pAction ){ - sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0); + if( aChange==0 || fkParentIsModified(pTab, pFKey, aChange, bChngRowid) ){ + Trigger *pAct = fkActionTrigger(pParse, pTab, pFKey, pChanges); + if( pAct ){ + sqlite3CodeRowTriggerDirect(pParse, pAct, pTab, regOld, OE_Abort, 0); + } } } } } Index: src/insert.c ================================================================== --- src/insert.c +++ src/insert.c @@ -1029,11 +1029,11 @@ { int isReplace; /* Set to true if constraints may cause a replace */ sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx, keyColumn>=0, 0, onError, endOfLoop, &isReplace ); - sqlite3FkCheck(pParse, pTab, 0, regIns); + sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0); sqlite3CompleteInsertion( pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0 ); } } Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -3207,22 +3207,22 @@ ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In ** this case foreign keys are parsed, but no other functionality is ** provided (enforcement of FK constraints requires the triggers sub-system). */ #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) - void sqlite3FkCheck(Parse*, Table*, int, int); + void sqlite3FkCheck(Parse*, Table*, int, int, int*, int); void sqlite3FkDropTable(Parse*, SrcList *, Table*); - void sqlite3FkActions(Parse*, Table*, ExprList*, int); + void sqlite3FkActions(Parse*, Table*, ExprList*, int, int*, int); int sqlite3FkRequired(Parse*, Table*, int*, int); u32 sqlite3FkOldmask(Parse*, Table*); FKey *sqlite3FkReferences(Table *); #else - #define sqlite3FkActions(a,b,c,d) + #define sqlite3FkActions(a,b,c,d,e,f) #define sqlite3FkCheck(a,b,c,d) #define sqlite3FkDropTable(a,b,c) - #define sqlite3FkOldmask(a,b) 0 - #define sqlite3FkRequired(a,b,c,d) 0 + #define sqlite3FkOldmask(a,b) 0 + #define sqlite3FkRequired(a,b,c,d,e,f) 0 #endif #ifndef SQLITE_OMIT_FOREIGN_KEY void sqlite3FkDelete(sqlite3 *, Table*); int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**); #else Index: src/update.c ================================================================== --- src/update.c +++ src/update.c @@ -486,11 +486,11 @@ sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid, aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0); /* Do FK constraint checks. */ if( hasFK ){ - sqlite3FkCheck(pParse, pTab, regOldRowid, 0); + sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngRowid); } /* Delete the index entries associated with the current record. */ j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid); sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx); @@ -500,21 +500,21 @@ sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0); } sqlite3VdbeJumpHere(v, j1); if( hasFK ){ - sqlite3FkCheck(pParse, pTab, 0, regNewRowid); + sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngRowid); } /* 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. */ if( hasFK ){ - sqlite3FkActions(pParse, pTab, pChanges, regOldRowid); + sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngRowid); } } /* Increment the row counter */ ADDED test/fkey7.test Index: test/fkey7.test ================================================================== --- test/fkey7.test +++ test/fkey7.test @@ -0,0 +1,54 @@ +# 2001 September 15 +# +# 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 implements regression tests for SQLite library. +# +# This file implements tests for foreign keys. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix fkey7 + +ifcapable {!foreignkey} { + finish_test + return +} + +do_execsql_test 1.1 { + PRAGMA foreign_keys = 1; + + CREATE TABLE s1(a PRIMARY KEY, b); + CREATE TABLE par(a, b REFERENCES s1, c UNIQUE, PRIMARY KEY(a)); + + CREATE TABLE c1(a, b REFERENCES par); + CREATE TABLE c2(a, b REFERENCES par); + CREATE TABLE c3(a, b REFERENCES par(c)); +} + +proc auth {op tbl args} { + if {$op == "SQLITE_READ"} { set ::tbls($tbl) 1 } + return "SQLITE_OK" +} +db auth auth +db cache size 0 +proc do_tblsread_test {tn sql tbllist} { + array unset ::tbls + uplevel [list execsql $sql] + uplevel [list do_test $tn {lsort [array names ::tbls]} $tbllist] +} + +do_tblsread_test 1.2 { UPDATE par SET b=? WHERE a=? } {par s1} +do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par} +do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par} +do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1} + + +finish_test Index: test/permutations.test ================================================================== --- test/permutations.test +++ test/permutations.test @@ -507,11 +507,11 @@ } -presql { pragma encoding = 'UTF-16' } -files { alter.test alter3.test analyze.test analyze3.test analyze4.test analyze5.test analyze6.test - analyze7.test analyze8.test analyze9.test analyzeA.test + analyze7.test analyze8.test analyze9.test analyzeA.test analyzeB.test auth.test bind.test blob.test capi2.test capi3.test collate1.test collate2.test collate3.test collate4.test collate5.test collate6.test conflict.test date.test delete.test expr.test fkey1.test func.test hook.test index.test insert2.test insert.test interrupt.test in.test intpkey.test ioerr.test join2.test join.test lastinsert.test