Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Changes to src/delete.c.

   532    532       ** being deleted. Do not attempt to delete the row a second time, and 
   533    533       ** do not fire AFTER triggers.  */
   534    534       sqlite3VdbeAddOp3(v, OP_NotExists, iCur, iLabel, iRowid);
   535    535   
   536    536       /* Do FK processing. This call checks that any FK constraints that
   537    537       ** refer to this table (i.e. constraints attached to other tables) 
   538    538       ** are not violated by deleting this row.  */
   539         -    sqlite3FkCheck(pParse, pTab, iOld, 0);
          539  +    sqlite3FkCheck(pParse, pTab, iOld, 0, 0, 0);
   540    540     }
   541    541   
   542    542     /* Delete the index and table entries. Skip this step if pTab is really
   543    543     ** a view (in which case the only effect of the DELETE statement is to
   544    544     ** fire the INSTEAD OF triggers).  */ 
   545    545     if( pTab->pSelect==0 ){
   546    546       sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, 0);
................................................................................
   549    549         sqlite3VdbeChangeP4(v, -1, pTab->zName, P4_TRANSIENT);
   550    550       }
   551    551     }
   552    552   
   553    553     /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
   554    554     ** handle rows (possibly in other tables) that refer via a foreign key
   555    555     ** to the row just deleted. */ 
   556         -  sqlite3FkActions(pParse, pTab, 0, iOld);
          556  +  sqlite3FkActions(pParse, pTab, 0, iOld, 0, 0);
   557    557   
   558    558     /* Invoke AFTER DELETE trigger programs. */
   559    559     sqlite3CodeRowTrigger(pParse, pTrigger, 
   560    560         TK_DELETE, 0, TRIGGER_AFTER, pTab, iOld, onconf, iLabel
   561    561     );
   562    562   
   563    563     /* Jump here if the row had already been deleted before any BEFORE

Changes to src/fkey.c.

   678    678   
   679    679       if( iSkip ){
   680    680         sqlite3VdbeResolveLabel(v, iSkip);
   681    681       }
   682    682     }
   683    683   }
   684    684   
          685  +
          686  +/*
          687  +** The second argument points to an FKey object representing a foreign key
          688  +** for which pTab is the child table. An UPDATE statement against pTab
          689  +** is currently being processed. For each column of the table that is 
          690  +** actually updated, the corresponding element in the aChange[] array
          691  +** is zero or greater (if a column is unmodified the corresponding element
          692  +** is set to -1). If the rowid column is modified by the UPDATE statement
          693  +** the bChngRowid argument is non-zero.
          694  +**
          695  +** This function returns true if any of the columns that are part of the
          696  +** child key for FK constraint *p are modified.
          697  +*/
          698  +static int fkChildIsModified(
          699  +  Table *pTab,                    /* Table being updated */
          700  +  FKey *p,                        /* Foreign key for which pTab is the child */
          701  +  int *aChange,                   /* Array indicating modified columns */
          702  +  int bChngRowid                  /* True if rowid is modified by this update */
          703  +){
          704  +  int i;
          705  +  for(i=0; i<p->nCol; i++){
          706  +    int iChildKey = p->aCol[i].iFrom;
          707  +    if( aChange[iChildKey]>=0 ) return 1;
          708  +    if( iChildKey==pTab->iPKey && bChngRowid ) return 1;
          709  +  }
          710  +  return 0;
          711  +}
          712  +
          713  +/*
          714  +** The second argument points to an FKey object representing a foreign key
          715  +** for which pTab is the parent table. An UPDATE statement against pTab
          716  +** is currently being processed. For each column of the table that is 
          717  +** actually updated, the corresponding element in the aChange[] array
          718  +** is zero or greater (if a column is unmodified the corresponding element
          719  +** is set to -1). If the rowid column is modified by the UPDATE statement
          720  +** the bChngRowid argument is non-zero.
          721  +**
          722  +** This function returns true if any of the columns that are part of the
          723  +** parent key for FK constraint *p are modified.
          724  +*/
          725  +static int fkParentIsModified(
          726  +  Table *pTab, 
          727  +  FKey *p, 
          728  +  int *aChange, 
          729  +  int bChngRowid
          730  +){
          731  +  int i;
          732  +  for(i=0; i<p->nCol; i++){
          733  +    char *zKey = p->aCol[i].zCol;
          734  +    int iKey;
          735  +    for(iKey=0; iKey<pTab->nCol; iKey++){
          736  +      if( aChange[iKey]>=0 || (iKey==pTab->iPKey && bChngRowid) ){
          737  +        Column *pCol = &pTab->aCol[iKey];
          738  +        if( zKey ){
          739  +          if( 0==sqlite3StrICmp(pCol->zName, zKey) ) return 1;
          740  +        }else if( pCol->colFlags & COLFLAG_PRIMKEY ){
          741  +          return 1;
          742  +        }
          743  +      }
          744  +    }
          745  +  }
          746  +  return 0;
          747  +}
          748  +
   685    749   /*
   686    750   ** This function is called when inserting, deleting or updating a row of
   687    751   ** table pTab to generate VDBE code to perform foreign key constraint 
   688    752   ** processing for the operation.
   689    753   **
   690    754   ** For a DELETE operation, parameter regOld is passed the index of the
   691    755   ** first register in an array of (pTab->nCol+1) registers containing the
................................................................................
   702    766   ** described for DELETE. Then again after the original record is deleted
   703    767   ** but before the new record is inserted using the INSERT convention. 
   704    768   */
   705    769   void sqlite3FkCheck(
   706    770     Parse *pParse,                  /* Parse context */
   707    771     Table *pTab,                    /* Row is being deleted from this table */ 
   708    772     int regOld,                     /* Previous row data is stored here */
   709         -  int regNew                      /* New row data is stored here */
          773  +  int regNew,                     /* New row data is stored here */
          774  +  int *aChange,                   /* Array indicating UPDATEd columns (or 0) */
          775  +  int bChngRowid                  /* True if rowid is UPDATEd */
   710    776   ){
   711    777     sqlite3 *db = pParse->db;       /* Database handle */
   712    778     FKey *pFKey;                    /* Used to iterate through FKs */
   713    779     int iDb;                        /* Index of database containing pTab */
   714    780     const char *zDb;                /* Name of database containing pTab */
   715    781     int isIgnoreErrors = pParse->disableTriggers;
   716    782   
................................................................................
   729    795       Table *pTo;                   /* Parent table of foreign key pFKey */
   730    796       Index *pIdx = 0;              /* Index on key columns in pTo */
   731    797       int *aiFree = 0;
   732    798       int *aiCol;
   733    799       int iCol;
   734    800       int i;
   735    801       int isIgnore = 0;
          802  +
          803  +    if( aChange 
          804  +     && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0
          805  +     && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0 
          806  +    ){
          807  +      continue;
          808  +    }
   736    809   
   737    810       /* Find the parent table of this foreign key. Also find a unique index 
   738    811       ** on the parent key columns in the parent table. If either of these 
   739    812       ** schema items cannot be located, set an error in pParse and return 
   740    813       ** early.  */
   741    814       if( pParse->disableTriggers ){
   742    815         pTo = sqlite3FindTable(db, pFKey->zTo, zDb);
................................................................................
   811    884     }
   812    885   
   813    886     /* Loop through all the foreign key constraints that refer to this table */
   814    887     for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
   815    888       Index *pIdx = 0;              /* Foreign key index for pFKey */
   816    889       SrcList *pSrc;
   817    890       int *aiCol = 0;
          891  +
          892  +    if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){
          893  +      continue;
          894  +    }
   818    895   
   819    896       if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs) 
   820    897        && !pParse->pToplevel && !pParse->isMultiWrite 
   821    898       ){
   822    899         assert( regOld==0 && regNew!=0 );
   823    900         /* Inserting a single row into a parent table cannot cause an immediate
   824    901         ** foreign key violation. So do nothing in this case.  */
................................................................................
   884    961         if( pIdx ){
   885    962           for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
   886    963         }
   887    964       }
   888    965     }
   889    966     return mask;
   890    967   }
          968  +
   891    969   
   892    970   /*
   893    971   ** This function is called before generating code to update or delete a 
   894    972   ** row contained in table pTab. If the operation is a DELETE, then
   895    973   ** parameter aChange is passed a NULL value. For an UPDATE, aChange points
   896    974   ** to an array of size N, where N is the number of columns in table pTab.
   897    975   ** If the i'th column is not modified by the UPDATE, then the corresponding 
................................................................................
   914    992         /* A DELETE operation. Foreign key processing is required if the 
   915    993         ** table in question is either the child or parent table for any 
   916    994         ** foreign key constraint.  */
   917    995         return (sqlite3FkReferences(pTab) || pTab->pFKey);
   918    996       }else{
   919    997         /* This is an UPDATE. Foreign key processing is only required if the
   920    998         ** operation modifies one or more child or parent key columns. */
   921         -      int i;
   922    999         FKey *p;
   923   1000   
   924   1001         /* Check if any child key columns are being modified. */
   925   1002         for(p=pTab->pFKey; p; p=p->pNextFrom){
   926         -        for(i=0; i<p->nCol; i++){
   927         -          int iChildKey = p->aCol[i].iFrom;
   928         -          if( aChange[iChildKey]>=0 ) return 1;
   929         -          if( iChildKey==pTab->iPKey && chngRowid ) return 1;
   930         -        }
         1003  +        if( fkChildIsModified(pTab, p, aChange, chngRowid) ) return 1;
   931   1004         }
   932   1005   
   933   1006         /* Check if any parent key columns are being modified. */
   934   1007         for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
   935         -        for(i=0; i<p->nCol; i++){
   936         -          char *zKey = p->aCol[i].zCol;
   937         -          int iKey;
   938         -          for(iKey=0; iKey<pTab->nCol; iKey++){
   939         -            Column *pCol = &pTab->aCol[iKey];
   940         -            if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey)
   941         -                      : (pCol->colFlags & COLFLAG_PRIMKEY)!=0) ){
   942         -              if( aChange[iKey]>=0 ) return 1;
   943         -              if( iKey==pTab->iPKey && chngRowid ) return 1;
   944         -            }
   945         -          }
   946         -        }
         1008  +        if( fkParentIsModified(pTab, p, aChange, chngRowid) ) return 1;
   947   1009         }
   948   1010       }
   949   1011     }
   950   1012     return 0;
   951   1013   }
   952   1014   
   953   1015   /*
................................................................................
  1165   1227   ** This function is called when deleting or updating a row to implement
  1166   1228   ** any required CASCADE, SET NULL or SET DEFAULT actions.
  1167   1229   */
  1168   1230   void sqlite3FkActions(
  1169   1231     Parse *pParse,                  /* Parse context */
  1170   1232     Table *pTab,                    /* Table being updated or deleted from */
  1171   1233     ExprList *pChanges,             /* Change-list for UPDATE, NULL for DELETE */
  1172         -  int regOld                      /* Address of array containing old row */
         1234  +  int regOld,                     /* Address of array containing old row */
         1235  +  int *aChange,                   /* Array indicating UPDATEd columns (or 0) */
         1236  +  int bChngRowid                  /* True if rowid is UPDATEd */
  1173   1237   ){
  1174   1238     /* If foreign-key support is enabled, iterate through all FKs that 
  1175   1239     ** refer to table pTab. If there is an action associated with the FK 
  1176   1240     ** for this operation (either update or delete), invoke the associated 
  1177   1241     ** trigger sub-program.  */
  1178   1242     if( pParse->db->flags&SQLITE_ForeignKeys ){
  1179   1243       FKey *pFKey;                  /* Iterator variable */
  1180   1244       for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
  1181         -      Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges);
  1182         -      if( pAction ){
  1183         -        sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0);
         1245  +      if( aChange==0 || fkParentIsModified(pTab, pFKey, aChange, bChngRowid) ){
         1246  +        Trigger *pAct = fkActionTrigger(pParse, pTab, pFKey, pChanges);
         1247  +        if( pAct ){
         1248  +          sqlite3CodeRowTriggerDirect(pParse, pAct, pTab, regOld, OE_Abort, 0);
         1249  +        }
  1184   1250         }
  1185   1251       }
  1186   1252     }
  1187   1253   }
  1188   1254   
  1189   1255   #endif /* ifndef SQLITE_OMIT_TRIGGER */
  1190   1256   

Changes to src/insert.c.

  1027   1027       }else
  1028   1028   #endif
  1029   1029       {
  1030   1030         int isReplace;    /* Set to true if constraints may cause a replace */
  1031   1031         sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx,
  1032   1032             keyColumn>=0, 0, onError, endOfLoop, &isReplace
  1033   1033         );
  1034         -      sqlite3FkCheck(pParse, pTab, 0, regIns);
         1034  +      sqlite3FkCheck(pParse, pTab, 0, regIns, 0, 0);
  1035   1035         sqlite3CompleteInsertion(
  1036   1036             pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0
  1037   1037         );
  1038   1038       }
  1039   1039     }
  1040   1040   
  1041   1041     /* Update the count of rows that are inserted

Changes to src/sqliteInt.h.

  3205   3205   ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
  3206   3206   ** key functionality is available. If OMIT_TRIGGER is defined but
  3207   3207   ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
  3208   3208   ** this case foreign keys are parsed, but no other functionality is 
  3209   3209   ** provided (enforcement of FK constraints requires the triggers sub-system).
  3210   3210   */
  3211   3211   #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER)
  3212         -  void sqlite3FkCheck(Parse*, Table*, int, int);
         3212  +  void sqlite3FkCheck(Parse*, Table*, int, int, int*, int);
  3213   3213     void sqlite3FkDropTable(Parse*, SrcList *, Table*);
  3214         -  void sqlite3FkActions(Parse*, Table*, ExprList*, int);
         3214  +  void sqlite3FkActions(Parse*, Table*, ExprList*, int, int*, int);
  3215   3215     int sqlite3FkRequired(Parse*, Table*, int*, int);
  3216   3216     u32 sqlite3FkOldmask(Parse*, Table*);
  3217   3217     FKey *sqlite3FkReferences(Table *);
  3218   3218   #else
  3219         -  #define sqlite3FkActions(a,b,c,d)
         3219  +  #define sqlite3FkActions(a,b,c,d,e,f)
  3220   3220     #define sqlite3FkCheck(a,b,c,d)
  3221   3221     #define sqlite3FkDropTable(a,b,c)
  3222   3222     #define sqlite3FkOldmask(a,b)      0
  3223         -  #define sqlite3FkRequired(a,b,c,d) 0
         3223  +  #define sqlite3FkRequired(a,b,c,d,e,f) 0
  3224   3224   #endif
  3225   3225   #ifndef SQLITE_OMIT_FOREIGN_KEY
  3226   3226     void sqlite3FkDelete(sqlite3 *, Table*);
  3227   3227     int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**);
  3228   3228   #else
  3229   3229     #define sqlite3FkDelete(a,b)
  3230   3230     #define sqlite3FkLocateIndex(a,b,c,d,e)

Changes to src/update.c.

   484    484   
   485    485       /* Do constraint checks. */
   486    486       sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid,
   487    487           aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0);
   488    488   
   489    489       /* Do FK constraint checks. */
   490    490       if( hasFK ){
   491         -      sqlite3FkCheck(pParse, pTab, regOldRowid, 0);
          491  +      sqlite3FkCheck(pParse, pTab, regOldRowid, 0, aXRef, chngRowid);
   492    492       }
   493    493   
   494    494       /* Delete the index entries associated with the current record.  */
   495    495       j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
   496    496       sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
   497    497     
   498    498       /* If changing the record number, delete the old record.  */
   499    499       if( hasFK || chngRowid ){
   500    500         sqlite3VdbeAddOp2(v, OP_Delete, iCur, 0);
   501    501       }
   502    502       sqlite3VdbeJumpHere(v, j1);
   503    503   
   504    504       if( hasFK ){
   505         -      sqlite3FkCheck(pParse, pTab, 0, regNewRowid);
          505  +      sqlite3FkCheck(pParse, pTab, 0, regNewRowid, aXRef, chngRowid);
   506    506       }
   507    507     
   508    508       /* Insert the new index entries and the new record. */
   509    509       sqlite3CompleteInsertion(pParse, pTab, iCur, regNewRowid, aRegIdx, 1, 0, 0);
   510    510   
   511    511       /* Do any ON CASCADE, SET NULL or SET DEFAULT operations required to
   512    512       ** handle rows (possibly in other tables) that refer via a foreign key
   513    513       ** to the row just updated. */ 
   514    514       if( hasFK ){
   515         -      sqlite3FkActions(pParse, pTab, pChanges, regOldRowid);
          515  +      sqlite3FkActions(pParse, pTab, pChanges, regOldRowid, aXRef, chngRowid);
   516    516       }
   517    517     }
   518    518   
   519    519     /* Increment the row counter 
   520    520     */
   521    521     if( (db->flags & SQLITE_CountRows) && !pParse->pTriggerTab){
   522    522       sqlite3VdbeAddOp2(v, OP_AddImm, regRowCount, 1);

Added test/fkey7.test.

            1  +# 2001 September 15
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +# This file implements tests for foreign keys.
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set testprefix fkey7
           19  +
           20  +ifcapable {!foreignkey} {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +do_execsql_test 1.1 {
           26  +  PRAGMA foreign_keys = 1;
           27  +
           28  +  CREATE TABLE s1(a PRIMARY KEY, b);
           29  +  CREATE TABLE par(a, b REFERENCES s1, c UNIQUE, PRIMARY KEY(a));
           30  +
           31  +  CREATE TABLE c1(a, b REFERENCES par);
           32  +  CREATE TABLE c2(a, b REFERENCES par);
           33  +  CREATE TABLE c3(a, b REFERENCES par(c));
           34  +}
           35  +
           36  +proc auth {op tbl args} {
           37  +  if {$op == "SQLITE_READ"} { set ::tbls($tbl) 1 }
           38  +  return "SQLITE_OK"
           39  +}
           40  +db auth auth
           41  +db cache size 0
           42  +proc do_tblsread_test {tn sql tbllist} {
           43  +  array unset ::tbls
           44  +  uplevel [list execsql $sql]
           45  +  uplevel [list do_test $tn {lsort [array names ::tbls]} $tbllist]
           46  +}
           47  +
           48  +do_tblsread_test 1.2 { UPDATE par SET b=? WHERE a=? } {par s1}
           49  +do_tblsread_test 1.3 { UPDATE par SET a=? WHERE b=? } {c1 c2 par}
           50  +do_tblsread_test 1.4 { UPDATE par SET c=? WHERE b=? } {c3 par}
           51  +do_tblsread_test 1.5 { UPDATE par SET a=?,b=?,c=? WHERE b=? } {c1 c2 c3 par s1}
           52  +
           53  +
           54  +finish_test

Changes to test/permutations.test.

   505    505   test_suite "utf16" -description {
   506    506     Run tests using UTF-16 databases
   507    507   } -presql {
   508    508     pragma encoding = 'UTF-16'
   509    509   } -files {
   510    510       alter.test alter3.test
   511    511       analyze.test analyze3.test analyze4.test analyze5.test analyze6.test
   512         -    analyze7.test analyze8.test analyze9.test analyzeA.test
          512  +    analyze7.test analyze8.test analyze9.test analyzeA.test analyzeB.test
   513    513       auth.test bind.test blob.test capi2.test capi3.test collate1.test
   514    514       collate2.test collate3.test collate4.test collate5.test collate6.test
   515    515       conflict.test date.test delete.test expr.test fkey1.test func.test
   516    516       hook.test index.test insert2.test insert.test interrupt.test in.test
   517    517       intpkey.test ioerr.test join2.test join.test lastinsert.test
   518    518       laststmtchanges.test limit.test lock2.test lock.test main.test 
   519    519       memdb.test minmax.test misc1.test misc2.test misc3.test notnull.test