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

Changes to src/fkey.c.

    78     78   ** This simplifies the implementation a bit.
    79     79   **
    80     80   ** For the purposes of immediate FK constraints, the OR REPLACE conflict
    81     81   ** resolution is considered to delete rows before the new row is inserted.
    82     82   ** If a delete caused by OR REPLACE violates an FK constraint, an exception
    83     83   ** is thrown, even if the FK constraint would be satisfied after the new 
    84     84   ** row is inserted.
           85  +**
           86  +** Immediate constraints are usually handled similarly. The only difference 
           87  +** is that the counter used is stored as part of each individual statement
           88  +** object (struct Vdbe). If, after the statement has run, its immediate
           89  +** constraint counter is greater than zero, it returns SQLITE_CONSTRAINT
           90  +** and the statement transaction is rolled back. An exception is an INSERT
           91  +** statement that inserts a single row only (no triggers). In this case,
           92  +** instead of using a counter, an exception is thrown immediately if the
           93  +** INSERT violates a foreign key constraint. This is necessary as such
           94  +** an INSERT does not open a statement transaction.
    85     95   **
    86     96   ** TODO: How should dropping a table be handled? How should renaming a 
    87     97   ** table be handled?
    88     98   **
    89     99   **
    90    100   ** Query API Notes
    91    101   ** ---------------
................................................................................
   255    265     }
   256    266   
   257    267     *ppIdx = pIdx;
   258    268     return 0;
   259    269   }
   260    270   
   261    271   /*
   262         -** This function is called when a row is inserted into the child table of 
   263         -** foreign key constraint pFKey and, if pFKey is deferred, when a row is
   264         -** deleted from the child table of pFKey. If an SQL UPDATE is executed on
   265         -** the child table of pFKey, this function is invoked twice for each row
          272  +** This function is called when a row is inserted into or deleted from the 
          273  +** child table of foreign key constraint pFKey. If an SQL UPDATE is executed 
          274  +** on the child table of pFKey, this function is invoked twice for each row
   266    275   ** affected - once to "delete" the old row, and then again to "insert" the
   267    276   ** new row.
   268    277   **
   269    278   ** Each time it is called, this function generates VDBE code to locate the
   270    279   ** row in the parent table that corresponds to the row being inserted into 
   271    280   ** or deleted from the child table. If the parent row can be found, no 
   272    281   ** special action is taken. Otherwise, if the parent row can *not* be
   273    282   ** found in the parent table:
   274    283   **
   275    284   **   Operation | FK type   | Action taken
   276    285   **   --------------------------------------------------------------------------
   277         -**   INSERT      immediate   Throw a "foreign key constraint failed" exception.
          286  +**   INSERT      immediate   Increment the "immediate constraint counter".
          287  +**
          288  +**   DELETE      immediate   Decrement the "immediate constraint counter".
   278    289   **
   279    290   **   INSERT      deferred    Increment the "deferred constraint counter".
   280    291   **
   281    292   **   DELETE      deferred    Decrement the "deferred constraint counter".
   282    293   **
   283         -** This function is never called for a delete on the child table of an
   284         -** immediate foreign key constraint. These operations are identified in
   285         -** the comment at the top of this file (fkey.c) as "I.1" and "D.1".
          294  +** These operations are identified in the comment at the top of this file 
          295  +** (fkey.c) as "I.1" and "D.1".
   286    296   */
   287    297   static void fkLookupParent(
   288    298     Parse *pParse,        /* Parse context */
   289    299     int iDb,              /* Index of database housing pTab */
   290    300     Table *pTab,          /* Parent table of FK pFKey */
   291    301     Index *pIdx,          /* Unique index on parent key columns in pTab */
   292    302     FKey *pFKey,          /* Foreign key constraint */
................................................................................
   374    384   **
   375    385   ** The code generated by this function scans through the rows in the child
   376    386   ** table that correspond to the parent table row being deleted or inserted.
   377    387   ** For each child row found, one of the following actions is taken:
   378    388   **
   379    389   **   Operation | FK type   | Action taken
   380    390   **   --------------------------------------------------------------------------
   381         -**   DELETE      immediate   Throw a "foreign key constraint failed" exception.
          391  +**   DELETE      immediate   Increment the "immediate constraint counter".
          392  +**                           Or, if the ON (UPDATE|DELETE) action is RESTRICT,
          393  +**                           throw a "foreign key constraint failed" exception.
          394  +**
          395  +**   INSERT      immediate   Decrement the "immediate constraint counter".
   382    396   **
   383    397   **   DELETE      deferred    Increment the "deferred constraint counter".
   384    398   **                           Or, if the ON (UPDATE|DELETE) action is RESTRICT,
   385    399   **                           throw a "foreign key constraint failed" exception.
   386    400   **
   387    401   **   INSERT      deferred    Decrement the "deferred constraint counter".
   388    402   **
   389         -** This function is never called for an INSERT operation on the parent table
   390         -** of an immediate foreign key constraint. These operations are identified in
   391         -** the comment at the top of this file (fkey.c) as "I.2" and "D.2".
          403  +** These operations are identified in the comment at the top of this file 
          404  +** (fkey.c) as "I.2" and "D.2".
   392    405   */
   393    406   static void fkScanChildren(
   394    407     Parse *pParse,                  /* Parse context */
   395    408     SrcList *pSrc,                  /* SrcList containing the table to scan */
   396    409     Index *pIdx,                    /* Foreign key index */
   397    410     FKey *pFKey,                    /* Foreign key relationship */
   398    411     int *aiCol,                     /* Map from pIdx cols to child table cols */
................................................................................
   401    414   ){
   402    415     sqlite3 *db = pParse->db;       /* Database handle */
   403    416     int i;                          /* Iterator variable */
   404    417     Expr *pWhere = 0;               /* WHERE clause to scan with */
   405    418     NameContext sNameContext;       /* Context used to resolve WHERE clause */
   406    419     WhereInfo *pWInfo;              /* Context used by sqlite3WhereXXX() */
   407    420   
          421  +  /* Create an Expr object representing an SQL expression like:
          422  +  **
          423  +  **   <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
          424  +  **
          425  +  ** The collation sequence used for the comparison should be that of
          426  +  ** the parent key columns. The affinity of the parent key column should
          427  +  ** be applied to each child key value before the comparison takes place.
          428  +  */
   408    429     for(i=0; i<pFKey->nCol; i++){
   409    430       Expr *pLeft;                  /* Value from parent table row */
   410    431       Expr *pRight;                 /* Column ref to child table */
   411    432       Expr *pEq;                    /* Expression (pLeft = pRight) */
   412    433       int iCol;                     /* Index of column in child table */ 
   413    434       const char *zCol;             /* Name of column in child table */
   414    435   
   415    436       pLeft = sqlite3Expr(db, TK_REGISTER, 0);
   416    437       if( pLeft ){
          438  +      /* Set the collation sequence and affinity of the LHS of each TK_EQ
          439  +      ** expression to the parent key column defaults.  */
   417    440         if( pIdx ){
   418    441           int iCol = pIdx->aiColumn[i];
   419    442           Column *pCol = &pIdx->pTable->aCol[iCol];
   420    443           pLeft->iTable = regData+iCol+1;
   421    444           pLeft->affinity = pCol->affinity;
   422    445           pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl);
   423    446         }else{
................................................................................
   441    464   
   442    465     /* Create VDBE to loop through the entries in pSrc that match the WHERE
   443    466     ** clause. If the constraint is not deferred, throw an exception for
   444    467     ** each row found. Otherwise, for deferred constraints, increment the
   445    468     ** deferred constraint counter by nIncr for each row selected.  */
   446    469     pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0);
   447    470     if( nIncr==0 ){
   448         -    /* A RESTRICT Action. */
          471  +    /* Special case: A RESTRICT Action. Throw an error immediately if one
          472  +    ** of these is encountered.  */
   449    473       sqlite3HaltConstraint(
   450    474         pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
   451    475       );
   452    476     }else{
   453    477       if( nIncr>0 && pFKey->isDeferred==0 ){
   454    478         sqlite3ParseToplevel(pParse)->mayAbort = 1;
   455    479       }

Changes to test/fkey2.test.

   184    184   } {35.0 text 35 integer}
   185    185   do_test fkey2-1.5.2 {
   186    186     catchsql { DELETE FROM i }
   187    187   } {1 {foreign key constraint failed}}
   188    188   
   189    189   # Use a collation sequence on the parent key.
   190    190   drop_all_tables
   191         -do_test fkey2-1.5.1 {
          191  +do_test fkey2-1.6.1 {
   192    192     execsql {
   193    193       CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
   194    194       CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
   195    195       INSERT INTO i VALUES('SQLite');
   196    196       INSERT INTO j VALUES('sqlite');
   197    197     }
   198    198     catchsql { DELETE FROM i }
   199    199   } {1 {foreign key constraint failed}}
          200  +
          201  +# Use the parent key collation even if it is default and the child key
          202  +# has an explicit value.
          203  +drop_all_tables
          204  +do_test fkey2-1.6.2 {
          205  +  execsql {
          206  +    CREATE TABLE i(i TEXT PRIMARY KEY);        -- Colseq is "BINARY"
          207  +    CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
          208  +    INSERT INTO i VALUES('SQLite');
          209  +  }
          210  +  catchsql { INSERT INTO j VALUES('sqlite') }
          211  +} {1 {foreign key constraint failed}}
          212  +do_test fkey2-1.6.3 {
          213  +  execsql {
          214  +    INSERT INTO i VALUES('sqlite');
          215  +    INSERT INTO j VALUES('sqlite');
          216  +    DELETE FROM i WHERE i = 'SQLite';
          217  +  }
          218  +  catchsql { DELETE FROM i WHERE i = 'sqlite' }
          219  +} {1 {foreign key constraint failed}}
   200    220   
   201    221   #-------------------------------------------------------------------------
   202    222   # This section (test cases fkey2-2.*) contains tests to check that the
   203    223   # deferred foreign key constraint logic works.
   204    224   #
   205    225   proc fkey2-2-test {tn nocommit sql {res {}}} {
   206    226     if {$res eq "FKV"} {