Check in implementation of foreign key constraints.
FossilOrigin-Name: d5d399811876391642937edeb9e8434dd9e356f5
diff --git a/src/build.c b/src/build.c
index c23d92f..0fbe07e 100644
--- a/src/build.c
+++ b/src/build.c
@@ -504,7 +504,6 @@
*/
void sqlite3DeleteTable(Table *pTable){
Index *pIndex, *pNext;
- FKey *pFKey, *pNextFKey;
sqlite3 *db;
if( pTable==0 ) return;
@@ -526,13 +525,8 @@
sqlite3DeleteIndex(pIndex);
}
-#ifndef SQLITE_OMIT_FOREIGN_KEY
- /* Delete all foreign keys associated with this table. */
- for(pFKey=pTable->pFKey; pFKey; pFKey=pNextFKey){
- pNextFKey = pFKey->pNextFrom;
- sqlite3DbFree(db, pFKey);
- }
-#endif
+ /* Delete any foreign keys attached to this table. */
+ sqlite3FkDelete(pTable);
/* Delete the Table structure itself.
*/
@@ -1174,7 +1168,11 @@
"INTEGER PRIMARY KEY");
#endif
}else{
- sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, sortOrder, 0);
+ Index *p;
+ p = sqlite3CreateIndex(pParse, 0, 0, 0, pList, onError, 0, 0, sortOrder, 0);
+ if( p ){
+ p->autoIndex = 2;
+ }
pList = 0;
}
@@ -2153,6 +2151,7 @@
sqlite3 *db = pParse->db;
#ifndef SQLITE_OMIT_FOREIGN_KEY
FKey *pFKey = 0;
+ FKey *pNextTo;
Table *p = pParse->pNewTable;
int nByte;
int i;
@@ -2231,6 +2230,16 @@
pFKey->updateConf = (u8)((flags >> 8 ) & 0xff);
pFKey->insertConf = (u8)((flags >> 16 ) & 0xff);
+ pNextTo = (FKey *)sqlite3HashInsert(&p->pSchema->fkeyHash,
+ pFKey->zTo, sqlite3Strlen30(pFKey->zTo), (void *)pFKey
+ );
+ if( pNextTo==pFKey ) goto fk_end;
+ if( pNextTo ){
+ assert( pNextTo->pPrevTo==0 );
+ pFKey->pNextTo = pNextTo;
+ pNextTo->pPrevTo = pFKey;
+ }
+
/* Link the foreign key to the table as the last step.
*/
p->pFKey = pFKey;
@@ -2350,8 +2359,12 @@
** pList is a list of columns to be indexed. pList will be NULL if this
** is a primary key or unique-constraint on the most recent column added
** to the table currently under construction.
+**
+** If the index is created successfully, return a pointer to the new Index
+** structure. This is used by sqlite3AddPrimaryKey() to mark the index
+** as the tables primary key (Index.autoIndex==2).
*/
-void sqlite3CreateIndex(
+Index *sqlite3CreateIndex(
Parse *pParse, /* All information about this parse */
Token *pName1, /* First part of index name. May be NULL */
Token *pName2, /* Second part of index name. May be NULL */
@@ -2363,6 +2376,7 @@
int sortOrder, /* Sort order of primary key when pList==NULL */
int ifNotExist /* Omit error if index already exists */
){
+ Index *pRet = 0; /* Pointer to return */
Table *pTab = 0; /* Table to be indexed */
Index *pIndex = 0; /* The index to be created */
char *zName = 0; /* Name of the index */
@@ -2798,6 +2812,7 @@
pIndex->pNext = pOther->pNext;
pOther->pNext = pIndex;
}
+ pRet = pIndex;
pIndex = 0;
}
@@ -2810,7 +2825,7 @@
sqlite3ExprListDelete(db, pList);
sqlite3SrcListDelete(db, pTblName);
sqlite3DbFree(db, zName);
- return;
+ return pRet;
}
/*
diff --git a/src/callback.c b/src/callback.c
index 492e420..80dc693 100644
--- a/src/callback.c
+++ b/src/callback.c
@@ -423,6 +423,7 @@
sqlite3DeleteTable(pTab);
}
sqlite3HashClear(&temp1);
+ sqlite3HashClear(&pSchema->fkeyHash);
pSchema->pSeqTab = 0;
pSchema->flags &= ~DB_SchemaLoaded;
}
@@ -444,6 +445,7 @@
sqlite3HashInit(&p->tblHash);
sqlite3HashInit(&p->idxHash);
sqlite3HashInit(&p->trigHash);
+ sqlite3HashInit(&p->fkeyHash);
p->enc = SQLITE_UTF8;
}
return p;
diff --git a/src/delete.c b/src/delete.c
index 7e5b7bf..a384ade 100644
--- a/src/delete.c
+++ b/src/delete.c
@@ -308,7 +308,7 @@
goto delete_from_cleanup;
}
if( pParse->nested==0 ) sqlite3VdbeCountChanges(v);
- sqlite3BeginWriteOperation(pParse, (pTrigger?1:0), iDb);
+ sqlite3BeginWriteOperation(pParse, 1, iDb);
/* If we are trying to delete from a view, realize that view into
** a ephemeral table.
@@ -341,7 +341,9 @@
** It is easier just to erase the whole table. Prior to version 3.6.5,
** this optimization caused the row change count (the value returned by
** API function sqlite3_count_changes) to be set incorrectly. */
- if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab) ){
+ if( rcauth==SQLITE_OK && pWhere==0 && !pTrigger && !IsVirtual(pTab)
+ && 0==sqlite3FkRequired(pParse, pTab, 0)
+ ){
assert( !isView );
sqlite3VdbeAddOp4(v, OP_Clear, pTab->tnum, iDb, memCnt,
pTab->zName, P4_STATIC);
@@ -446,7 +448,7 @@
** These are the requirements:
**
** 1. A read/write cursor pointing to pTab, the table containing the row
-** to be deleted, must be opened as cursor number "base".
+** to be deleted, must be opened as cursor number $iCur.
**
** 2. Read/write cursors for all indices of pTab must be open as
** cursor number base+i for the i-th index.
@@ -481,13 +483,13 @@
/* If there are any triggers to fire, allocate a range of registers to
** use for the old.* references in the triggers. */
- if( pTrigger ){
+ if( sqlite3FkRequired(pParse, pTab, 0) || pTrigger ){
u32 mask; /* Mask of OLD.* columns in use */
int iCol; /* Iterator used while populating OLD.* */
/* TODO: Could use temporary registers here. Also could attempt to
** avoid copying the contents of the rowid register. */
- mask = sqlite3TriggerOldmask(pParse, pTrigger, TK_DELETE, 0, pTab, onconf);
+ mask = sqlite3TriggerOldmask(pParse, pTrigger, 0, pTab, onconf);
iOld = pParse->nMem+1;
pParse->nMem += (1 + pTab->nCol);
@@ -495,14 +497,14 @@
** used by any BEFORE and AFTER triggers that exist. */
sqlite3VdbeAddOp2(v, OP_Copy, iRowid, iOld);
for(iCol=0; iCol<pTab->nCol; iCol++){
- if( mask==0xffffffff || mask&(1<<iCol) ){
+ if( 1 || mask==0xffffffff || mask&(1<<iCol) ){
int iTarget = iOld + iCol + 1;
sqlite3VdbeAddOp3(v, OP_Column, iCur, iCol, iTarget);
sqlite3ColumnDefault(v, pTab, iCol, iTarget);
}
}
- /* Invoke any BEFORE trigger programs */
+ /* Invoke BEFORE DELETE trigger programs. */
sqlite3CodeRowTrigger(pParse, pTrigger,
TK_DELETE, 0, TRIGGER_BEFORE, pTab, -1, iOld, onconf, iLabel
);
@@ -512,6 +514,11 @@
** being deleted. Do not attempt to delete the row a second time, and
** do not fire AFTER triggers. */
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, 0, iOld, 0);
}
/* Delete the index and table entries. Skip this step if pTab is really
@@ -525,12 +532,15 @@
}
}
- /* Invoke AFTER triggers. */
- if( pTrigger ){
- sqlite3CodeRowTrigger(pParse, pTrigger,
- TK_DELETE, 0, TRIGGER_AFTER, pTab, -1, iOld, onconf, iLabel
- );
- }
+ /* 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);
+
+ /* Invoke AFTER DELETE trigger programs. */
+ sqlite3CodeRowTrigger(pParse, pTrigger,
+ TK_DELETE, 0, TRIGGER_AFTER, pTab, -1, iOld, onconf, iLabel
+ );
/* Jump here if the row had already been deleted before any BEFORE
** trigger programs were invoked. Or if a trigger program throws a
diff --git a/src/fkey.c b/src/fkey.c
new file mode 100644
index 0000000..1d9895a
--- /dev/null
+++ b/src/fkey.c
@@ -0,0 +1,784 @@
+/*
+**
+** 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 contains code used by the compiler to add foreign key
+** support to compiled SQL statements.
+*/
+#include "sqliteInt.h"
+
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+
+/*
+** Deferred and Immediate FKs
+** --------------------------
+**
+** Foreign keys in SQLite come in two flavours: deferred and immediate.
+** If an immediate foreign key constraint is violated, an OP_Halt is
+** executed and the current statement transaction rolled back. If a
+** deferred foreign key constraint is violated, no action is taken
+** immediately. However if the application attempts to commit the
+** transaction before fixing the constraint violation, the attempt fails.
+**
+** Deferred constraints are implemented using a simple counter associated
+** with the database handle. The counter is set to zero each time a
+** database transaction is opened. Each time a statement is executed
+** that causes a foreign key violation, the counter is incremented. Each
+** time a statement is executed that removes an existing violation from
+** the database, the counter is decremented. When the transaction is
+** committed, the commit fails if the current value of the counter is
+** greater than zero. This scheme has two big drawbacks:
+**
+** * When a commit fails due to a deferred foreign key constraint,
+** there is no way to tell which foreign constraint is not satisfied,
+** or which row it is not satisfied for.
+**
+** * If the database contains foreign key violations when the
+** transaction is opened, this may cause the mechanism to malfunction.
+**
+** Despite these problems, this approach is adopted as it seems simpler
+** than the alternatives.
+**
+** INSERT operations:
+**
+** I.1) For each FK for which the table is the referencing table, search
+** the referenced table for a match. If none is found, throw an
+** exception for an immediate FK, or increment the counter for a
+** deferred FK.
+**
+** I.2) For each deferred FK for which the table is the referenced table,
+** search the referencing table for rows that correspond to the new
+** row in the referenced table. Decrement the counter for each row
+** found (as the constraint is now satisfied).
+**
+** DELETE operations:
+**
+** D.1) For each deferred FK for which the table is the referencing table,
+** search the referenced table for a row that corresponds to the
+** deleted row in the referencing table. If such a row is not found,
+** decrement the counter.
+**
+** D.2) For each FK for which the table is the referenced table, search
+** the referencing table for rows that correspond to the deleted row
+** in the referenced table. For each found, throw an exception for an
+** immediate FK, or increment the counter for a deferred FK.
+**
+** UPDATE operations:
+**
+** An UPDATE command requires that all 4 steps above are taken, but only
+** for FK constraints for which the affected columns are actually
+** modified (values must be compared at runtime).
+**
+** Note that I.1 and D.1 are very similar operations, as are I.2 and D.2.
+** 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
+** ---------------
+**
+** Before coding an UPDATE or DELETE row operation, the code-generator
+** for those two operations needs to know whether or not the operation
+** requires any FK processing and, if so, which columns of the original
+** row are required by the FK processing VDBE code (i.e. if FKs were
+** implemented using triggers, which of the old.* columns would be
+** accessed). No information is required by the code-generator before
+** coding an INSERT operation.
+**
+*/
+
+/*
+** VDBE Calling Convention
+** -----------------------
+**
+** Example:
+**
+** For the following INSERT statement:
+**
+** CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c);
+** INSERT INTO t1 VALUES(1, 2, 3.1);
+**
+** Register (x): 2 (type integer)
+** Register (x+1): 1 (type integer)
+** Register (x+2): NULL (type NULL)
+** Register (x+3): 3.1 (type real)
+*/
+
+/*
+** ON UPDATE and ON DELETE clauses
+** -------------------------------
+*/
+
+/*
+** Externally accessible module functions
+** --------------------------------------
+**
+** sqlite3FkRequired()
+** sqlite3FkOldmask()
+**
+** sqlite3FkCheck()
+** sqlite3FkActions()
+**
+** sqlite3FkDelete()
+**
+*/
+
+/*
+** A foreign key constraint requires that the key columns in the referenced
+** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
+** Given that pTo is the referenced table for foreign key constraint
+** pFKey, check that the columns in pTo are indeed subject to a such a
+** constraint. If they are not, return non-zero and leave an error in pParse.
+**
+** If an error does not occur, return zero.
+*/
+static int locateFkeyIndex(
+ Parse *pParse, /* Parse context to store any error in */
+ Table *pTo, /* Referenced table */
+ FKey *pFKey, /* Foreign key to find index for */
+ Index **ppIdx, /* OUT: Unique index on referenced table */
+ int **paiCol /* OUT: Map of index columns in pFKey */
+){
+ Index *pIdx = 0;
+ int *aiCol = 0;
+ int nCol = pFKey->nCol;
+ char *zFirst = pFKey->aCol[0].zCol;
+
+ /* The caller is responsible for zeroing output parameters. */
+ assert( ppIdx && *ppIdx==0 );
+ assert( !paiCol || *paiCol==0 );
+
+ /* If this is a non-composite (single column) foreign key, check if it
+ ** maps to the INTEGER PRIMARY KEY of table pTo. If so, leave *ppIdx
+ ** and *paiCol set to zero and return early.
+ **
+ ** Otherwise, for a composite foreign key (more than one column), allocate
+ ** space for the aiCol array (returned via output parameter *paiCol).
+ ** Non-composite foreign keys do not require the aiCol array.
+ */
+ if( nCol==1 ){
+ /* The FK maps to the IPK if any of the following are true:
+ **
+ ** 1) The FK is explicitly mapped to "rowid", "oid" or "_rowid_", or
+ ** 2) There is an explicit INTEGER PRIMARY KEY column and the FK is
+ ** implicitly mapped to the primary key of table pTo, or
+ ** 3) The FK is explicitly mapped to a column declared as INTEGER
+ ** PRIMARY KEY.
+ */
+ if( zFirst && sqlite3IsRowid(zFirst) ) return 0;
+ if( pTo->iPKey>=0 ){
+ if( !zFirst ) return 0;
+ if( !sqlite3StrICmp(pTo->aCol[pTo->iPKey].zName, zFirst) ) return 0;
+ }
+ }else if( paiCol ){
+ assert( nCol>1 );
+ aiCol = (int *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int));
+ if( !aiCol ) return 1;
+ *paiCol = aiCol;
+ }
+
+ for(pIdx=pTo->pIndex; pIdx; pIdx=pIdx->pNext){
+ if( pIdx->nColumn==nCol && pIdx->onError!=OE_None ){
+ /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number
+ ** of columns. If each indexed column corresponds to a foreign key
+ ** column of pFKey, then this index is a winner. */
+
+ if( zFirst==0 ){
+ /* If zFirst is NULL, then this foreign key is implicitly mapped to
+ ** the PRIMARY KEY of table pTo. The PRIMARY KEY index may be
+ ** identified by the test (Index.autoIndex==2). */
+ if( pIdx->autoIndex==2 ){
+ if( aiCol ) memcpy(aiCol, pIdx->aiColumn, sizeof(int)*nCol);
+ break;
+ }
+ }else{
+ /* If zFirst is non-NULL, then this foreign key was declared to
+ ** map to an explicit list of columns in table pTo. Check if this
+ ** index matches those columns. */
+ int i, j;
+ for(i=0; i<nCol; i++){
+ char *zIdxCol = pTo->aCol[pIdx->aiColumn[i]].zName;
+ for(j=0; j<nCol; j++){
+ if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){
+ if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom;
+ break;
+ }
+ }
+ if( j==nCol ) break;
+ }
+ if( i==nCol ) break; /* pIdx is usable */
+ }
+ }
+ }
+
+ if( pParse && !pIdx ){
+ sqlite3ErrorMsg(pParse, "foreign key mismatch");
+ sqlite3DbFree(pParse->db, aiCol);
+ return 1;
+ }
+
+ *ppIdx = pIdx;
+ return 0;
+}
+
+static void fkCheckReference(
+ Parse *pParse, /* Parse context */
+ int iDb, /* Index of database housing pTab */
+ Table *pTab, /* Table referenced by FK pFKey */
+ Index *pIdx, /* Index ensuring uniqueness of FK in pTab */
+ FKey *pFKey, /* Foreign key to check */
+ int *aiCol, /* Map from FK column to referencing table column */
+ int regData, /* Address of array containing referencing row */
+ int nIncr /* If deferred FK, increment counter by this */
+){
+ int i;
+ Vdbe *v = sqlite3GetVdbe(pParse);
+ int iCur = pParse->nTab - 1;
+ int iOk = sqlite3VdbeMakeLabel(v);
+
+ assert( pFKey->isDeferred || nIncr==1 );
+
+ /* Check if any of the key columns in the referencing table are
+ ** NULL. If any are, then the constraint is satisfied. No need
+ ** to search for a matching row in the referenced table. */
+ for(i=0; i<pFKey->nCol; i++){
+ int iReg = pFKey->aCol[i].iFrom + regData + 1;
+ sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk);
+ }
+
+ if( pIdx==0 ){
+ /* If pIdx is NULL, then the foreign key constraint references the
+ ** INTEGER PRIMARY KEY column in the referenced table (table pTab). */
+ int iReg = pFKey->aCol[0].iFrom + regData + 1;
+ sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
+ sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iReg);
+ sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
+ sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
+ }else{
+ int regRec = sqlite3GetTempReg(pParse);
+ KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
+
+ sqlite3VdbeAddOp3(v, OP_OpenRead, iCur, pIdx->tnum, iDb);
+ sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
+
+ if( aiCol ){
+ int nCol = pFKey->nCol;
+ int regTemp = sqlite3GetTempRange(pParse, nCol);
+ for(i=0; i<nCol; i++){
+ sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[i]+1+regData, regTemp+i);
+ }
+ sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec);
+ sqlite3ReleaseTempRange(pParse, regTemp, nCol);
+ }else{
+ int iReg = pFKey->aCol[0].iFrom + regData + 1;
+ sqlite3VdbeAddOp3(v, OP_MakeRecord, iReg, 1, regRec);
+ sqlite3IndexAffinityStr(v, pIdx);
+ }
+
+ sqlite3VdbeAddOp3(v, OP_Found, iCur, iOk, regRec);
+ sqlite3ReleaseTempReg(pParse, regRec);
+ }
+
+ if( pFKey->isDeferred ){
+ assert( nIncr==1 || nIncr==-1 );
+ sqlite3VdbeAddOp1(v, OP_DeferredCons, nIncr);
+ }else{
+ sqlite3HaltConstraint(
+ pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
+ );
+ }
+
+ sqlite3VdbeResolveLabel(v, iOk);
+}
+
+static void fkScanReferences(
+ 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 FK to referenced table columns */
+ int regData, /* Referenced table data starts here */
+ int nIncr /* Amount to increment deferred counter by */
+){
+ 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 deleted row */
+ Expr *pRight; /* Column ref to referencing table */
+ Expr *pEq; /* Expression (pLeft = pRight) */
+ int iCol; /* Index of column in referencing table */
+ const char *zCol; /* Name of column in referencing table */
+
+ pLeft = sqlite3Expr(db, TK_REGISTER, 0);
+ if( pLeft ){
+ pLeft->iTable = (pIdx ? (regData+pIdx->aiColumn[i]+1) : regData);
+ }
+ iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
+ if( iCol<0 ){
+ zCol = "rowid";
+ }else{
+ zCol = pFKey->pFrom->aCol[iCol].zName;
+ }
+ pRight = sqlite3Expr(db, TK_ID, zCol);
+ pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0);
+ pWhere = sqlite3ExprAnd(db, pWhere, pEq);
+ }
+
+ /* Resolve the references in the WHERE clause. */
+ memset(&sNameContext, 0, sizeof(NameContext));
+ 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. */
+ pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0);
+ if( pFKey->isDeferred && nIncr ){
+ assert( nIncr==1 || nIncr==-1 );
+ sqlite3VdbeAddOp1(pParse->pVdbe, OP_DeferredCons, nIncr);
+ }else{
+ assert( nIncr==1 || nIncr==0 );
+ sqlite3HaltConstraint(
+ pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
+ );
+ }
+ sqlite3WhereEnd(pWInfo);
+
+ /* Clean up the WHERE clause constructed above. */
+ sqlite3ExprDelete(db, pWhere);
+}
+
+/*
+** This function returns a pointer to the head of a linked list of FK
+** constraints that refer to the table passed as an argument. For example,
+** given the following schema:
+**
+** CREATE TABLE t1(a PRIMARY KEY);
+** CREATE TABLE t2(b REFERENCES t1(a);
+**
+** Calling this function with table "t1" as an argument returns a pointer
+** to the FKey structure representing the foreign key constraint on table
+** "t2". Calling this function with "t2" as the argument would return a
+** NULL pointer (as there are no FK constraints that refer to t2).
+*/
+static FKey *fkRefering(Table *pTab){
+ int nName = sqlite3Strlen30(pTab->zName);
+ return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName);
+}
+
+void sqlite3FkCheck(
+ Parse *pParse, /* Parse context */
+ Table *pTab, /* Row is being deleted from this table */
+ ExprList *pChanges, /* Changed columns if this is an UPDATE */
+ int regOld, /* Previous row data is stored here */
+ int regNew /* New row data is stored here */
+){
+ sqlite3 *db = pParse->db; /* Database handle */
+ Vdbe *v; /* VM to write code to */
+ FKey *pFKey; /* Used to iterate through FKs */
+ int iDb; /* Index of database containing pTab */
+ const char *zDb; /* Name of database containing pTab */
+
+ assert( ( pChanges && regOld && regNew) /* UPDATE operation */
+ || (!pChanges && !regOld && regNew) /* INSERT operation */
+ || (!pChanges && regOld && !regNew) /* DELETE operation */
+ );
+
+ /* If foreign-keys are disabled, this function is a no-op. */
+ if( (db->flags&SQLITE_ForeignKeys)==0 ) return;
+
+ v = sqlite3GetVdbe(pParse);
+ iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
+ zDb = db->aDb[iDb].zName;
+
+ /* Loop through all the foreign key constraints attached to the table. */
+ for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
+ Table *pTo; /* Table referenced by this FK */
+ Index *pIdx = 0; /* Index on key columns in pTo */
+ int *aiCol = 0;
+
+ if( pFKey->isDeferred==0 && regNew==0 ) continue;
+
+ /* Find the table this foreign key references. Also find a unique
+ ** index on the referenced table that corresponds to the key columns.
+ ** If either of these things cannot be located, set an error in pParse
+ ** and return early. */
+ pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);
+ if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiCol) ) return;
+ assert( pFKey->nCol==1 || (aiCol && pIdx) );
+
+ /* If the key does not overlap with the pChanges list, skip this FK. */
+ if( pChanges ){
+ /* TODO */
+ }
+
+ /* Take a shared-cache advisory read-lock on the referenced table.
+ ** Allocate a cursor to use to search the unique index on the FK
+ ** columns in the referenced table. */
+ sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName);
+ pParse->nTab++;
+
+ if( regOld!=0 && pFKey->isDeferred ){
+ fkCheckReference(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1);
+ }
+ if( regNew!=0 ){
+ fkCheckReference(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1);
+ }
+
+ sqlite3DbFree(db, aiCol);
+ }
+
+ /* Loop through all the foreign key constraints that refer to this table */
+ for(pFKey = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){
+ int iGoto; /* Address of OP_Goto instruction */
+ Index *pIdx = 0; /* Foreign key index for pFKey */
+ SrcList *pSrc;
+ int *aiCol = 0;
+
+ /* For immediate constraints, skip this scan if:
+ **
+ ** 1) this is an INSERT operation, or
+ ** 2) an UPDATE operation and the FK action is a trigger-action, or
+ ** 3) a DELETE operation and the FK action is a trigger-action.
+ **
+ ** A "trigger-action" is one of CASCADE, SET DEFAULT or SET NULL.
+ */
+ if( pFKey->isDeferred==0 ){
+ if( regOld==0 ) continue; /* 1 */
+ if( regNew!=0 && pFKey->updateConf>OE_Restrict ) continue; /* 2 */
+ if( regNew==0 && pFKey->deleteConf>OE_Restrict ) continue; /* 3 */
+ }
+
+ if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return;
+ assert( aiCol || pFKey->nCol==1 );
+
+ /* Check if this update statement has modified any of the key columns
+ ** for this foreign key constraint. If it has not, there is no need
+ ** to search the referencing table for rows in violation. This is
+ ** just an optimization. Things would work fine without this check. */
+ if( pChanges ){
+ /* TODO */
+ }
+
+ /* Create a SrcList structure containing a single table (the table
+ ** the foreign key that refers to this table is attached to). This
+ ** is required for the sqlite3WhereXXX() interface. */
+ pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
+ if( !pSrc ) return;
+ pSrc->a->pTab = pFKey->pFrom;
+ pSrc->a->pTab->nRef++;
+ pSrc->a->iCursor = pParse->nTab++;
+
+ /* If this is an UPDATE, and none of the columns associated with this
+ ** FK have been modified, do not scan the referencing table. Unlike
+ ** the compile-time test implemented above, this is not just an
+ ** optimization. It is required so that immediate foreign keys do not
+ ** throw exceptions when the user executes a statement like:
+ **
+ ** UPDATE refd_table SET refd_column = refd_column
+ */
+ if( pChanges ){
+ int i;
+ int iJump = sqlite3VdbeCurrentAddr(v) + pFKey->nCol + 1;
+ for(i=0; i<pFKey->nCol; i++){
+ int iOff = (pIdx ? pIdx->aiColumn[i] : -1) + 1;
+ sqlite3VdbeAddOp3(v, OP_Ne, regOld+iOff, iJump, regNew+iOff);
+ }
+ iGoto = sqlite3VdbeAddOp0(v, OP_Goto);
+ }
+
+ if( regNew!=0 && pFKey->isDeferred ){
+ fkScanReferences(pParse, pSrc, pIdx, pFKey, aiCol, regNew, -1);
+ }
+ if( regOld!=0 ){
+ /* If there is a RESTRICT action configured for the current operation
+ ** on the referenced 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. */
+ fkScanReferences(pParse, pSrc, pIdx, pFKey, aiCol, regOld,
+ (pChanges!=0 && pFKey->updateConf!=OE_Restrict)
+ || (pChanges==0 && pFKey->deleteConf!=OE_Restrict)
+ );
+ }
+
+ if( pChanges ){
+ sqlite3VdbeJumpHere(v, iGoto);
+ }
+ sqlite3SrcListDelete(db, pSrc);
+ sqlite3DbFree(db, aiCol);
+ }
+}
+
+#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
+
+/*
+** This function is called before generating code to update or delete a
+** row contained in table pTab. If the operation is an update, then
+** pChanges is a pointer to the list of columns to modify. If this is a
+** delete, then pChanges is NULL.
+*/
+u32 sqlite3FkOldmask(
+ Parse *pParse, /* Parse context */
+ Table *pTab, /* Table being modified */
+ ExprList *pChanges /* Non-NULL for UPDATE operations */
+){
+ u32 mask = 0;
+ if( pParse->db->flags&SQLITE_ForeignKeys ){
+ FKey *p;
+ int i;
+ for(p=pTab->pFKey; p; p=p->pNextFrom){
+ if( pChanges || p->isDeferred ){
+ for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
+ }
+ }
+ for(p=fkRefering(pTab); p; p=p->pNextTo){
+ Index *pIdx = 0;
+ locateFkeyIndex(0, pTab, p, &pIdx, 0);
+ if( pIdx ){
+ for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
+ }
+ }
+ }
+ return mask;
+}
+
+/*
+** This function is called before generating code to update or delete a
+** row contained in table pTab. If the operation is an update, then
+** pChanges is a pointer to the list of columns to modify. If this is a
+** delete, then pChanges is NULL.
+**
+** If any foreign key processing will be required, this function returns
+** true. If there is no foreign key related processing, this function
+** returns false.
+*/
+int sqlite3FkRequired(
+ Parse *pParse, /* Parse context */
+ Table *pTab, /* Table being modified */
+ ExprList *pChanges /* Non-NULL for UPDATE operations */
+){
+ if( pParse->db->flags&SQLITE_ForeignKeys ){
+ FKey *p;
+ for(p=pTab->pFKey; p; p=p->pNextFrom){
+ if( pChanges || p->isDeferred ) return 1;
+ }
+ if( fkRefering(pTab) ) return 1;
+ }
+ return 0;
+}
+
+static Trigger *fkActionTrigger(
+ Parse *pParse,
+ Table *pTab, /* Table being updated or deleted from */
+ FKey *pFKey, /* Foreign key to get action for */
+ ExprList *pChanges /* Change-list for UPDATE, NULL for DELETE */
+){
+ sqlite3 *db = pParse->db; /* Database handle */
+ int action;
+ Trigger *pTrigger;
+
+ if( pChanges ){
+ action = pFKey->updateConf;
+ pTrigger = pFKey->pOnUpdate;
+ }else{
+ action = pFKey->deleteConf;
+ pTrigger = pFKey->pOnDelete;
+ }
+
+ assert( OE_SetNull>OE_Restrict && OE_SetDflt>OE_Restrict );
+ assert( OE_Cascade>OE_Restrict && OE_None<OE_Restrict );
+
+ if( action>OE_Restrict && !pTrigger ){
+ char const *zFrom; /* Name of referencing table */
+ int nFrom; /* Length in bytes of zFrom */
+ Index *pIdx = 0;
+ int *aiCol = 0;
+ TriggerStep *pStep;
+ sqlite3 *dbMem = pTab->dbMem;
+ Expr *pWhere = 0;
+ ExprList *pList = 0;
+ int i;
+
+ if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
+ assert( aiCol || pFKey->nCol==1 );
+
+ assert( dbMem==0 || dbMem==pParse->db );
+ zFrom = pFKey->pFrom->zName;
+ nFrom = sqlite3Strlen30(zFrom);
+ pTrigger = (Trigger *)sqlite3DbMallocZero(dbMem,
+ sizeof(Trigger) + /* struct Trigger */
+ sizeof(TriggerStep) + /* Single step in trigger program */
+ nFrom + 1 /* Space for pStep->target.z */
+ );
+ if( !pTrigger ){
+ pParse->db->mallocFailed = 1;
+ return 0;
+ }
+ pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
+ pStep->target.z = (char *)&pStep[1];
+ pStep->target.n = nFrom;
+ memcpy((char *)pStep->target.z, zFrom, nFrom);
+
+ for(i=0; i<pFKey->nCol; i++){
+ Expr *pEq;
+ int iFromCol; /* Idx of column in referencing table */
+ Token tFromCol; /* Name of column in referencing table */
+ Token tToCol; /* Name of column in referenced table */
+ Token tOld = { "old", 3 }; /* Literal "old" token */
+ Token tNew = { "new", 3 }; /* Literal "new" token */
+
+ iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
+ tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
+ tFromCol.z = iFromCol<0 ? "oid" : pFKey->pFrom->aCol[iFromCol].zName;
+
+ tToCol.n = sqlite3Strlen30(tToCol.z);
+ tFromCol.n = sqlite3Strlen30(tFromCol.z);
+
+ /* Create the expression "zFromCol = OLD.zToCol" */
+ pEq = sqlite3PExpr(pParse, TK_EQ,
+ sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol),
+ sqlite3PExpr(pParse, TK_DOT,
+ sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
+ sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
+ , 0)
+ , 0);
+ pWhere = sqlite3ExprAnd(pParse->db, pWhere, pEq);
+
+ if( action!=OE_Cascade || pChanges ){
+ Expr *pNew;
+ if( action==OE_Cascade ){
+ pNew = sqlite3PExpr(pParse, TK_DOT,
+ sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
+ sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
+ , 0);
+ }else if( action==OE_SetDflt ){
+ Expr *pDflt = pIdx ? 0 : pTab->aCol[pIdx->aiColumn[i]].pDflt;
+ if( pDflt ){
+ pNew = sqlite3ExprDup(db, pDflt, 0);
+ }else{
+ pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
+ }
+ }else{
+ pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
+ }
+ pList = sqlite3ExprListAppend(pParse, pList, pNew);
+ sqlite3ExprListSetName(pParse, pList, &tFromCol, 0);
+ }
+ }
+ sqlite3DbFree(pParse->db, aiCol);
+
+ pStep->pWhere = sqlite3ExprDup(dbMem, pWhere, EXPRDUP_REDUCE);
+ pStep->pExprList = sqlite3ExprListDup(dbMem, pList, EXPRDUP_REDUCE);
+ sqlite3ExprDelete(pParse->db, pWhere);
+ sqlite3ExprListDelete(pParse->db, pList);
+
+ pStep->op = (action!=OE_Cascade || pChanges) ? TK_UPDATE : TK_DELETE;
+ pStep->pTrig = pTrigger;
+ pTrigger->pSchema = pTab->pSchema;
+ pTrigger->pTabSchema = pTab->pSchema;
+
+ if( pChanges ){
+ pFKey->pOnUpdate = pTrigger;
+ pTrigger->op = TK_UPDATE;
+ pStep->op = TK_UPDATE;
+ }else{
+ pFKey->pOnDelete = pTrigger;
+ pTrigger->op = TK_DELETE;
+ pStep->op = (action==OE_Cascade)?TK_DELETE:TK_UPDATE;
+ }
+ }
+
+ return pTrigger;
+}
+
+static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){
+ if( p ){
+ TriggerStep *pStep = p->step_list;
+ sqlite3ExprDelete(dbMem, pStep->pWhere);
+ sqlite3ExprListDelete(dbMem, pStep->pExprList);
+ sqlite3DbFree(dbMem, p);
+ }
+}
+
+/*
+** This function is called when deleting or updating a row to implement
+** any required CASCADE, SET NULL or SET DEFAULT actions.
+*/
+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 */
+){
+ /* 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 = fkRefering(pTab); pFKey; pFKey=pFKey->pNextTo){
+ Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges);
+ if( pAction ){
+ sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0);
+ }
+ }
+ }
+}
+
+/*
+** Free all memory associated with foreign key definitions attached to
+** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash
+** hash table.
+*/
+void sqlite3FkDelete(Table *pTab){
+ FKey *pFKey; /* Iterator variable */
+ FKey *pNext; /* Copy of pFKey->pNextFrom */
+
+ for(pFKey=pTab->pFKey; pFKey; pFKey=pNext){
+
+ /* Remove the FK from the fkeyHash hash table. */
+ if( pFKey->pPrevTo ){
+ pFKey->pPrevTo->pNextTo = pFKey->pNextTo;
+ }else{
+ void *data = (void *)pFKey->pNextTo;
+ const char *z = (data ? pFKey->pNextTo->zTo : pFKey->zTo);
+ sqlite3HashInsert(&pTab->pSchema->fkeyHash, z, sqlite3Strlen30(z), data);
+ }
+ if( pFKey->pNextTo ){
+ pFKey->pNextTo->pPrevTo = pFKey->pPrevTo;
+ }
+
+ /* Delete any triggers created to implement actions for this FK. */
+ fkTriggerDelete(pTab->dbMem, pFKey->pOnDelete);
+ fkTriggerDelete(pTab->dbMem, pFKey->pOnUpdate);
+
+ /* Delete the memory allocated for the FK structure. */
+ pNext = pFKey->pNextFrom;
+ sqlite3DbFree(pTab->dbMem, pFKey);
+ }
+}
+
+#endif
diff --git a/src/insert.c b/src/insert.c
index f27d50f..071f65d 100644
--- a/src/insert.c
+++ b/src/insert.c
@@ -979,6 +979,7 @@
sqlite3GenerateConstraintChecks(pParse, pTab, baseCur, regIns, aRegIdx,
keyColumn>=0, 0, onError, endOfLoop, &isReplace
);
+ sqlite3FkCheck(pParse, pTab, 0, 0, regIns);
sqlite3CompleteInsertion(
pParse, pTab, baseCur, regIns, aRegIdx, 0, appendFlag, isReplace==0
);
@@ -1377,7 +1378,7 @@
sqlite3VdbeJumpHere(v, j3);
sqlite3ReleaseTempReg(pParse, regR);
}
-
+
if( pbMayReplace ){
*pbMayReplace = seenReplace;
}
diff --git a/src/main.c b/src/main.c
index c904d40..6f5e98e 100644
--- a/src/main.c
+++ b/src/main.c
@@ -731,6 +731,9 @@
sqlite3ResetInternalSchema(db, 0);
}
+ /* Any deferred constraint violations have now been resolved. */
+ db->nDeferredCons = 0;
+
/* If one has been configured, invoke the rollback-hook callback */
if( db->xRollbackCallback && (inTrans || !db->autoCommit) ){
db->xRollbackCallback(db->pRollbackArg);
diff --git a/src/parse.y b/src/parse.y
index 39af6c0..f3ea25e 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -196,9 +196,9 @@
// This obviates the need for the "id" nonterminal.
//
%fallback ID
- ABORT AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW CONFLICT
- DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
- IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH PLAN
+ ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
+ CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
+ IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK
SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL
%ifdef SQLITE_OMIT_COMPOUND_SELECT
@@ -314,20 +314,20 @@
// check fails.
//
%type refargs {int}
-refargs(A) ::= . { A = OE_Restrict * 0x010101; }
+refargs(A) ::= . { A = OE_None * 0x010101; }
refargs(A) ::= refargs(X) refarg(Y). { A = (X & ~Y.mask) | Y.value; }
%type refarg {struct {int value; int mask;}}
refarg(A) ::= MATCH nm. { A.value = 0; A.mask = 0x000000; }
refarg(A) ::= ON DELETE refact(X). { A.value = X; A.mask = 0x0000ff; }
refarg(A) ::= ON UPDATE refact(X). { A.value = X<<8; A.mask = 0x00ff00; }
-refarg(A) ::= ON INSERT refact(X). { A.value = X<<16; A.mask = 0xff0000; }
%type refact {int}
refact(A) ::= SET NULL. { A = OE_SetNull; }
refact(A) ::= SET DEFAULT. { A = OE_SetDflt; }
refact(A) ::= CASCADE. { A = OE_Cascade; }
refact(A) ::= RESTRICT. { A = OE_Restrict; }
+refact(A) ::= NO ACTION. { A = OE_None; }
%type defer_subclause {int}
-defer_subclause(A) ::= NOT DEFERRABLE init_deferred_pred_opt(X). {A = X;}
+defer_subclause(A) ::= NOT DEFERRABLE init_deferred_pred_opt. {A = 0;}
defer_subclause(A) ::= DEFERRABLE init_deferred_pred_opt(X). {A = X;}
%type init_deferred_pred_opt {int}
init_deferred_pred_opt(A) ::= . {A = 0;}
diff --git a/src/pragma.c b/src/pragma.c
index 60f214a..e2ad17d 100644
--- a/src/pragma.c
+++ b/src/pragma.c
@@ -191,6 +191,9 @@
** flag if there are any active statements. */
{ "read_uncommitted", SQLITE_ReadUncommitted },
{ "recursive_triggers", SQLITE_RecTriggers },
+
+ /* TODO: Prevent this flag from being set if not in auto-commit mode? */
+ { "foreign_keys", SQLITE_ForeignKeys },
};
int i;
const struct sPragmaType *p;
@@ -231,11 +234,12 @@
static const char *actionName(u8 action){
const char *zName;
switch( action ){
- case OE_SetNull: zName = "SET NULL"; break;
- case OE_SetDflt: zName = "SET DEFAULT"; break;
- case OE_Cascade: zName = "CASCADE"; break;
- default: zName = "RESTRICT";
- assert( action==OE_Restrict ); break;
+ case OE_SetNull: zName = "SET NULL"; break;
+ case OE_SetDflt: zName = "SET DEFAULT"; break;
+ case OE_Cascade: zName = "CASCADE"; break;
+ case OE_Restrict: zName = "RESTRICT"; break;
+ default: zName = "NO ACTION";
+ assert( action==OE_None ); break;
}
return zName;
}
diff --git a/src/shell.c b/src/shell.c
index 6883572..137ce35 100644
--- a/src/shell.c
+++ b/src/shell.c
@@ -851,7 +851,7 @@
" /delete_action/\n"
"END;\n"
- /* The "BEFORE DELETE ON <referenced>" trigger. This trigger's job
+ /* The "AFTER UPDATE ON <referenced>" trigger. This trigger's job
** is to detect when the key columns of a row in the referenced table
** to which one or more rows in the referencing table correspond are
** updated. The action taken depends on the value of the 'ON UPDATE'
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index fa5aaa0..b59e2bf 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -670,6 +670,7 @@
Hash tblHash; /* All tables indexed by name */
Hash idxHash; /* All (named) indices indexed by name */
Hash trigHash; /* All triggers indexed by name */
+ Hash fkeyHash; /* All foreign keys by referenced table name */
Table *pSeqTab; /* The sqlite_sequence table used by AUTOINCREMENT */
u8 file_format; /* Schema format version for this file */
u8 enc; /* Text encoding used by this database */
@@ -860,6 +861,9 @@
int nSavepoint; /* Number of non-transaction savepoints */
int nStatement; /* Number of nested statement-transactions */
u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+ i64 nDeferredCons; /* Net deferred constraints this transaction. */
+#endif
#ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
/* The following variables are all protected by the STATIC_MASTER
@@ -916,6 +920,7 @@
#define SQLITE_RecoveryMode 0x00040000 /* Ignore schema errors */
#define SQLITE_ReverseOrder 0x00100000 /* Reverse unordered SELECTs */
#define SQLITE_RecTriggers 0x00200000 /* Enable recursive triggers */
+#define SQLITE_ForeignKeys 0x00400000 /* Enforce foreign key constraints */
/*
** Possible values for the sqlite.magic field.
@@ -1002,6 +1007,7 @@
*/
struct Savepoint {
char *zName; /* Savepoint name (nul-terminated) */
+ int nDeferredCons; /* Number of deferred fk violations */
Savepoint *pNext; /* Parent savepoint (if any) */
};
@@ -1281,11 +1287,15 @@
Table *pFrom; /* The table that contains the REFERENCES clause */
FKey *pNextFrom; /* Next foreign key in pFrom */
char *zTo; /* Name of table that the key points to */
+ FKey *pNextTo; /* Next foreign key on table named zTo */
+ FKey *pPrevTo; /* Previous foreign key on table named zTo */
int nCol; /* Number of columns in this key */
u8 isDeferred; /* True if constraint checking is deferred till COMMIT */
u8 updateConf; /* How to resolve conflicts that occur on UPDATE */
u8 deleteConf; /* How to resolve conflicts that occur on DELETE */
u8 insertConf; /* How to resolve conflicts that occur on INSERT */
+ Trigger *pOnUpdate; /* Trigger for AFTER UPDATE ON zTo */
+ Trigger *pOnDelete; /* Trigger for AFTER DELETE ON zTo */
struct sColMap { /* Mapping of columns in pFrom to columns in zTo */
int iFrom; /* Index of column in pFrom */
char *zCol; /* Name of column in zTo. If 0 use PRIMARY KEY */
@@ -2586,7 +2596,7 @@
void sqlite3SrcListAssignCursors(Parse*, SrcList*);
void sqlite3IdListDelete(sqlite3*, IdList*);
void sqlite3SrcListDelete(sqlite3*, SrcList*);
-void sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
+Index *sqlite3CreateIndex(Parse*,Token*,Token*,SrcList*,ExprList*,int,Token*,
Token*, int, int);
void sqlite3DropIndex(Parse*, SrcList*, int);
int sqlite3Select(Parse*, Select*, SelectDest*);
@@ -2694,6 +2704,7 @@
Trigger *sqlite3TriggerList(Parse *, Table *);
void sqlite3CodeRowTrigger(Parse*, Trigger *, int, ExprList*, int, Table *,
int, int, int, int);
+ void sqlite3CodeRowTriggerDirect(Parse *, Trigger *, Table *, int, int, int);
void sqliteViewTriggers(Parse*, Table*, Expr*, int, ExprList*);
void sqlite3DeleteTriggerStep(sqlite3*, TriggerStep*);
TriggerStep *sqlite3TriggerSelectStep(sqlite3*,Select*);
@@ -2703,7 +2714,7 @@
TriggerStep *sqlite3TriggerDeleteStep(sqlite3*,Token*, Expr*);
void sqlite3DeleteTrigger(sqlite3*, Trigger*);
void sqlite3UnlinkAndDeleteTrigger(sqlite3*,int,const char*);
- u32 sqlite3TriggerOldmask(Parse*,Trigger*,int,ExprList*,Table*,int);
+ u32 sqlite3TriggerOldmask(Parse*,Trigger*,ExprList*,Table*,int);
# define sqlite3ParseToplevel(p) ((p)->pToplevel ? (p)->pToplevel : (p))
#else
# define sqlite3TriggersExist(B,C,D,E,F) 0
@@ -2713,7 +2724,7 @@
# define sqlite3CodeRowTrigger(A,B,C,D,E,F,G,H,I,J)
# define sqlite3TriggerList(X, Y) 0
# define sqlite3ParseToplevel(p) p
-# define sqlite3TriggerOldmask(A,B,C,D,E,F) 0
+# define sqlite3TriggerOldmask(A,B,C,D,E) 0
#endif
int sqlite3JoinType(Parse*, Token*, Token*, Token*);
@@ -2931,7 +2942,19 @@
int sqlite3TempInMemory(const sqlite3*);
VTable *sqlite3GetVTable(sqlite3*, Table*);
-
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+ void sqlite3FkCheck(Parse*, Table*, ExprList*, int, int);
+ void sqlite3FkActions(Parse*, Table*, ExprList*, int);
+ void sqlite3FkDelete(Table*);
+ int sqlite3FkRequired(Parse*, Table*, ExprList*);
+ u32 sqlite3FkOldmask(Parse*, Table*, ExprList*);
+#else
+ #define sqlite3FkCheck(a,b,c,d,e)
+ #define sqlite3FkActions(a,b,c,d)
+ #define sqlite3FkDelete(a)
+ #define sqlite3FkRequired(a,b,c) 0
+ #define sqlite3FkOldmask(a,b,c) 0
+#endif
/*
** Available fault injectors. Should be numbered beginning with 0.
diff --git a/src/trigger.c b/src/trigger.c
index f062267..79f7f48 100644
--- a/src/trigger.c
+++ b/src/trigger.c
@@ -795,7 +795,7 @@
Parse *pSubParse; /* Parse context for sub-vdbe */
int iEndTrigger = 0; /* Label to jump to if WHEN is false */
- assert( pTab==tableOfTrigger(pTrigger) );
+ assert( pTrigger->zName==0 || pTab==tableOfTrigger(pTrigger) );
/* Allocate the TriggerPrg and SubProgram objects. To ensure that they
** are freed if an error occurs, link them into the Parse.pTriggerPrg
@@ -896,7 +896,7 @@
Parse *pRoot = sqlite3ParseToplevel(pParse);
TriggerPrg *pPrg;
- assert( pTab==tableOfTrigger(pTrigger) );
+ assert( pTrigger->zName==0 || pTab==tableOfTrigger(pTrigger) );
/* It may be that this trigger has already been coded (or is in the
** process of being coded). If this is the case, then an entry with
@@ -915,6 +915,38 @@
return pPrg;
}
+void sqlite3CodeRowTriggerDirect(
+ Parse *pParse, /* Parse context */
+ Trigger *p, /* Trigger to code */
+ Table *pTab, /* The table to code triggers from */
+ int oldIdx, /* The indice of the "old" row to access */
+ int orconf, /* ON CONFLICT policy */
+ int ignoreJump /* Instruction to jump to for RAISE(IGNORE) */
+){
+ Vdbe *v = sqlite3GetVdbe(pParse); /* Main VM */
+ TriggerPrg *pPrg;
+ pPrg = getRowTrigger(pParse, p, pTab, orconf);
+ assert( pPrg || pParse->nErr || pParse->db->mallocFailed );
+
+ /* Code the OP_Program opcode in the parent VDBE. P4 of the OP_Program
+ ** is a pointer to the sub-vdbe containing the trigger program. */
+ if( pPrg ){
+ sqlite3VdbeAddOp3(v, OP_Program, oldIdx, ignoreJump, ++pParse->nMem);
+ pPrg->pProgram->nRef++;
+ sqlite3VdbeChangeP4(v, -1, (const char *)pPrg->pProgram, P4_SUBPROGRAM);
+ VdbeComment(
+ (v, "Call: %s.%s", (p->zName?p->zName:"fkey"), onErrorText(orconf)));
+
+ /* Set the P5 operand of the OP_Program instruction to non-zero if
+ ** recursive invocation of this trigger program is disallowed. Recursive
+ ** invocation is disallowed if (a) the sub-program is really a trigger,
+ ** not a foreign key action, and (b) the flag to enable recursive triggers
+ ** is clear. */
+ sqlite3VdbeChangeP5(v, p->zName && !(pParse->db->flags&SQLITE_RecTriggers));
+
+ }
+}
+
/*
** This is called to code FOR EACH ROW triggers.
**
@@ -976,19 +1008,7 @@
&& p->tr_tm==tr_tm
&& checkColumnOverlap(p->pColumns,pChanges)
){
- Vdbe *v = sqlite3GetVdbe(pParse); /* Main VM */
- TriggerPrg *pPrg;
- pPrg = getRowTrigger(pParse, p, pTab, orconf);
- assert( pPrg || pParse->nErr || pParse->db->mallocFailed );
-
- /* Code the OP_Program opcode in the parent VDBE. P4 of the OP_Program
- ** is a pointer to the sub-vdbe containing the trigger program. */
- if( pPrg ){
- sqlite3VdbeAddOp3(v, OP_Program, oldIdx, ignoreJump, ++pParse->nMem);
- pPrg->pProgram->nRef++;
- sqlite3VdbeChangeP4(v, -1, (const char *)pPrg->pProgram, P4_SUBPROGRAM);
- VdbeComment((v, "Call: %s.%s", p->zName, onErrorText(orconf)));
- }
+ sqlite3CodeRowTriggerDirect(pParse, p, pTab, oldIdx, orconf, ignoreJump);
}
}
}
@@ -1015,15 +1035,14 @@
u32 sqlite3TriggerOldmask(
Parse *pParse, /* Parse context */
Trigger *pTrigger, /* List of triggers on table pTab */
- int op, /* Either TK_UPDATE or TK_DELETE */
ExprList *pChanges, /* Changes list for any UPDATE OF triggers */
Table *pTab, /* The table to code triggers from */
int orconf /* Default ON CONFLICT policy for trigger steps */
){
+ const int op = pChanges ? TK_UPDATE : TK_DELETE;
u32 mask = 0;
Trigger *p;
- assert(op==TK_UPDATE || op==TK_DELETE);
for(p=pTrigger; p; p=p->pNext){
if( p->op==op && checkColumnOverlap(p->pColumns,pChanges) ){
TriggerPrg *pPrg;
diff --git a/src/update.c b/src/update.c
index f0880de..c5463bc 100644
--- a/src/update.c
+++ b/src/update.c
@@ -115,12 +115,12 @@
int iDb; /* Database containing the table being updated */
int j1; /* Addresses of jump instructions */
int okOnePass; /* True for one-pass algorithm without the FIFO */
+ int hasFK; /* True if foreign key processing is required */
#ifndef SQLITE_OMIT_TRIGGER
int isView; /* Trying to update a view */
Trigger *pTrigger; /* List of triggers on pTab, if required */
#endif
- u32 oldmask = 0; /* Mask of OLD.* columns in use */
/* Register Allocations */
int regRowCount = 0; /* A count of rows changed */
@@ -159,6 +159,8 @@
# define isView 0
#endif
+ hasFK = sqlite3FkRequired(pParse, pTab, pChanges);
+
if( sqlite3ViewGetColumnNames(pParse, pTab) ){
goto update_cleanup;
}
@@ -273,11 +275,11 @@
/* Allocate required registers. */
regOldRowid = regNewRowid = ++pParse->nMem;
- if( pTrigger ){
+ if( pTrigger || hasFK ){
regOld = pParse->nMem + 1;
pParse->nMem += pTab->nCol;
}
- if( chngRowid || pTrigger ){
+ if( chngRowid || pTrigger || hasFK ){
regNewRowid = ++pParse->nMem;
}
regNew = pParse->nMem + 1;
@@ -289,10 +291,6 @@
sqlite3AuthContextPush(pParse, &sContext, pTab->zName);
}
- /* If there are any triggers, set oldmask and new_col_mask. */
- oldmask = sqlite3TriggerOldmask(
- pParse, pTrigger, TK_UPDATE, pChanges, pTab, onError);
-
/* If we are trying to update a view, realize that view into
** a ephemeral table.
*/
@@ -378,9 +376,21 @@
** for example, then jump to the next iteration of the RowSet loop. */
sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addr, regOldRowid);
+ /* If the record number will change, set register regNewRowid to
+ ** contain the new value. If the record number is not being modified,
+ ** then regNewRowid is the same register as regOldRowid, which is
+ ** already populated. */
+ assert( chngRowid || pTrigger || hasFK || regOldRowid==regNewRowid );
+ if( chngRowid ){
+ sqlite3ExprCode(pParse, pRowidExpr, regNewRowid);
+ sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid);
+ }
+
/* If there are triggers on this table, populate an array of registers
** with the required old.* column data. */
- if( pTrigger ){
+ if( hasFK || pTrigger ){
+ u32 oldmask = sqlite3FkOldmask(pParse, pTab, pChanges);
+ oldmask |= sqlite3TriggerOldmask(pParse, pTrigger, pChanges, pTab, onError);
for(i=0; i<pTab->nCol; i++){
if( aXRef[i]<0 || oldmask==0xffffffff || (oldmask & (1<<i)) ){
sqlite3VdbeAddOp3(v, OP_Column, iCur, i, regOld+i);
@@ -389,18 +399,9 @@
sqlite3VdbeAddOp2(v, OP_Null, 0, regOld+i);
}
}
- }
-
- /* If the record number will change, set register regNewRowid to
- ** contain the new value. If the record number is not being modified,
- ** then regNewRowid is the same register as regOldRowid, which is
- ** already populated. */
- assert( chngRowid || pTrigger || regOldRowid==regNewRowid );
- if( chngRowid ){
- sqlite3ExprCode(pParse, pRowidExpr, regNewRowid);
- sqlite3VdbeAddOp1(v, OP_MustBeInt, regNewRowid);
- }else if( pTrigger ){
- sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid);
+ if( chngRowid==0 ){
+ sqlite3VdbeAddOp2(v, OP_Copy, regOldRowid, regNewRowid);
+ }
}
/* Populate the array of registers beginning at regNew with the new
@@ -443,6 +444,9 @@
sqlite3GenerateConstraintChecks(pParse, pTab, iCur, regNewRowid,
aRegIdx, (chngRowid?regOldRowid:0), 1, onError, addr, 0);
+ /* Do FK constraint checks. */
+ sqlite3FkCheck(pParse, pTab, pChanges, regOldRowid, regNewRowid);
+
/* Delete the index entries associated with the current record. */
j1 = sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regOldRowid);
sqlite3GenerateRowIndexDelete(pParse, pTab, iCur, aRegIdx);
@@ -455,6 +459,11 @@
/* 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. */
+ sqlite3FkActions(pParse, pTab, pChanges, regOldRowid);
}
/* Increment the row counter
diff --git a/src/vdbe.c b/src/vdbe.c
index e25280a..f0e1c22 100644
--- a/src/vdbe.c
+++ b/src/vdbe.c
@@ -872,10 +872,12 @@
sqlite3SetString(&p->zErrMsg, db, "%s", pOp->p4.z);
}
rc = sqlite3VdbeHalt(p);
- assert( rc==SQLITE_BUSY || rc==SQLITE_OK );
+ assert( rc==SQLITE_BUSY || rc==SQLITE_OK || rc==SQLITE_ERROR );
if( rc==SQLITE_BUSY ){
p->rc = rc = SQLITE_BUSY;
}else{
+ assert( rc==SQLITE_OK || p->rc==SQLITE_CONSTRAINT );
+ assert( rc==SQLITE_OK || db->nDeferredCons>0 );
rc = p->rc ? SQLITE_ERROR : SQLITE_DONE;
}
goto vdbe_return;
@@ -2507,6 +2509,7 @@
/* Link the new savepoint into the database handle's list. */
pNew->pNext = db->pSavepoint;
+ pNew->nDeferredCons = db->nDeferredCons;
db->pSavepoint = pNew;
}
}
@@ -2545,6 +2548,9 @@
*/
int isTransaction = pSavepoint->pNext==0 && db->isTransactionSavepoint;
if( isTransaction && p1==SAVEPOINT_RELEASE ){
+ if( (rc = sqlite3VdbeCheckDeferred(p))!=SQLITE_OK ){
+ goto vdbe_return;
+ }
db->autoCommit = 1;
if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
p->pc = pc;
@@ -2577,7 +2583,10 @@
db->nSavepoint--;
}
- /* If it is a RELEASE, then destroy the savepoint being operated on too */
+ /* If it is a RELEASE, then destroy the savepoint being operated on
+ ** too. If it is a ROLLBACK TO, then set the number of deferred
+ ** constraint violations present in the database to the value stored
+ ** when the savepoint was created. */
if( p1==SAVEPOINT_RELEASE ){
assert( pSavepoint==db->pSavepoint );
db->pSavepoint = pSavepoint->pNext;
@@ -2585,6 +2594,8 @@
if( !isTransaction ){
db->nSavepoint--;
}
+ }else{
+ db->nDeferredCons = pSavepoint->nDeferredCons;
}
}
}
@@ -2633,6 +2644,8 @@
assert( desiredAutoCommit==1 );
sqlite3RollbackAll(db);
db->autoCommit = 1;
+ }else if( (rc = sqlite3VdbeCheckDeferred(p))!=SQLITE_OK ){
+ goto vdbe_return;
}else{
db->autoCommit = (u8)desiredAutoCommit;
if( sqlite3VdbeHalt(p)==SQLITE_BUSY ){
@@ -2720,6 +2733,11 @@
p->iStatement = db->nSavepoint + db->nStatement;
}
rc = sqlite3BtreeBeginStmt(pBt, p->iStatement);
+
+ /* Store the current value of the database handles deferred constraint
+ ** counter. If the statement transaction needs to be rolled back,
+ ** the value of this counter needs to be restored too. */
+ p->nStmtDefCons = db->nDeferredCons;
}
}
break;
@@ -4733,18 +4751,18 @@
pRt = &p->aMem[pOp->p3];
assert( pProgram->nOp>0 );
- /* If the SQLITE_RecTriggers flag is clear, then recursive invocation of
- ** triggers is disabled for backwards compatibility (flag set/cleared by
- ** the "PRAGMA recursive_triggers" command).
+ /* If the p5 flag is clear, then recursive invocation of triggers is
+ ** disabled for backwards compatibility (p5 is set if this sub-program
+ ** is really a trigger, not a foreign key action, and the flag set
+ ** and cleared by the "PRAGMA recursive_triggers" command is clear).
**
** It is recursive invocation of triggers, at the SQL level, that is
** disabled. In some cases a single trigger may generate more than one
** SubProgram (if the trigger may be executed with more than one different
** ON CONFLICT algorithm). SubProgram structures associated with a
** single trigger all have the same value for the SubProgram.token
- ** variable.
- */
- if( 0==(db->flags&SQLITE_RecTriggers) ){
+ ** variable. */
+ if( pOp->p5 ){
t = pProgram->token;
for(pFrame=p->pFrame; pFrame && pFrame->token!=t; pFrame=pFrame->pParent);
if( pFrame ) break;
@@ -4842,6 +4860,18 @@
#endif /* #ifndef SQLITE_OMIT_TRIGGER */
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+/* Opcode: DeferredCons P1 * * * *
+**
+** Increment the database handles "deferred constraint violation" counter
+** by P1 (P1 may be negative or positive).
+*/
+case OP_DeferredCons: {
+ db->nDeferredCons += pOp->p1;
+ break;
+}
+#endif /* #ifndef SQLITE_OMIT_FOREIGN_KEY */
+
#ifndef SQLITE_OMIT_AUTOINCREMENT
/* Opcode: MemMax P1 P2 * * *
**
diff --git a/src/vdbeInt.h b/src/vdbeInt.h
index ecf8dcd..0c3ffad 100644
--- a/src/vdbeInt.h
+++ b/src/vdbeInt.h
@@ -315,6 +315,7 @@
int aCounter[2]; /* Counters used by sqlite3_stmt_status() */
char *zSql; /* Text of the SQL statement that generated this */
void *pFree; /* Free this when deleting the vdbe */
+ i64 nStmtDefCons; /* Number of def. constraints when stmt started */
int iStatement; /* Statement number (or 0 if has not opened stmt) */
#ifdef SQLITE_DEBUG
FILE *trace; /* Write an execution trace here, if not NULL */
@@ -387,6 +388,12 @@
int sqlite3VdbeReleaseBuffers(Vdbe *p);
#endif
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+int sqlite3VdbeCheckDeferred(Vdbe *);
+#else
+# define sqlite3VdbeCheckDeferred(p) 0
+#endif
+
#ifndef SQLITE_OMIT_SHARED_CACHE
void sqlite3VdbeMutexArrayEnter(Vdbe *p);
#else
diff --git a/src/vdbeapi.c b/src/vdbeapi.c
index 634c29c..66dfd71 100644
--- a/src/vdbeapi.c
+++ b/src/vdbeapi.c
@@ -323,6 +323,8 @@
db->u1.isInterrupted = 0;
}
+ assert( db->writeVdbeCnt>0 || db->autoCommit==0 || db->nDeferredCons==0 );
+
#ifndef SQLITE_OMIT_TRACE
if( db->xProfile && !db->init.busy ){
double rNow;
diff --git a/src/vdbeaux.c b/src/vdbeaux.c
index c169b37..fdf223e 100644
--- a/src/vdbeaux.c
+++ b/src/vdbeaux.c
@@ -1895,6 +1895,13 @@
}
db->nStatement--;
p->iStatement = 0;
+
+ /* If the statement transaction is being rolled back, also restore the
+ ** database handles deferred constraint counter to the value it had when
+ ** the statement transaction was opened. */
+ if( eOp==SAVEPOINT_ROLLBACK ){
+ db->nDeferredCons = p->nStmtDefCons;
+ }
}
return rc;
}
@@ -1927,6 +1934,28 @@
#endif
/*
+** This function is called when a transaction opened by the database
+** handle associated with the VM passed as an argument is about to be
+** committed. If there are outstanding deferred foreign key constraint
+** violations, return SQLITE_ERROR. Otherwise, SQLITE_OK.
+**
+** If there are outstanding FK violations and this function returns
+** SQLITE_ERROR, set the result of the VM to SQLITE_CONSTRAINT and write
+** an error message to it. Then return SQLITE_ERROR.
+*/
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+int sqlite3VdbeCheckDeferred(Vdbe *p){
+ sqlite3 *db = p->db;
+ if( db->nDeferredCons ){
+ p->rc = SQLITE_CONSTRAINT;
+ sqlite3SetString(&p->zErrMsg, db, "foreign key constraint failed");
+ return SQLITE_ERROR;
+ }
+ return SQLITE_OK;
+}
+#endif
+
+/*
** This routine is called the when a VDBE tries to halt. If the VDBE
** has made changes and is in autocommit mode, then commit those
** changes. If a rollback is needed, then do the rollback.
@@ -2012,10 +2041,14 @@
&& db->writeVdbeCnt==(p->readOnly==0)
){
if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){
- /* The auto-commit flag is true, and the vdbe program was
- ** successful or hit an 'OR FAIL' constraint. This means a commit
- ** is required.
- */
+ if( sqlite3VdbeCheckDeferred(p) ){
+ sqlite3BtreeMutexArrayLeave(&p->aMutex);
+ return SQLITE_ERROR;
+ }
+ /* The auto-commit flag is true, the vdbe program was successful
+ ** or hit an 'OR FAIL' constraint and there are no deferred foreign
+ ** key constraints to hold up the transaction. This means a commit
+ ** is required. */
rc = vdbeCommit(db, p);
if( rc==SQLITE_BUSY ){
sqlite3BtreeMutexArrayLeave(&p->aMutex);
@@ -2024,6 +2057,7 @@
p->rc = rc;
sqlite3RollbackAll(db);
}else{
+ db->nDeferredCons = 0;
sqlite3CommitInternalChanges(db);
}
}else{
diff --git a/src/vdbeblob.c b/src/vdbeblob.c
index 009234f..394335a 100644
--- a/src/vdbeblob.c
+++ b/src/vdbeblob.c
@@ -144,25 +144,41 @@
}
/* If the value is being opened for writing, check that the
- ** column is not indexed. It is against the rules to open an
- ** indexed column for writing.
- */
+ ** column is not indexed, and that it is not part of a foreign key.
+ ** It is against the rules to open a column to which either of these
+ ** descriptions applies for writing. */
if( flags ){
+ const char *zFault = 0;
Index *pIdx;
+#ifndef SQLITE_OMIT_FOREIGN_KEY
+ if( db->flags&SQLITE_ForeignKeys ){
+ FKey *pFKey;
+ for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
+ int j;
+ for(j=0; j<pFKey->nCol; j++){
+ if( pFKey->aCol[j].iFrom==iCol ){
+ zFault = "foreign key";
+ }
+ }
+ }
+ }
+#endif
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
int j;
for(j=0; j<pIdx->nColumn; j++){
if( pIdx->aiColumn[j]==iCol ){
- sqlite3DbFree(db, zErr);
- zErr = sqlite3MPrintf(db,
- "cannot open indexed column for writing");
- rc = SQLITE_ERROR;
- (void)sqlite3SafetyOff(db);
- sqlite3BtreeLeaveAll(db);
- goto blob_open_out;
+ zFault = "indexed";
}
}
}
+ if( zFault ){
+ sqlite3DbFree(db, zErr);
+ zErr = sqlite3MPrintf(db, "cannot open %s column for writing", zFault);
+ rc = SQLITE_ERROR;
+ (void)sqlite3SafetyOff(db);
+ sqlite3BtreeLeaveAll(db);
+ goto blob_open_out;
+ }
}
v = sqlite3VdbeCreate(db);