Index: src/fkey.c ================================================================== --- src/fkey.c +++ src/fkey.c @@ -435,11 +435,11 @@ assert( nIncr==1 ); sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY, OE_Abort, 0, P4_STATIC, P5_ConstraintFK); }else{ if( nIncr>0 && pFKey->isDeferred==0 ){ - sqlite3ParseToplevel(pParse)->mayAbort = 1; + sqlite3MayAbort(pParse); } sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); } sqlite3VdbeResolveLabel(v, iOk); @@ -506,10 +506,14 @@ ** This function is called to generate code executed when a row is deleted ** from the parent table of foreign key constraint pFKey and, if pFKey is ** deferred, when a row is inserted into the same table. When generating ** code for an SQL UPDATE operation, this function may be called twice - ** once to "delete" the old row and once to "insert" the new row. +** +** Parameter nIncr is passed -1 when inserting a row (as this may decrease +** the number of FK violations in the db) or +1 when deleting one (as this +** may increase the number of FK constraint problems). ** ** 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: ** @@ -623,17 +627,13 @@ 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. */ + ** clause. For each row found, increment either the deferred or immediate + ** foreign key constraint counter. */ pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0); - if( nIncr>0 && pFKey->isDeferred==0 ){ - sqlite3ParseToplevel(pParse)->mayAbort = 1; - } sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); if( pWInfo ){ sqlite3WhereEnd(pWInfo); } @@ -807,10 +807,28 @@ } } } return 0; } + +/* +** Return true if the parser passed as the first argument is being +** used to code a trigger that is really a "SET NULL" action belonging +** to trigger pFKey. +*/ +static int isSetNullAction(Parse *pParse, FKey *pFKey){ + Parse *pTop = sqlite3ParseToplevel(pParse); + if( pTop->pTriggerPrg ){ + Trigger *p = pTop->pTriggerPrg->pTrigger; + if( (p==pFKey->apTrigger[0] && pFKey->aAction[0]==OE_SetNull) + || (p==pFKey->apTrigger[1] && pFKey->aAction[1]==OE_SetNull) + ){ + 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. @@ -860,11 +878,11 @@ Index *pIdx = 0; /* Index on key columns in pTo */ int *aiFree = 0; int *aiCol; int iCol; int i; - int isIgnore = 0; + int bIgnore = 0; if( aChange && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0 && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){ @@ -919,11 +937,11 @@ ** values read from the parent table are NULL. */ if( db->xAuth ){ int rcauth; char *zCol = pTo->aCol[pIdx ? pIdx->aiColumn[i] : pTo->iPKey].zName; rcauth = sqlite3AuthReadCol(pParse, pTo->zName, zCol, iDb); - isIgnore = (rcauth==SQLITE_IGNORE); + bIgnore = (rcauth==SQLITE_IGNORE); } #endif } /* Take a shared-cache advisory read-lock on the parent table. Allocate @@ -934,16 +952,22 @@ if( regOld!=0 ){ /* A row is being removed from the child table. Search for the parent. ** If the parent does not exist, removing the child row resolves an ** outstanding foreign key constraint violation. */ - fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1,isIgnore); + fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1, bIgnore); } - if( regNew!=0 ){ + if( regNew!=0 && !isSetNullAction(pParse, pFKey) ){ /* A row is being added to the child table. If a parent row cannot - ** be found, adding the child row has violated the FK constraint. */ - fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1,isIgnore); + ** be found, adding the child row has violated the FK constraint. + ** + ** If this operation is being performed as part of a trigger program + ** that is actually a "SET NULL" action belonging to this very + ** foreign key, then omit this scan altogether. As all child key + ** values are guaranteed to be NULL, it is not possible for adding + ** this row to cause an FK violation. */ + fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1, bIgnore); } sqlite3DbFree(db, aiFree); } @@ -960,12 +984,12 @@ if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs) && !pParse->pToplevel && !pParse->isMultiWrite ){ assert( regOld==0 && regNew!=0 ); - /* Inserting a single row into a parent table cannot cause an immediate - ** foreign key violation. So do nothing in this case. */ + /* Inserting a single row into a parent table cannot cause (or fix) + ** an immediate foreign key violation. So do nothing in this case. */ continue; } if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){ if( !isIgnoreErrors || db->mallocFailed ) return; @@ -985,17 +1009,32 @@ if( regNew!=0 ){ fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1); } if( regOld!=0 ){ - /* If there is a RESTRICT action configured for the current operation - ** on the parent 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. */ + int eAction = pFKey->aAction[aChange!=0]; fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1); + /* If this is a deferred FK constraint, or a CASCADE or SET NULL + ** action applies, then any foreign key violations caused by + ** removing the parent key will be rectified by the action trigger. + ** So do not set the "may-abort" flag in this case. + ** + ** Note 1: If the FK is declared "ON UPDATE CASCADE", then the + ** may-abort flag will eventually be set on this statement anyway + ** (when this function is called as part of processing the UPDATE + ** within the action trigger). + ** + ** Note 2: At first glance it may seem like SQLite could simply omit + ** all OP_FkCounter related scans when either CASCADE or SET NULL + ** applies. The trouble starts if the CASCADE or SET NULL action + ** trigger causes other triggers or action rules attached to the + ** child table to fire. In these cases the fk constraint counters + ** might be set incorrectly if any OP_FkCounter related scans are + ** omitted. */ + if( !pFKey->isDeferred && eAction!=OE_Cascade && eAction!=OE_SetNull ){ + sqlite3MayAbort(pParse); + } } pItem->zName = 0; sqlite3SrcListDelete(db, pSrc); } sqlite3DbFree(db, aiCol); Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -394,36 +394,39 @@ ** ** assert( sqlite3VdbeAssertMayAbort(pParse->pVdbe, pParse->mayAbort) ); */ int sqlite3VdbeAssertMayAbort(Vdbe *v, int mayAbort){ int hasAbort = 0; + int hasFkCounter = 0; Op *pOp; VdbeOpIter sIter; memset(&sIter, 0, sizeof(sIter)); sIter.v = v; while( (pOp = opIterNext(&sIter))!=0 ){ int opcode = pOp->opcode; if( opcode==OP_Destroy || opcode==OP_VUpdate || opcode==OP_VRename -#ifndef SQLITE_OMIT_FOREIGN_KEY - || (opcode==OP_FkCounter && pOp->p1==0 && pOp->p2==1) -#endif || ((opcode==OP_Halt || opcode==OP_HaltIfNull) && ((pOp->p1&0xff)==SQLITE_CONSTRAINT && pOp->p2==OE_Abort)) ){ hasAbort = 1; break; } +#ifndef SQLITE_OMIT_FOREIGN_KEY + if( opcode==OP_FkCounter && pOp->p1==0 && pOp->p2==1 ){ + hasFkCounter = 1; + } +#endif } sqlite3DbFree(v->db, sIter.apSub); /* Return true if hasAbort==mayAbort. Or if a malloc failure occurred. ** If malloc failed, then the while() loop above may not have iterated ** through all opcodes and hasAbort may be set incorrectly. Return ** true for this case to prevent the assert() in the callers frame ** from failing. */ - return ( v->db->mallocFailed || hasAbort==mayAbort ); + return ( v->db->mallocFailed || hasAbort==mayAbort || hasFkCounter ); } #endif /* SQLITE_DEBUG - the sqlite3AssertMayAbort() function */ /* ** Loop through the program looking for P2 values that are negative ADDED test/fkey8.test Index: test/fkey8.test ================================================================== --- /dev/null +++ test/fkey8.test @@ -0,0 +1,106 @@ +# 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 fkey8 + +ifcapable {!foreignkey} { + finish_test + return +} +do_execsql_test 1.0 { PRAGMA foreign_keys = 1; } + + +foreach {tn use_stmt sql schema} { + 1 1 "DELETE FROM p1" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1); + } + + 2.1 0 "DELETE FROM p1" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE); + } + 2.2 0 "DELETE FROM p1" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON DELETE SET NULL); + } + 2.3 1 "DELETE FROM p1" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON DELETE SET DEFAULT); + } + + 3 1 "DELETE FROM p1" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE); + CREATE TRIGGER ct1 AFTER DELETE ON c1 BEGIN + INSERT INTO p1 VALUES('x'); + END; + } + + 4 1 "DELETE FROM p1" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); + CREATE TABLE cc1(d REFERENCES c1); + } + + 5.1 0 "DELETE FROM p1" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); + CREATE TABLE cc1(d REFERENCES c1 ON DELETE CASCADE); + } + 5.2 0 "DELETE FROM p1" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); + CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET NULL); + } + 5.3 1 "DELETE FROM p1" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON DELETE CASCADE, c PRIMARY KEY); + CREATE TABLE cc1(d REFERENCES c1 ON DELETE SET DEFAULT); + } + + 6.1 1 "UPDATE p1 SET a = ?" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c); + } + 6.2 0 "UPDATE OR IGNORE p1 SET a = ?" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON UPDATE SET NULL, c); + } + 6.3 1 "UPDATE OR IGNORE p1 SET a = ?" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b REFERENCES p1 ON UPDATE CASCADE, c); + } + 6.4 1 "UPDATE OR IGNORE p1 SET a = ?" { + CREATE TABLE p1(a PRIMARY KEY); + CREATE TABLE c1(b NOT NULL REFERENCES p1 ON UPDATE SET NULL, c); + } + +} { + drop_all_tables + do_test 1.$tn { + execsql $schema + set stmt [sqlite3_prepare_v2 db $sql -1 dummy] + set ret [uses_stmt_journal $stmt] + sqlite3_finalize $stmt + set ret + } $use_stmt +} + + +finish_test +