/ Check-in [9fd54b0a]
Login

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

Overview
Comment:Fix comments in fkey2.c to reflect the immediate-constraint-counter approach.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9fd54b0aa73ed74c65f7db53cb666752f13263f9
User & Date: dan 2009-09-25 12:00:02
Context
2009-09-25
17:03
Avoid checking if an insert or delete has "fixed" an outstanding FK constraint violation if the constraint counter indicates that the database contains no such violations. check-in: 519144ac user: dan tags: trunk
12:00
Fix comments in fkey2.c to reflect the immediate-constraint-counter approach. check-in: 9fd54b0a user: dan tags: trunk
11:26
Prevent ALTER TABLE from being used to add a column with a REFERENCES clause and a non-NULL default value while foreign key support is enabled. check-in: 353b1b18 user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/fkey.c.

78
79
80
81
82
83
84










85
86
87
88
89
90
91
...
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277


278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
...
374
375
376
377
378
379
380


381


382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
...
401
402
403
404
405
406
407








408
409
410
411
412
413
414
415
416


417
418
419
420
421
422
423
...
441
442
443
444
445
446
447
448

449
450
451
452
453
454
455
** 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
** ---------------
................................................................................
  }

  *ppIdx = pIdx;
  return 0;
}

/*
** This function is called when a row is inserted into the child table of 
** foreign key constraint pFKey and, if pFKey is deferred, when a row is
** deleted from the child table of pFKey. If an SQL UPDATE is executed on
** the child table of pFKey, this function is invoked twice for each row
** affected - once to "delete" the old row, and then again to "insert" the
** new row.
**
** Each time it is called, this function generates VDBE code to locate the
** row in the parent table that corresponds to the row being inserted into 
** or deleted from the child table. If the parent row can be found, no 
** special action is taken. Otherwise, if the parent row can *not* be
** found in the parent table:
**
**   Operation | FK type   | Action taken
**   --------------------------------------------------------------------------
**   INSERT      immediate   Throw a "foreign key constraint failed" exception.


**
**   INSERT      deferred    Increment the "deferred constraint counter".
**
**   DELETE      deferred    Decrement the "deferred constraint counter".
**
** This function is never called for a delete on the child table of an
** immediate foreign key constraint. These operations are identified in
** the comment at the top of this file (fkey.c) as "I.1" and "D.1".
*/
static void fkLookupParent(
  Parse *pParse,        /* Parse context */
  int iDb,              /* Index of database housing pTab */
  Table *pTab,          /* Parent table of FK pFKey */
  Index *pIdx,          /* Unique index on parent key columns in pTab */
  FKey *pFKey,          /* Foreign key constraint */
................................................................................
**
** The code generated by this function scans through the rows in the child
** table that correspond to the parent table row being deleted or inserted.
** For each child row found, one of the following actions is taken:
**
**   Operation | FK type   | Action taken
**   --------------------------------------------------------------------------


**   DELETE      immediate   Throw a "foreign key constraint failed" exception.


**
**   DELETE      deferred    Increment the "deferred constraint counter".
**                           Or, if the ON (UPDATE|DELETE) action is RESTRICT,
**                           throw a "foreign key constraint failed" exception.
**
**   INSERT      deferred    Decrement the "deferred constraint counter".
**
** This function is never called for an INSERT operation on the parent table
** of an immediate foreign key constraint. These operations are identified in
** the comment at the top of this file (fkey.c) as "I.2" and "D.2".
*/
static void fkScanChildren(
  Parse *pParse,                  /* Parse context */
  SrcList *pSrc,                  /* SrcList containing the table to scan */
  Index *pIdx,                    /* Foreign key index */
  FKey *pFKey,                    /* Foreign key relationship */
  int *aiCol,                     /* Map from pIdx cols to child table cols */
................................................................................
){
  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 parent table row */
    Expr *pRight;                 /* Column ref to child table */
    Expr *pEq;                    /* Expression (pLeft = pRight) */
    int iCol;                     /* Index of column in child table */ 
    const char *zCol;             /* Name of column in child table */

    pLeft = sqlite3Expr(db, TK_REGISTER, 0);
    if( pLeft ){


      if( pIdx ){
        int iCol = pIdx->aiColumn[i];
        Column *pCol = &pIdx->pTable->aCol[iCol];
        pLeft->iTable = regData+iCol+1;
        pLeft->affinity = pCol->affinity;
        pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl);
      }else{
................................................................................

  /* 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( nIncr==0 ){
    /* A RESTRICT Action. */

    sqlite3HaltConstraint(
      pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
    );
  }else{
    if( nIncr>0 && pFKey->isDeferred==0 ){
      sqlite3ParseToplevel(pParse)->mayAbort = 1;
    }







>
>
>
>
>
>
>
>
>
>







 







|
<
|
|











|
>
>





<
|
|







 







>
>
|
>
>







<
|
|







 







>
>
>
>
>
>
>
>









>
>







 







|
>







78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
...
265
266
267
268
269
270
271
272

273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293

294
295
296
297
298
299
300
301
302
...
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402

403
404
405
406
407
408
409
410
411
...
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
...
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
** 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.
**
** Immediate constraints are usually handled similarly. The only difference 
** is that the counter used is stored as part of each individual statement
** object (struct Vdbe). If, after the statement has run, its immediate
** constraint counter is greater than zero, it returns SQLITE_CONSTRAINT
** and the statement transaction is rolled back. An exception is an INSERT
** statement that inserts a single row only (no triggers). In this case,
** instead of using a counter, an exception is thrown immediately if the
** INSERT violates a foreign key constraint. This is necessary as such
** an INSERT does not open a statement transaction.
**
** TODO: How should dropping a table be handled? How should renaming a 
** table be handled?
**
**
** Query API Notes
** ---------------
................................................................................
  }

  *ppIdx = pIdx;
  return 0;
}

/*
** This function is called when a row is inserted into or deleted from the 

** child table of foreign key constraint pFKey. If an SQL UPDATE is executed 
** on the child table of pFKey, this function is invoked twice for each row
** affected - once to "delete" the old row, and then again to "insert" the
** new row.
**
** Each time it is called, this function generates VDBE code to locate the
** row in the parent table that corresponds to the row being inserted into 
** or deleted from the child table. If the parent row can be found, no 
** special action is taken. Otherwise, if the parent row can *not* be
** found in the parent table:
**
**   Operation | FK type   | Action taken
**   --------------------------------------------------------------------------
**   INSERT      immediate   Increment the "immediate constraint counter".
**
**   DELETE      immediate   Decrement the "immediate constraint counter".
**
**   INSERT      deferred    Increment the "deferred constraint counter".
**
**   DELETE      deferred    Decrement the "deferred constraint counter".
**

** These operations are identified in the comment at the top of this file 
** (fkey.c) as "I.1" and "D.1".
*/
static void fkLookupParent(
  Parse *pParse,        /* Parse context */
  int iDb,              /* Index of database housing pTab */
  Table *pTab,          /* Parent table of FK pFKey */
  Index *pIdx,          /* Unique index on parent key columns in pTab */
  FKey *pFKey,          /* Foreign key constraint */
................................................................................
**
** The code generated by this function scans through the rows in the child
** table that correspond to the parent table row being deleted or inserted.
** For each child row found, one of the following actions is taken:
**
**   Operation | FK type   | Action taken
**   --------------------------------------------------------------------------
**   DELETE      immediate   Increment the "immediate constraint counter".
**                           Or, if the ON (UPDATE|DELETE) action is RESTRICT,
**                           throw a "foreign key constraint failed" exception.
**
**   INSERT      immediate   Decrement the "immediate constraint counter".
**
**   DELETE      deferred    Increment the "deferred constraint counter".
**                           Or, if the ON (UPDATE|DELETE) action is RESTRICT,
**                           throw a "foreign key constraint failed" exception.
**
**   INSERT      deferred    Decrement the "deferred constraint counter".
**

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

  /* Create an Expr object representing an SQL expression like:
  **
  **   <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
  **
  ** The collation sequence used for the comparison should be that of
  ** the parent key columns. The affinity of the parent key column should
  ** be applied to each child key value before the comparison takes place.
  */
  for(i=0; i<pFKey->nCol; i++){
    Expr *pLeft;                  /* Value from parent table row */
    Expr *pRight;                 /* Column ref to child table */
    Expr *pEq;                    /* Expression (pLeft = pRight) */
    int iCol;                     /* Index of column in child table */ 
    const char *zCol;             /* Name of column in child table */

    pLeft = sqlite3Expr(db, TK_REGISTER, 0);
    if( pLeft ){
      /* Set the collation sequence and affinity of the LHS of each TK_EQ
      ** expression to the parent key column defaults.  */
      if( pIdx ){
        int iCol = pIdx->aiColumn[i];
        Column *pCol = &pIdx->pTable->aCol[iCol];
        pLeft->iTable = regData+iCol+1;
        pLeft->affinity = pCol->affinity;
        pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl);
      }else{
................................................................................

  /* 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( nIncr==0 ){
    /* Special case: A RESTRICT Action. Throw an error immediately if one
    ** of these is encountered.  */
    sqlite3HaltConstraint(
      pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
    );
  }else{
    if( nIncr>0 && pFKey->isDeferred==0 ){
      sqlite3ParseToplevel(pParse)->mayAbort = 1;
    }

Changes to test/fkey2.test.

184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199




















200
201
202
203
204
205
206
} {35.0 text 35 integer}
do_test fkey2-1.5.2 {
  catchsql { DELETE FROM i }
} {1 {foreign key constraint failed}}

# Use a collation sequence on the parent key.
drop_all_tables
do_test fkey2-1.5.1 {
  execsql {
    CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
    CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
    INSERT INTO j VALUES('sqlite');
  }
  catchsql { DELETE FROM i }
} {1 {foreign key constraint failed}}





















#-------------------------------------------------------------------------
# This section (test cases fkey2-2.*) contains tests to check that the
# deferred foreign key constraint logic works.
#
proc fkey2-2-test {tn nocommit sql {res {}}} {
  if {$res eq "FKV"} {







|








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
} {35.0 text 35 integer}
do_test fkey2-1.5.2 {
  catchsql { DELETE FROM i }
} {1 {foreign key constraint failed}}

# Use a collation sequence on the parent key.
drop_all_tables
do_test fkey2-1.6.1 {
  execsql {
    CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
    CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
    INSERT INTO j VALUES('sqlite');
  }
  catchsql { DELETE FROM i }
} {1 {foreign key constraint failed}}

# Use the parent key collation even if it is default and the child key
# has an explicit value.
drop_all_tables
do_test fkey2-1.6.2 {
  execsql {
    CREATE TABLE i(i TEXT PRIMARY KEY);        -- Colseq is "BINARY"
    CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
    INSERT INTO i VALUES('SQLite');
  }
  catchsql { INSERT INTO j VALUES('sqlite') }
} {1 {foreign key constraint failed}}
do_test fkey2-1.6.3 {
  execsql {
    INSERT INTO i VALUES('sqlite');
    INSERT INTO j VALUES('sqlite');
    DELETE FROM i WHERE i = 'SQLite';
  }
  catchsql { DELETE FROM i WHERE i = 'sqlite' }
} {1 {foreign key constraint failed}}

#-------------------------------------------------------------------------
# This section (test cases fkey2-2.*) contains tests to check that the
# deferred foreign key constraint logic works.
#
proc fkey2-2-test {tn nocommit sql {res {}}} {
  if {$res eq "FKV"} {