3 ** The author disclaims copyright to this source code. In place of
4 ** a legal notice, here is a blessing:
6 ** May you do good and not evil.
7 ** May you find forgiveness for yourself and forgive others.
8 ** May you share freely, never taking more than you give.
10 *************************************************************************
11 ** This file contains code used by the compiler to add foreign key
12 ** support to compiled SQL statements.
14 #include "sqliteInt.h"
16 #ifndef SQLITE_OMIT_FOREIGN_KEY
17 #ifndef SQLITE_OMIT_TRIGGER
20 ** Deferred and Immediate FKs
21 ** --------------------------
23 ** Foreign keys in SQLite come in two flavours: deferred and immediate.
24 ** If an immediate foreign key constraint is violated,
25 ** SQLITE_CONSTRAINT_FOREIGNKEY is returned and the current
26 ** statement transaction rolled back. If a
27 ** deferred foreign key constraint is violated, no action is taken
28 ** immediately. However if the application attempts to commit the
29 ** transaction before fixing the constraint violation, the attempt fails.
31 ** Deferred constraints are implemented using a simple counter associated
32 ** with the database handle. The counter is set to zero each time a
33 ** database transaction is opened. Each time a statement is executed
34 ** that causes a foreign key violation, the counter is incremented. Each
35 ** time a statement is executed that removes an existing violation from
36 ** the database, the counter is decremented. When the transaction is
37 ** committed, the commit fails if the current value of the counter is
38 ** greater than zero. This scheme has two big drawbacks:
40 ** * When a commit fails due to a deferred foreign key constraint,
41 ** there is no way to tell which foreign constraint is not satisfied,
42 ** or which row it is not satisfied for.
44 ** * If the database contains foreign key violations when the
45 ** transaction is opened, this may cause the mechanism to malfunction.
47 ** Despite these problems, this approach is adopted as it seems simpler
48 ** than the alternatives.
52 ** I.1) For each FK for which the table is the child table, search
53 ** the parent table for a match. If none is found increment the
54 ** constraint counter.
56 ** I.2) For each FK for which the table is the parent table,
57 ** search the child table for rows that correspond to the new
58 ** row in the parent table. Decrement the counter for each row
59 ** found (as the constraint is now satisfied).
63 ** D.1) For each FK for which the table is the child table,
64 ** search the parent table for a row that corresponds to the
65 ** deleted row in the child table. If such a row is not found,
66 ** decrement the counter.
68 ** D.2) For each FK for which the table is the parent table, search
69 ** the child table for rows that correspond to the deleted row
70 ** in the parent table. For each found increment the counter.
74 ** An UPDATE command requires that all 4 steps above are taken, but only
75 ** for FK constraints for which the affected columns are actually
76 ** modified (values must be compared at runtime).
78 ** Note that I.1 and D.1 are very similar operations, as are I.2 and D.2.
79 ** This simplifies the implementation a bit.
81 ** For the purposes of immediate FK constraints, the OR REPLACE conflict
82 ** resolution is considered to delete rows before the new row is inserted.
83 ** If a delete caused by OR REPLACE violates an FK constraint, an exception
84 ** is thrown, even if the FK constraint would be satisfied after the new
87 ** Immediate constraints are usually handled similarly. The only difference
88 ** is that the counter used is stored as part of each individual statement
89 ** object (struct Vdbe). If, after the statement has run, its immediate
90 ** constraint counter is greater than zero,
91 ** it returns SQLITE_CONSTRAINT_FOREIGNKEY
92 ** and the statement transaction is rolled back. An exception is an INSERT
93 ** statement that inserts a single row only (no triggers). In this case,
94 ** instead of using a counter, an exception is thrown immediately if the
95 ** INSERT violates a foreign key constraint. This is necessary as such
96 ** an INSERT does not open a statement transaction.
98 ** TODO: How should dropping a table be handled? How should renaming a
105 ** Before coding an UPDATE or DELETE row operation, the code-generator
106 ** for those two operations needs to know whether or not the operation
107 ** requires any FK processing and, if so, which columns of the original
108 ** row are required by the FK processing VDBE code (i.e. if FKs were
109 ** implemented using triggers, which of the old.* columns would be
110 ** accessed). No information is required by the code-generator before
111 ** coding an INSERT operation. The functions used by the UPDATE/DELETE
112 ** generation code to query for this information are:
114 ** sqlite3FkRequired() - Test to see if FK processing is required.
115 ** sqlite3FkOldmask() - Query for the set of required old.* columns.
118 ** Externally accessible module functions
119 ** --------------------------------------
121 ** sqlite3FkCheck() - Check for foreign key violations.
122 ** sqlite3FkActions() - Code triggers for ON UPDATE/ON DELETE actions.
123 ** sqlite3FkDelete() - Delete an FKey structure.
127 ** VDBE Calling Convention
128 ** -----------------------
132 ** For the following INSERT statement:
134 ** CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c);
135 ** INSERT INTO t1 VALUES(1, 2, 3.1);
137 ** Register (x): 2 (type integer)
138 ** Register (x+1): 1 (type integer)
139 ** Register (x+2): NULL (type NULL)
140 ** Register (x+3): 3.1 (type real)
144 ** A foreign key constraint requires that the key columns in the parent
145 ** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
146 ** Given that pParent is the parent table for foreign key constraint pFKey,
147 ** search the schema for a unique index on the parent key columns.
149 ** If successful, zero is returned. If the parent key is an INTEGER PRIMARY
150 ** KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx
151 ** is set to point to the unique index.
153 ** If the parent key consists of a single column (the foreign key constraint
154 ** is not a composite foreign key), output variable *paiCol is set to NULL.
155 ** Otherwise, it is set to point to an allocated array of size N, where
156 ** N is the number of columns in the parent key. The first element of the
157 ** array is the index of the child table column that is mapped by the FK
158 ** constraint to the parent table column stored in the left-most column
159 ** of index *ppIdx. The second element of the array is the index of the
160 ** child table column that corresponds to the second left-most column of
161 ** *ppIdx, and so on.
163 ** If the required index cannot be found, either because:
165 ** 1) The named parent key columns do not exist, or
167 ** 2) The named parent key columns do exist, but are not subject to a
168 ** UNIQUE or PRIMARY KEY constraint, or
170 ** 3) No parent key columns were provided explicitly as part of the
171 ** foreign key definition, and the parent table does not have a
174 ** 4) No parent key columns were provided explicitly as part of the
175 ** foreign key definition, and the PRIMARY KEY of the parent table
176 ** consists of a different number of columns to the child key in
179 ** then non-zero is returned, and a "foreign key mismatch" error loaded
180 ** into pParse. If an OOM error occurs, non-zero is returned and the
181 ** pParse->db->mallocFailed flag is set.
183 int sqlite3FkLocateIndex(
184 Parse
*pParse
, /* Parse context to store any error in */
185 Table
*pParent
, /* Parent table of FK constraint pFKey */
186 FKey
*pFKey
, /* Foreign key to find index for */
187 Index
**ppIdx
, /* OUT: Unique index on parent table */
188 int **paiCol
/* OUT: Map of index columns in pFKey */
190 Index
*pIdx
= 0; /* Value to return via *ppIdx */
191 int *aiCol
= 0; /* Value to return via *paiCol */
192 int nCol
= pFKey
->nCol
; /* Number of columns in parent key */
193 char *zKey
= pFKey
->aCol
[0].zCol
; /* Name of left-most parent key column */
195 /* The caller is responsible for zeroing output parameters. */
196 assert( ppIdx
&& *ppIdx
==0 );
197 assert( !paiCol
|| *paiCol
==0 );
200 /* If this is a non-composite (single column) foreign key, check if it
201 ** maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx
202 ** and *paiCol set to zero and return early.
204 ** Otherwise, for a composite foreign key (more than one column), allocate
205 ** space for the aiCol array (returned via output parameter *paiCol).
206 ** Non-composite foreign keys do not require the aiCol array.
209 /* The FK maps to the IPK if any of the following are true:
211 ** 1) There is an INTEGER PRIMARY KEY column and the FK is implicitly
212 ** mapped to the primary key of table pParent, or
213 ** 2) The FK is explicitly mapped to a column declared as INTEGER
216 if( pParent
->iPKey
>=0 ){
217 if( !zKey
) return 0;
218 if( !sqlite3StrICmp(pParent
->aCol
[pParent
->iPKey
].zName
, zKey
) ) return 0;
222 aiCol
= (int *)sqlite3DbMallocRawNN(pParse
->db
, nCol
*sizeof(int));
223 if( !aiCol
) return 1;
227 for(pIdx
=pParent
->pIndex
; pIdx
; pIdx
=pIdx
->pNext
){
228 if( pIdx
->nKeyCol
==nCol
&& IsUniqueIndex(pIdx
) && pIdx
->pPartIdxWhere
==0 ){
229 /* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number
230 ** of columns. If each indexed column corresponds to a foreign key
231 ** column of pFKey, then this index is a winner. */
234 /* If zKey is NULL, then this foreign key is implicitly mapped to
235 ** the PRIMARY KEY of table pParent. The PRIMARY KEY index may be
236 ** identified by the test. */
237 if( IsPrimaryKeyIndex(pIdx
) ){
240 for(i
=0; i
<nCol
; i
++) aiCol
[i
] = pFKey
->aCol
[i
].iFrom
;
245 /* If zKey is non-NULL, then this foreign key was declared to
246 ** map to an explicit list of columns in table pParent. Check if this
247 ** index matches those columns. Also, check that the index uses
248 ** the default collation sequences for each column. */
250 for(i
=0; i
<nCol
; i
++){
251 i16 iCol
= pIdx
->aiColumn
[i
]; /* Index of column in parent tbl */
252 const char *zDfltColl
; /* Def. collation for column */
253 char *zIdxCol
; /* Name of indexed column */
255 if( iCol
<0 ) break; /* No foreign keys against expression indexes */
257 /* If the index uses a collation sequence that is different from
258 ** the default collation sequence for the column, this index is
259 ** unusable. Bail out early in this case. */
260 zDfltColl
= pParent
->aCol
[iCol
].zColl
;
261 if( !zDfltColl
) zDfltColl
= sqlite3StrBINARY
;
262 if( sqlite3StrICmp(pIdx
->azColl
[i
], zDfltColl
) ) break;
264 zIdxCol
= pParent
->aCol
[iCol
].zName
;
265 for(j
=0; j
<nCol
; j
++){
266 if( sqlite3StrICmp(pFKey
->aCol
[j
].zCol
, zIdxCol
)==0 ){
267 if( aiCol
) aiCol
[i
] = pFKey
->aCol
[j
].iFrom
;
273 if( i
==nCol
) break; /* pIdx is usable */
279 if( !pParse
->disableTriggers
){
280 sqlite3ErrorMsg(pParse
,
281 "foreign key mismatch - \"%w\" referencing \"%w\"",
282 pFKey
->pFrom
->zName
, pFKey
->zTo
);
284 sqlite3DbFree(pParse
->db
, aiCol
);
293 ** This function is called when a row is inserted into or deleted from the
294 ** child table of foreign key constraint pFKey. If an SQL UPDATE is executed
295 ** on the child table of pFKey, this function is invoked twice for each row
296 ** affected - once to "delete" the old row, and then again to "insert" the
299 ** Each time it is called, this function generates VDBE code to locate the
300 ** row in the parent table that corresponds to the row being inserted into
301 ** or deleted from the child table. If the parent row can be found, no
302 ** special action is taken. Otherwise, if the parent row can *not* be
303 ** found in the parent table:
305 ** Operation | FK type | Action taken
306 ** --------------------------------------------------------------------------
307 ** INSERT immediate Increment the "immediate constraint counter".
309 ** DELETE immediate Decrement the "immediate constraint counter".
311 ** INSERT deferred Increment the "deferred constraint counter".
313 ** DELETE deferred Decrement the "deferred constraint counter".
315 ** These operations are identified in the comment at the top of this file
316 ** (fkey.c) as "I.1" and "D.1".
318 static void fkLookupParent(
319 Parse
*pParse
, /* Parse context */
320 int iDb
, /* Index of database housing pTab */
321 Table
*pTab
, /* Parent table of FK pFKey */
322 Index
*pIdx
, /* Unique index on parent key columns in pTab */
323 FKey
*pFKey
, /* Foreign key constraint */
324 int *aiCol
, /* Map from parent key columns to child table columns */
325 int regData
, /* Address of array containing child table row */
326 int nIncr
, /* Increment constraint counter by this */
327 int isIgnore
/* If true, pretend pTab contains all NULL values */
329 int i
; /* Iterator variable */
330 Vdbe
*v
= sqlite3GetVdbe(pParse
); /* Vdbe to add code to */
331 int iCur
= pParse
->nTab
- 1; /* Cursor number to use */
332 int iOk
= sqlite3VdbeMakeLabel(pParse
); /* jump here if parent key found */
334 sqlite3VdbeVerifyAbortable(v
,
336 && !(pParse
->db
->flags
& SQLITE_DeferFKs
)
337 && !pParse
->pToplevel
338 && !pParse
->isMultiWrite
) ? OE_Abort
: OE_Ignore
);
340 /* If nIncr is less than zero, then check at runtime if there are any
341 ** outstanding constraints to resolve. If there are not, there is no need
342 ** to check if deleting this row resolves any outstanding violations.
344 ** Check if any of the key columns in the child table row are NULL. If
345 ** any are, then the constraint is considered satisfied. No need to
346 ** search for a matching row in the parent table. */
348 sqlite3VdbeAddOp2(v
, OP_FkIfZero
, pFKey
->isDeferred
, iOk
);
351 for(i
=0; i
<pFKey
->nCol
; i
++){
352 int iReg
= sqlite3TableColumnToStorage(pFKey
->pFrom
,aiCol
[i
]) + regData
+ 1;
353 sqlite3VdbeAddOp2(v
, OP_IsNull
, iReg
, iOk
); VdbeCoverage(v
);
358 /* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY
359 ** column of the parent table (table pTab). */
360 int iMustBeInt
; /* Address of MustBeInt instruction */
361 int regTemp
= sqlite3GetTempReg(pParse
);
363 /* Invoke MustBeInt to coerce the child key value to an integer (i.e.
364 ** apply the affinity of the parent key). If this fails, then there
365 ** is no matching parent key. Before using MustBeInt, make a copy of
366 ** the value. Otherwise, the value inserted into the child key column
367 ** will have INTEGER affinity applied to it, which may not be correct. */
368 sqlite3VdbeAddOp2(v
, OP_SCopy
,
369 sqlite3TableColumnToStorage(pFKey
->pFrom
,aiCol
[0])+1+regData
, regTemp
);
370 iMustBeInt
= sqlite3VdbeAddOp2(v
, OP_MustBeInt
, regTemp
, 0);
373 /* If the parent table is the same as the child table, and we are about
374 ** to increment the constraint-counter (i.e. this is an INSERT operation),
375 ** then check if the row being inserted matches itself. If so, do not
376 ** increment the constraint-counter. */
377 if( pTab
==pFKey
->pFrom
&& nIncr
==1 ){
378 sqlite3VdbeAddOp3(v
, OP_Eq
, regData
, iOk
, regTemp
); VdbeCoverage(v
);
379 sqlite3VdbeChangeP5(v
, SQLITE_NOTNULL
);
382 sqlite3OpenTable(pParse
, iCur
, iDb
, pTab
, OP_OpenRead
);
383 sqlite3VdbeAddOp3(v
, OP_NotExists
, iCur
, 0, regTemp
); VdbeCoverage(v
);
384 sqlite3VdbeGoto(v
, iOk
);
385 sqlite3VdbeJumpHere(v
, sqlite3VdbeCurrentAddr(v
)-2);
386 sqlite3VdbeJumpHere(v
, iMustBeInt
);
387 sqlite3ReleaseTempReg(pParse
, regTemp
);
389 int nCol
= pFKey
->nCol
;
390 int regTemp
= sqlite3GetTempRange(pParse
, nCol
);
391 int regRec
= sqlite3GetTempReg(pParse
);
393 sqlite3VdbeAddOp3(v
, OP_OpenRead
, iCur
, pIdx
->tnum
, iDb
);
394 sqlite3VdbeSetP4KeyInfo(pParse
, pIdx
);
395 for(i
=0; i
<nCol
; i
++){
396 sqlite3VdbeAddOp2(v
, OP_Copy
,
397 sqlite3TableColumnToStorage(pFKey
->pFrom
, aiCol
[i
])+1+regData
,
401 /* If the parent table is the same as the child table, and we are about
402 ** to increment the constraint-counter (i.e. this is an INSERT operation),
403 ** then check if the row being inserted matches itself. If so, do not
404 ** increment the constraint-counter.
406 ** If any of the parent-key values are NULL, then the row cannot match
407 ** itself. So set JUMPIFNULL to make sure we do the OP_Found if any
408 ** of the parent-key values are NULL (at this point it is known that
409 ** none of the child key values are).
411 if( pTab
==pFKey
->pFrom
&& nIncr
==1 ){
412 int iJump
= sqlite3VdbeCurrentAddr(v
) + nCol
+ 1;
413 for(i
=0; i
<nCol
; i
++){
414 int iChild
= sqlite3TableColumnToStorage(pFKey
->pFrom
,aiCol
[i
])
416 int iParent
= 1+regData
;
417 iParent
+= sqlite3TableColumnToStorage(pIdx
->pTable
,
419 assert( pIdx
->aiColumn
[i
]>=0 );
420 assert( aiCol
[i
]!=pTab
->iPKey
);
421 if( pIdx
->aiColumn
[i
]==pTab
->iPKey
){
422 /* The parent key is a composite key that includes the IPK column */
425 sqlite3VdbeAddOp3(v
, OP_Ne
, iChild
, iJump
, iParent
); VdbeCoverage(v
);
426 sqlite3VdbeChangeP5(v
, SQLITE_JUMPIFNULL
);
428 sqlite3VdbeGoto(v
, iOk
);
431 sqlite3VdbeAddOp4(v
, OP_MakeRecord
, regTemp
, nCol
, regRec
,
432 sqlite3IndexAffinityStr(pParse
->db
,pIdx
), nCol
);
433 sqlite3VdbeAddOp4Int(v
, OP_Found
, iCur
, iOk
, regRec
, 0); VdbeCoverage(v
);
435 sqlite3ReleaseTempReg(pParse
, regRec
);
436 sqlite3ReleaseTempRange(pParse
, regTemp
, nCol
);
440 if( !pFKey
->isDeferred
&& !(pParse
->db
->flags
& SQLITE_DeferFKs
)
441 && !pParse
->pToplevel
442 && !pParse
->isMultiWrite
444 /* Special case: If this is an INSERT statement that will insert exactly
445 ** one row into the table, raise a constraint immediately instead of
446 ** incrementing a counter. This is necessary as the VM code is being
447 ** generated for will not open a statement transaction. */
449 sqlite3HaltConstraint(pParse
, SQLITE_CONSTRAINT_FOREIGNKEY
,
450 OE_Abort
, 0, P4_STATIC
, P5_ConstraintFK
);
452 if( nIncr
>0 && pFKey
->isDeferred
==0 ){
453 sqlite3MayAbort(pParse
);
455 sqlite3VdbeAddOp2(v
, OP_FkCounter
, pFKey
->isDeferred
, nIncr
);
458 sqlite3VdbeResolveLabel(v
, iOk
);
459 sqlite3VdbeAddOp1(v
, OP_Close
, iCur
);
464 ** Return an Expr object that refers to a memory register corresponding
465 ** to column iCol of table pTab.
467 ** regBase is the first of an array of register that contains the data
468 ** for pTab. regBase itself holds the rowid. regBase+1 holds the first
469 ** column. regBase+2 holds the second column, and so forth.
471 static Expr
*exprTableRegister(
472 Parse
*pParse
, /* Parsing and code generating context */
473 Table
*pTab
, /* The table whose content is at r[regBase]... */
474 int regBase
, /* Contents of table pTab */
475 i16 iCol
/* Which column of pTab is desired */
480 sqlite3
*db
= pParse
->db
;
482 pExpr
= sqlite3Expr(db
, TK_REGISTER
, 0);
484 if( iCol
>=0 && iCol
!=pTab
->iPKey
){
485 pCol
= &pTab
->aCol
[iCol
];
486 pExpr
->iTable
= regBase
+ sqlite3TableColumnToStorage(pTab
,iCol
) + 1;
487 pExpr
->affExpr
= pCol
->affinity
;
489 if( zColl
==0 ) zColl
= db
->pDfltColl
->zName
;
490 pExpr
= sqlite3ExprAddCollateString(pParse
, pExpr
, zColl
);
492 pExpr
->iTable
= regBase
;
493 pExpr
->affExpr
= SQLITE_AFF_INTEGER
;
500 ** Return an Expr object that refers to column iCol of table pTab which
503 static Expr
*exprTableColumn(
504 sqlite3
*db
, /* The database connection */
505 Table
*pTab
, /* The table whose column is desired */
506 int iCursor
, /* The open cursor on the table */
507 i16 iCol
/* The column that is wanted */
509 Expr
*pExpr
= sqlite3Expr(db
, TK_COLUMN
, 0);
511 pExpr
->y
.pTab
= pTab
;
512 pExpr
->iTable
= iCursor
;
513 pExpr
->iColumn
= iCol
;
519 ** This function is called to generate code executed when a row is deleted
520 ** from the parent table of foreign key constraint pFKey and, if pFKey is
521 ** deferred, when a row is inserted into the same table. When generating
522 ** code for an SQL UPDATE operation, this function may be called twice -
523 ** once to "delete" the old row and once to "insert" the new row.
525 ** Parameter nIncr is passed -1 when inserting a row (as this may decrease
526 ** the number of FK violations in the db) or +1 when deleting one (as this
527 ** may increase the number of FK constraint problems).
529 ** The code generated by this function scans through the rows in the child
530 ** table that correspond to the parent table row being deleted or inserted.
531 ** For each child row found, one of the following actions is taken:
533 ** Operation | FK type | Action taken
534 ** --------------------------------------------------------------------------
535 ** DELETE immediate Increment the "immediate constraint counter".
536 ** Or, if the ON (UPDATE|DELETE) action is RESTRICT,
537 ** throw a "FOREIGN KEY constraint failed" exception.
539 ** INSERT immediate Decrement the "immediate constraint counter".
541 ** DELETE deferred Increment the "deferred constraint counter".
542 ** Or, if the ON (UPDATE|DELETE) action is RESTRICT,
543 ** throw a "FOREIGN KEY constraint failed" exception.
545 ** INSERT deferred Decrement the "deferred constraint counter".
547 ** These operations are identified in the comment at the top of this file
548 ** (fkey.c) as "I.2" and "D.2".
550 static void fkScanChildren(
551 Parse
*pParse
, /* Parse context */
552 SrcList
*pSrc
, /* The child table to be scanned */
553 Table
*pTab
, /* The parent table */
554 Index
*pIdx
, /* Index on parent covering the foreign key */
555 FKey
*pFKey
, /* The foreign key linking pSrc to pTab */
556 int *aiCol
, /* Map from pIdx cols to child table cols */
557 int regData
, /* Parent row data starts here */
558 int nIncr
/* Amount to increment deferred counter by */
560 sqlite3
*db
= pParse
->db
; /* Database handle */
561 int i
; /* Iterator variable */
562 Expr
*pWhere
= 0; /* WHERE clause to scan with */
563 NameContext sNameContext
; /* Context used to resolve WHERE clause */
564 WhereInfo
*pWInfo
; /* Context used by sqlite3WhereXXX() */
565 int iFkIfZero
= 0; /* Address of OP_FkIfZero */
566 Vdbe
*v
= sqlite3GetVdbe(pParse
);
568 assert( pIdx
==0 || pIdx
->pTable
==pTab
);
569 assert( pIdx
==0 || pIdx
->nKeyCol
==pFKey
->nCol
);
570 assert( pIdx
!=0 || pFKey
->nCol
==1 );
571 assert( pIdx
!=0 || HasRowid(pTab
) );
574 iFkIfZero
= sqlite3VdbeAddOp2(v
, OP_FkIfZero
, pFKey
->isDeferred
, 0);
578 /* Create an Expr object representing an SQL expression like:
580 ** <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
582 ** The collation sequence used for the comparison should be that of
583 ** the parent key columns. The affinity of the parent key column should
584 ** be applied to each child key value before the comparison takes place.
586 for(i
=0; i
<pFKey
->nCol
; i
++){
587 Expr
*pLeft
; /* Value from parent table row */
588 Expr
*pRight
; /* Column ref to child table */
589 Expr
*pEq
; /* Expression (pLeft = pRight) */
590 i16 iCol
; /* Index of column in child table */
591 const char *zCol
; /* Name of column in child table */
593 iCol
= pIdx
? pIdx
->aiColumn
[i
] : -1;
594 pLeft
= exprTableRegister(pParse
, pTab
, regData
, iCol
);
595 iCol
= aiCol
? aiCol
[i
] : pFKey
->aCol
[0].iFrom
;
597 zCol
= pFKey
->pFrom
->aCol
[iCol
].zName
;
598 pRight
= sqlite3Expr(db
, TK_ID
, zCol
);
599 pEq
= sqlite3PExpr(pParse
, TK_EQ
, pLeft
, pRight
);
600 pWhere
= sqlite3ExprAnd(pParse
, pWhere
, pEq
);
603 /* If the child table is the same as the parent table, then add terms
604 ** to the WHERE clause that prevent this entry from being scanned.
605 ** The added WHERE clause terms are like this:
607 ** $current_rowid!=rowid
608 ** NOT( $current_a==a AND $current_b==b AND ... )
610 ** The first form is used for rowid tables. The second form is used
611 ** for WITHOUT ROWID tables. In the second form, the *parent* key is
612 ** (a,b,...). Either the parent or primary key could be used to
613 ** uniquely identify the current row, but the parent key is more convenient
614 ** as the required values have already been loaded into registers
617 if( pTab
==pFKey
->pFrom
&& nIncr
>0 ){
618 Expr
*pNe
; /* Expression (pLeft != pRight) */
619 Expr
*pLeft
; /* Value from parent table row */
620 Expr
*pRight
; /* Column ref to child table */
621 if( HasRowid(pTab
) ){
622 pLeft
= exprTableRegister(pParse
, pTab
, regData
, -1);
623 pRight
= exprTableColumn(db
, pTab
, pSrc
->a
[0].iCursor
, -1);
624 pNe
= sqlite3PExpr(pParse
, TK_NE
, pLeft
, pRight
);
626 Expr
*pEq
, *pAll
= 0;
628 for(i
=0; i
<pIdx
->nKeyCol
; i
++){
629 i16 iCol
= pIdx
->aiColumn
[i
];
631 pLeft
= exprTableRegister(pParse
, pTab
, regData
, iCol
);
632 pRight
= sqlite3Expr(db
, TK_ID
, pTab
->aCol
[iCol
].zName
);
633 pEq
= sqlite3PExpr(pParse
, TK_IS
, pLeft
, pRight
);
634 pAll
= sqlite3ExprAnd(pParse
, pAll
, pEq
);
636 pNe
= sqlite3PExpr(pParse
, TK_NOT
, pAll
, 0);
638 pWhere
= sqlite3ExprAnd(pParse
, pWhere
, pNe
);
641 /* Resolve the references in the WHERE clause. */
642 memset(&sNameContext
, 0, sizeof(NameContext
));
643 sNameContext
.pSrcList
= pSrc
;
644 sNameContext
.pParse
= pParse
;
645 sqlite3ResolveExprNames(&sNameContext
, pWhere
);
647 /* Create VDBE to loop through the entries in pSrc that match the WHERE
648 ** clause. For each row found, increment either the deferred or immediate
649 ** foreign key constraint counter. */
650 if( pParse
->nErr
==0 ){
651 pWInfo
= sqlite3WhereBegin(pParse
, pSrc
, pWhere
, 0, 0, 0, 0);
652 sqlite3VdbeAddOp2(v
, OP_FkCounter
, pFKey
->isDeferred
, nIncr
);
654 sqlite3WhereEnd(pWInfo
);
658 /* Clean up the WHERE clause constructed above. */
659 sqlite3ExprDelete(db
, pWhere
);
661 sqlite3VdbeJumpHereOrPopInst(v
, iFkIfZero
);
666 ** This function returns a linked list of FKey objects (connected by
667 ** FKey.pNextTo) holding all children of table pTab. For example,
668 ** given the following schema:
670 ** CREATE TABLE t1(a PRIMARY KEY);
671 ** CREATE TABLE t2(b REFERENCES t1(a);
673 ** Calling this function with table "t1" as an argument returns a pointer
674 ** to the FKey structure representing the foreign key constraint on table
675 ** "t2". Calling this function with "t2" as the argument would return a
676 ** NULL pointer (as there are no FK constraints for which t2 is the parent
679 FKey
*sqlite3FkReferences(Table
*pTab
){
680 return (FKey
*)sqlite3HashFind(&pTab
->pSchema
->fkeyHash
, pTab
->zName
);
684 ** The second argument is a Trigger structure allocated by the
685 ** fkActionTrigger() routine. This function deletes the Trigger structure
686 ** and all of its sub-components.
688 ** The Trigger structure or any of its sub-components may be allocated from
689 ** the lookaside buffer belonging to database handle dbMem.
691 static void fkTriggerDelete(sqlite3
*dbMem
, Trigger
*p
){
693 TriggerStep
*pStep
= p
->step_list
;
694 sqlite3ExprDelete(dbMem
, pStep
->pWhere
);
695 sqlite3ExprListDelete(dbMem
, pStep
->pExprList
);
696 sqlite3SelectDelete(dbMem
, pStep
->pSelect
);
697 sqlite3ExprDelete(dbMem
, p
->pWhen
);
698 sqlite3DbFree(dbMem
, p
);
703 ** This function is called to generate code that runs when table pTab is
704 ** being dropped from the database. The SrcList passed as the second argument
705 ** to this function contains a single entry guaranteed to resolve to
708 ** Normally, no code is required. However, if either
710 ** (a) The table is the parent table of a FK constraint, or
711 ** (b) The table is the child table of a deferred FK constraint and it is
712 ** determined at runtime that there are outstanding deferred FK
713 ** constraint violations in the database,
715 ** then the equivalent of "DELETE FROM <tbl>" is executed before dropping
716 ** the table from the database. Triggers are disabled while running this
717 ** DELETE, but foreign key actions are not.
719 void sqlite3FkDropTable(Parse
*pParse
, SrcList
*pName
, Table
*pTab
){
720 sqlite3
*db
= pParse
->db
;
721 if( (db
->flags
&SQLITE_ForeignKeys
) && !IsVirtual(pTab
) ){
723 Vdbe
*v
= sqlite3GetVdbe(pParse
);
725 assert( v
); /* VDBE has already been allocated */
726 assert( pTab
->pSelect
==0 ); /* Not a view */
727 if( sqlite3FkReferences(pTab
)==0 ){
728 /* Search for a deferred foreign key constraint for which this table
729 ** is the child table. If one cannot be found, return without
730 ** generating any VDBE code. If one can be found, then jump over
731 ** the entire DELETE if there are no outstanding deferred constraints
732 ** when this statement is run. */
734 for(p
=pTab
->pFKey
; p
; p
=p
->pNextFrom
){
735 if( p
->isDeferred
|| (db
->flags
& SQLITE_DeferFKs
) ) break;
738 iSkip
= sqlite3VdbeMakeLabel(pParse
);
739 sqlite3VdbeAddOp2(v
, OP_FkIfZero
, 1, iSkip
); VdbeCoverage(v
);
742 pParse
->disableTriggers
= 1;
743 sqlite3DeleteFrom(pParse
, sqlite3SrcListDup(db
, pName
, 0), 0, 0, 0);
744 pParse
->disableTriggers
= 0;
746 /* If the DELETE has generated immediate foreign key constraint
747 ** violations, halt the VDBE and return an error at this point, before
748 ** any modifications to the schema are made. This is because statement
749 ** transactions are not able to rollback schema changes.
751 ** If the SQLITE_DeferFKs flag is set, then this is not required, as
752 ** the statement transaction will not be rolled back even if FK
753 ** constraints are violated.
755 if( (db
->flags
& SQLITE_DeferFKs
)==0 ){
756 sqlite3VdbeVerifyAbortable(v
, OE_Abort
);
757 sqlite3VdbeAddOp2(v
, OP_FkIfZero
, 0, sqlite3VdbeCurrentAddr(v
)+2);
759 sqlite3HaltConstraint(pParse
, SQLITE_CONSTRAINT_FOREIGNKEY
,
760 OE_Abort
, 0, P4_STATIC
, P5_ConstraintFK
);
764 sqlite3VdbeResolveLabel(v
, iSkip
);
771 ** The second argument points to an FKey object representing a foreign key
772 ** for which pTab is the child table. An UPDATE statement against pTab
773 ** is currently being processed. For each column of the table that is
774 ** actually updated, the corresponding element in the aChange[] array
775 ** is zero or greater (if a column is unmodified the corresponding element
776 ** is set to -1). If the rowid column is modified by the UPDATE statement
777 ** the bChngRowid argument is non-zero.
779 ** This function returns true if any of the columns that are part of the
780 ** child key for FK constraint *p are modified.
782 static int fkChildIsModified(
783 Table
*pTab
, /* Table being updated */
784 FKey
*p
, /* Foreign key for which pTab is the child */
785 int *aChange
, /* Array indicating modified columns */
786 int bChngRowid
/* True if rowid is modified by this update */
789 for(i
=0; i
<p
->nCol
; i
++){
790 int iChildKey
= p
->aCol
[i
].iFrom
;
791 if( aChange
[iChildKey
]>=0 ) return 1;
792 if( iChildKey
==pTab
->iPKey
&& bChngRowid
) return 1;
798 ** The second argument points to an FKey object representing a foreign key
799 ** for which pTab is the parent table. An UPDATE statement against pTab
800 ** is currently being processed. For each column of the table that is
801 ** actually updated, the corresponding element in the aChange[] array
802 ** is zero or greater (if a column is unmodified the corresponding element
803 ** is set to -1). If the rowid column is modified by the UPDATE statement
804 ** the bChngRowid argument is non-zero.
806 ** This function returns true if any of the columns that are part of the
807 ** parent key for FK constraint *p are modified.
809 static int fkParentIsModified(
816 for(i
=0; i
<p
->nCol
; i
++){
817 char *zKey
= p
->aCol
[i
].zCol
;
819 for(iKey
=0; iKey
<pTab
->nCol
; iKey
++){
820 if( aChange
[iKey
]>=0 || (iKey
==pTab
->iPKey
&& bChngRowid
) ){
821 Column
*pCol
= &pTab
->aCol
[iKey
];
823 if( 0==sqlite3StrICmp(pCol
->zName
, zKey
) ) return 1;
824 }else if( pCol
->colFlags
& COLFLAG_PRIMKEY
){
834 ** Return true if the parser passed as the first argument is being
835 ** used to code a trigger that is really a "SET NULL" action belonging
838 static int isSetNullAction(Parse
*pParse
, FKey
*pFKey
){
839 Parse
*pTop
= sqlite3ParseToplevel(pParse
);
840 if( pTop
->pTriggerPrg
){
841 Trigger
*p
= pTop
->pTriggerPrg
->pTrigger
;
842 if( (p
==pFKey
->apTrigger
[0] && pFKey
->aAction
[0]==OE_SetNull
)
843 || (p
==pFKey
->apTrigger
[1] && pFKey
->aAction
[1]==OE_SetNull
)
852 ** This function is called when inserting, deleting or updating a row of
853 ** table pTab to generate VDBE code to perform foreign key constraint
854 ** processing for the operation.
856 ** For a DELETE operation, parameter regOld is passed the index of the
857 ** first register in an array of (pTab->nCol+1) registers containing the
858 ** rowid of the row being deleted, followed by each of the column values
859 ** of the row being deleted, from left to right. Parameter regNew is passed
860 ** zero in this case.
862 ** For an INSERT operation, regOld is passed zero and regNew is passed the
863 ** first register of an array of (pTab->nCol+1) registers containing the new
866 ** For an UPDATE operation, this function is called twice. Once before
867 ** the original record is deleted from the table using the calling convention
868 ** described for DELETE. Then again after the original record is deleted
869 ** but before the new record is inserted using the INSERT convention.
872 Parse
*pParse
, /* Parse context */
873 Table
*pTab
, /* Row is being deleted from this table */
874 int regOld
, /* Previous row data is stored here */
875 int regNew
, /* New row data is stored here */
876 int *aChange
, /* Array indicating UPDATEd columns (or 0) */
877 int bChngRowid
/* True if rowid is UPDATEd */
879 sqlite3
*db
= pParse
->db
; /* Database handle */
880 FKey
*pFKey
; /* Used to iterate through FKs */
881 int iDb
; /* Index of database containing pTab */
882 const char *zDb
; /* Name of database containing pTab */
883 int isIgnoreErrors
= pParse
->disableTriggers
;
885 /* Exactly one of regOld and regNew should be non-zero. */
886 assert( (regOld
==0)!=(regNew
==0) );
888 /* If foreign-keys are disabled, this function is a no-op. */
889 if( (db
->flags
&SQLITE_ForeignKeys
)==0 ) return;
891 iDb
= sqlite3SchemaToIndex(db
, pTab
->pSchema
);
892 zDb
= db
->aDb
[iDb
].zDbSName
;
894 /* Loop through all the foreign key constraints for which pTab is the
895 ** child table (the table that the foreign key definition is part of). */
896 for(pFKey
=pTab
->pFKey
; pFKey
; pFKey
=pFKey
->pNextFrom
){
897 Table
*pTo
; /* Parent table of foreign key pFKey */
898 Index
*pIdx
= 0; /* Index on key columns in pTo */
906 && sqlite3_stricmp(pTab
->zName
, pFKey
->zTo
)!=0
907 && fkChildIsModified(pTab
, pFKey
, aChange
, bChngRowid
)==0
912 /* Find the parent table of this foreign key. Also find a unique index
913 ** on the parent key columns in the parent table. If either of these
914 ** schema items cannot be located, set an error in pParse and return
916 if( pParse
->disableTriggers
){
917 pTo
= sqlite3FindTable(db
, pFKey
->zTo
, zDb
);
919 pTo
= sqlite3LocateTable(pParse
, 0, pFKey
->zTo
, zDb
);
921 if( !pTo
|| sqlite3FkLocateIndex(pParse
, pTo
, pFKey
, &pIdx
, &aiFree
) ){
922 assert( isIgnoreErrors
==0 || (regOld
!=0 && regNew
==0) );
923 if( !isIgnoreErrors
|| db
->mallocFailed
) return;
925 /* If isIgnoreErrors is true, then a table is being dropped. In this
926 ** case SQLite runs a "DELETE FROM xxx" on the table being dropped
927 ** before actually dropping it in order to check FK constraints.
928 ** If the parent table of an FK constraint on the current table is
929 ** missing, behave as if it is empty. i.e. decrement the relevant
930 ** FK counter for each row of the current table with non-NULL keys.
932 Vdbe
*v
= sqlite3GetVdbe(pParse
);
933 int iJump
= sqlite3VdbeCurrentAddr(v
) + pFKey
->nCol
+ 1;
934 for(i
=0; i
<pFKey
->nCol
; i
++){
936 iFromCol
= pFKey
->aCol
[i
].iFrom
;
937 iReg
= sqlite3TableColumnToStorage(pFKey
->pFrom
,iFromCol
) + regOld
+1;
938 sqlite3VdbeAddOp2(v
, OP_IsNull
, iReg
, iJump
); VdbeCoverage(v
);
940 sqlite3VdbeAddOp2(v
, OP_FkCounter
, pFKey
->isDeferred
, -1);
944 assert( pFKey
->nCol
==1 || (aiFree
&& pIdx
) );
949 iCol
= pFKey
->aCol
[0].iFrom
;
952 for(i
=0; i
<pFKey
->nCol
; i
++){
953 if( aiCol
[i
]==pTab
->iPKey
){
956 assert( pIdx
==0 || pIdx
->aiColumn
[i
]>=0 );
957 #ifndef SQLITE_OMIT_AUTHORIZATION
958 /* Request permission to read the parent key columns. If the
959 ** authorization callback returns SQLITE_IGNORE, behave as if any
960 ** values read from the parent table are NULL. */
963 char *zCol
= pTo
->aCol
[pIdx
? pIdx
->aiColumn
[i
] : pTo
->iPKey
].zName
;
964 rcauth
= sqlite3AuthReadCol(pParse
, pTo
->zName
, zCol
, iDb
);
965 bIgnore
= (rcauth
==SQLITE_IGNORE
);
970 /* Take a shared-cache advisory read-lock on the parent table. Allocate
971 ** a cursor to use to search the unique index on the parent key columns
972 ** in the parent table. */
973 sqlite3TableLock(pParse
, iDb
, pTo
->tnum
, 0, pTo
->zName
);
977 /* A row is being removed from the child table. Search for the parent.
978 ** If the parent does not exist, removing the child row resolves an
979 ** outstanding foreign key constraint violation. */
980 fkLookupParent(pParse
, iDb
, pTo
, pIdx
, pFKey
, aiCol
, regOld
, -1, bIgnore
);
982 if( regNew
!=0 && !isSetNullAction(pParse
, pFKey
) ){
983 /* A row is being added to the child table. If a parent row cannot
984 ** be found, adding the child row has violated the FK constraint.
986 ** If this operation is being performed as part of a trigger program
987 ** that is actually a "SET NULL" action belonging to this very
988 ** foreign key, then omit this scan altogether. As all child key
989 ** values are guaranteed to be NULL, it is not possible for adding
990 ** this row to cause an FK violation. */
991 fkLookupParent(pParse
, iDb
, pTo
, pIdx
, pFKey
, aiCol
, regNew
, +1, bIgnore
);
994 sqlite3DbFree(db
, aiFree
);
997 /* Loop through all the foreign key constraints that refer to this table.
998 ** (the "child" constraints) */
999 for(pFKey
= sqlite3FkReferences(pTab
); pFKey
; pFKey
=pFKey
->pNextTo
){
1000 Index
*pIdx
= 0; /* Foreign key index for pFKey */
1004 if( aChange
&& fkParentIsModified(pTab
, pFKey
, aChange
, bChngRowid
)==0 ){
1008 if( !pFKey
->isDeferred
&& !(db
->flags
& SQLITE_DeferFKs
)
1009 && !pParse
->pToplevel
&& !pParse
->isMultiWrite
1011 assert( regOld
==0 && regNew
!=0 );
1012 /* Inserting a single row into a parent table cannot cause (or fix)
1013 ** an immediate foreign key violation. So do nothing in this case. */
1017 if( sqlite3FkLocateIndex(pParse
, pTab
, pFKey
, &pIdx
, &aiCol
) ){
1018 if( !isIgnoreErrors
|| db
->mallocFailed
) return;
1021 assert( aiCol
|| pFKey
->nCol
==1 );
1023 /* Create a SrcList structure containing the child table. We need the
1024 ** child table as a SrcList for sqlite3WhereBegin() */
1025 pSrc
= sqlite3SrcListAppend(pParse
, 0, 0, 0);
1027 SrcItem
*pItem
= pSrc
->a
;
1028 pItem
->pTab
= pFKey
->pFrom
;
1029 pItem
->zName
= pFKey
->pFrom
->zName
;
1030 pItem
->pTab
->nTabRef
++;
1031 pItem
->iCursor
= pParse
->nTab
++;
1034 fkScanChildren(pParse
, pSrc
, pTab
, pIdx
, pFKey
, aiCol
, regNew
, -1);
1037 int eAction
= pFKey
->aAction
[aChange
!=0];
1038 fkScanChildren(pParse
, pSrc
, pTab
, pIdx
, pFKey
, aiCol
, regOld
, 1);
1039 /* If this is a deferred FK constraint, or a CASCADE or SET NULL
1040 ** action applies, then any foreign key violations caused by
1041 ** removing the parent key will be rectified by the action trigger.
1042 ** So do not set the "may-abort" flag in this case.
1044 ** Note 1: If the FK is declared "ON UPDATE CASCADE", then the
1045 ** may-abort flag will eventually be set on this statement anyway
1046 ** (when this function is called as part of processing the UPDATE
1047 ** within the action trigger).
1049 ** Note 2: At first glance it may seem like SQLite could simply omit
1050 ** all OP_FkCounter related scans when either CASCADE or SET NULL
1051 ** applies. The trouble starts if the CASCADE or SET NULL action
1052 ** trigger causes other triggers or action rules attached to the
1053 ** child table to fire. In these cases the fk constraint counters
1054 ** might be set incorrectly if any OP_FkCounter related scans are
1056 if( !pFKey
->isDeferred
&& eAction
!=OE_Cascade
&& eAction
!=OE_SetNull
){
1057 sqlite3MayAbort(pParse
);
1061 sqlite3SrcListDelete(db
, pSrc
);
1063 sqlite3DbFree(db
, aiCol
);
1067 #define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
1070 ** This function is called before generating code to update or delete a
1071 ** row contained in table pTab.
1073 u32
sqlite3FkOldmask(
1074 Parse
*pParse
, /* Parse context */
1075 Table
*pTab
/* Table being modified */
1078 if( pParse
->db
->flags
&SQLITE_ForeignKeys
){
1081 for(p
=pTab
->pFKey
; p
; p
=p
->pNextFrom
){
1082 for(i
=0; i
<p
->nCol
; i
++) mask
|= COLUMN_MASK(p
->aCol
[i
].iFrom
);
1084 for(p
=sqlite3FkReferences(pTab
); p
; p
=p
->pNextTo
){
1086 sqlite3FkLocateIndex(pParse
, pTab
, p
, &pIdx
, 0);
1088 for(i
=0; i
<pIdx
->nKeyCol
; i
++){
1089 assert( pIdx
->aiColumn
[i
]>=0 );
1090 mask
|= COLUMN_MASK(pIdx
->aiColumn
[i
]);
1100 ** This function is called before generating code to update or delete a
1101 ** row contained in table pTab. If the operation is a DELETE, then
1102 ** parameter aChange is passed a NULL value. For an UPDATE, aChange points
1103 ** to an array of size N, where N is the number of columns in table pTab.
1104 ** If the i'th column is not modified by the UPDATE, then the corresponding
1105 ** entry in the aChange[] array is set to -1. If the column is modified,
1106 ** the value is 0 or greater. Parameter chngRowid is set to true if the
1107 ** UPDATE statement modifies the rowid fields of the table.
1109 ** If any foreign key processing will be required, this function returns
1110 ** non-zero. If there is no foreign key related processing, this function
1113 ** For an UPDATE, this function returns 2 if:
1115 ** * There are any FKs for which pTab is the child and the parent table
1116 ** and any FK processing at all is required (even of a different FK), or
1118 ** * the UPDATE modifies one or more parent keys for which the action is
1119 ** not "NO ACTION" (i.e. is CASCADE, SET DEFAULT or SET NULL).
1121 ** Or, assuming some other foreign key processing is required, 1.
1123 int sqlite3FkRequired(
1124 Parse
*pParse
, /* Parse context */
1125 Table
*pTab
, /* Table being modified */
1126 int *aChange
, /* Non-NULL for UPDATE operations */
1127 int chngRowid
/* True for UPDATE that affects rowid */
1129 int eRet
= 1; /* Value to return if bHaveFK is true */
1130 int bHaveFK
= 0; /* If FK processing is required */
1131 if( pParse
->db
->flags
&SQLITE_ForeignKeys
){
1133 /* A DELETE operation. Foreign key processing is required if the
1134 ** table in question is either the child or parent table for any
1135 ** foreign key constraint. */
1136 bHaveFK
= (sqlite3FkReferences(pTab
) || pTab
->pFKey
);
1138 /* This is an UPDATE. Foreign key processing is only required if the
1139 ** operation modifies one or more child or parent key columns. */
1142 /* Check if any child key columns are being modified. */
1143 for(p
=pTab
->pFKey
; p
; p
=p
->pNextFrom
){
1144 if( fkChildIsModified(pTab
, p
, aChange
, chngRowid
) ){
1145 if( 0==sqlite3_stricmp(pTab
->zName
, p
->zTo
) ) eRet
= 2;
1150 /* Check if any parent key columns are being modified. */
1151 for(p
=sqlite3FkReferences(pTab
); p
; p
=p
->pNextTo
){
1152 if( fkParentIsModified(pTab
, p
, aChange
, chngRowid
) ){
1153 if( p
->aAction
[1]!=OE_None
) return 2;
1159 return bHaveFK
? eRet
: 0;
1163 ** This function is called when an UPDATE or DELETE operation is being
1164 ** compiled on table pTab, which is the parent table of foreign-key pFKey.
1165 ** If the current operation is an UPDATE, then the pChanges parameter is
1166 ** passed a pointer to the list of columns being modified. If it is a
1167 ** DELETE, pChanges is passed a NULL pointer.
1169 ** It returns a pointer to a Trigger structure containing a trigger
1170 ** equivalent to the ON UPDATE or ON DELETE action specified by pFKey.
1171 ** If the action is "NO ACTION" or "RESTRICT", then a NULL pointer is
1172 ** returned (these actions require no special handling by the triggers
1173 ** sub-system, code for them is created by fkScanChildren()).
1175 ** For example, if pFKey is the foreign key and pTab is table "p" in
1176 ** the following schema:
1178 ** CREATE TABLE p(pk PRIMARY KEY);
1179 ** CREATE TABLE c(ck REFERENCES p ON DELETE CASCADE);
1181 ** then the returned trigger structure is equivalent to:
1183 ** CREATE TRIGGER ... DELETE ON p BEGIN
1184 ** DELETE FROM c WHERE ck = old.pk;
1187 ** The returned pointer is cached as part of the foreign key object. It
1188 ** is eventually freed along with the rest of the foreign key object by
1189 ** sqlite3FkDelete().
1191 static Trigger
*fkActionTrigger(
1192 Parse
*pParse
, /* Parse context */
1193 Table
*pTab
, /* Table being updated or deleted from */
1194 FKey
*pFKey
, /* Foreign key to get action for */
1195 ExprList
*pChanges
/* Change-list for UPDATE, NULL for DELETE */
1197 sqlite3
*db
= pParse
->db
; /* Database handle */
1198 int action
; /* One of OE_None, OE_Cascade etc. */
1199 Trigger
*pTrigger
; /* Trigger definition to return */
1200 int iAction
= (pChanges
!=0); /* 1 for UPDATE, 0 for DELETE */
1202 action
= pFKey
->aAction
[iAction
];
1203 if( action
==OE_Restrict
&& (db
->flags
& SQLITE_DeferFKs
) ){
1206 pTrigger
= pFKey
->apTrigger
[iAction
];
1208 if( action
!=OE_None
&& !pTrigger
){
1209 char const *zFrom
; /* Name of child table */
1210 int nFrom
; /* Length in bytes of zFrom */
1211 Index
*pIdx
= 0; /* Parent key index for this FK */
1212 int *aiCol
= 0; /* child table cols -> parent key cols */
1213 TriggerStep
*pStep
= 0; /* First (only) step of trigger program */
1214 Expr
*pWhere
= 0; /* WHERE clause of trigger step */
1215 ExprList
*pList
= 0; /* Changes list if ON UPDATE CASCADE */
1216 Select
*pSelect
= 0; /* If RESTRICT, "SELECT RAISE(...)" */
1217 int i
; /* Iterator variable */
1218 Expr
*pWhen
= 0; /* WHEN clause for the trigger */
1220 if( sqlite3FkLocateIndex(pParse
, pTab
, pFKey
, &pIdx
, &aiCol
) ) return 0;
1221 assert( aiCol
|| pFKey
->nCol
==1 );
1223 for(i
=0; i
<pFKey
->nCol
; i
++){
1224 Token tOld
= { "old", 3 }; /* Literal "old" token */
1225 Token tNew
= { "new", 3 }; /* Literal "new" token */
1226 Token tFromCol
; /* Name of column in child table */
1227 Token tToCol
; /* Name of column in parent table */
1228 int iFromCol
; /* Idx of column in child table */
1229 Expr
*pEq
; /* tFromCol = OLD.tToCol */
1231 iFromCol
= aiCol
? aiCol
[i
] : pFKey
->aCol
[0].iFrom
;
1232 assert( iFromCol
>=0 );
1233 assert( pIdx
!=0 || (pTab
->iPKey
>=0 && pTab
->iPKey
<pTab
->nCol
) );
1234 assert( pIdx
==0 || pIdx
->aiColumn
[i
]>=0 );
1235 sqlite3TokenInit(&tToCol
,
1236 pTab
->aCol
[pIdx
? pIdx
->aiColumn
[i
] : pTab
->iPKey
].zName
);
1237 sqlite3TokenInit(&tFromCol
, pFKey
->pFrom
->aCol
[iFromCol
].zName
);
1239 /* Create the expression "OLD.zToCol = zFromCol". It is important
1240 ** that the "OLD.zToCol" term is on the LHS of the = operator, so
1241 ** that the affinity and collation sequence associated with the
1242 ** parent table are used for the comparison. */
1243 pEq
= sqlite3PExpr(pParse
, TK_EQ
,
1244 sqlite3PExpr(pParse
, TK_DOT
,
1245 sqlite3ExprAlloc(db
, TK_ID
, &tOld
, 0),
1246 sqlite3ExprAlloc(db
, TK_ID
, &tToCol
, 0)),
1247 sqlite3ExprAlloc(db
, TK_ID
, &tFromCol
, 0)
1249 pWhere
= sqlite3ExprAnd(pParse
, pWhere
, pEq
);
1251 /* For ON UPDATE, construct the next term of the WHEN clause.
1252 ** The final WHEN clause will be like this:
1254 ** WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN)
1257 pEq
= sqlite3PExpr(pParse
, TK_IS
,
1258 sqlite3PExpr(pParse
, TK_DOT
,
1259 sqlite3ExprAlloc(db
, TK_ID
, &tOld
, 0),
1260 sqlite3ExprAlloc(db
, TK_ID
, &tToCol
, 0)),
1261 sqlite3PExpr(pParse
, TK_DOT
,
1262 sqlite3ExprAlloc(db
, TK_ID
, &tNew
, 0),
1263 sqlite3ExprAlloc(db
, TK_ID
, &tToCol
, 0))
1265 pWhen
= sqlite3ExprAnd(pParse
, pWhen
, pEq
);
1268 if( action
!=OE_Restrict
&& (action
!=OE_Cascade
|| pChanges
) ){
1270 if( action
==OE_Cascade
){
1271 pNew
= sqlite3PExpr(pParse
, TK_DOT
,
1272 sqlite3ExprAlloc(db
, TK_ID
, &tNew
, 0),
1273 sqlite3ExprAlloc(db
, TK_ID
, &tToCol
, 0));
1274 }else if( action
==OE_SetDflt
){
1275 Column
*pCol
= pFKey
->pFrom
->aCol
+ iFromCol
;
1277 if( pCol
->colFlags
& COLFLAG_GENERATED
){
1278 testcase( pCol
->colFlags
& COLFLAG_VIRTUAL
);
1279 testcase( pCol
->colFlags
& COLFLAG_STORED
);
1282 pDflt
= pCol
->pDflt
;
1285 pNew
= sqlite3ExprDup(db
, pDflt
, 0);
1287 pNew
= sqlite3ExprAlloc(db
, TK_NULL
, 0, 0);
1290 pNew
= sqlite3ExprAlloc(db
, TK_NULL
, 0, 0);
1292 pList
= sqlite3ExprListAppend(pParse
, pList
, pNew
);
1293 sqlite3ExprListSetName(pParse
, pList
, &tFromCol
, 0);
1296 sqlite3DbFree(db
, aiCol
);
1298 zFrom
= pFKey
->pFrom
->zName
;
1299 nFrom
= sqlite3Strlen30(zFrom
);
1301 if( action
==OE_Restrict
){
1307 pRaise
= sqlite3Expr(db
, TK_RAISE
, "FOREIGN KEY constraint failed");
1309 pRaise
->affExpr
= OE_Abort
;
1311 pSelect
= sqlite3SelectNew(pParse
,
1312 sqlite3ExprListAppend(pParse
, 0, pRaise
),
1313 sqlite3SrcListAppend(pParse
, 0, &tFrom
, 0),
1320 /* Disable lookaside memory allocation */
1323 pTrigger
= (Trigger
*)sqlite3DbMallocZero(db
,
1324 sizeof(Trigger
) + /* struct Trigger */
1325 sizeof(TriggerStep
) + /* Single step in trigger program */
1326 nFrom
+ 1 /* Space for pStep->zTarget */
1329 pStep
= pTrigger
->step_list
= (TriggerStep
*)&pTrigger
[1];
1330 pStep
->zTarget
= (char *)&pStep
[1];
1331 memcpy((char *)pStep
->zTarget
, zFrom
, nFrom
);
1333 pStep
->pWhere
= sqlite3ExprDup(db
, pWhere
, EXPRDUP_REDUCE
);
1334 pStep
->pExprList
= sqlite3ExprListDup(db
, pList
, EXPRDUP_REDUCE
);
1335 pStep
->pSelect
= sqlite3SelectDup(db
, pSelect
, EXPRDUP_REDUCE
);
1337 pWhen
= sqlite3PExpr(pParse
, TK_NOT
, pWhen
, 0);
1338 pTrigger
->pWhen
= sqlite3ExprDup(db
, pWhen
, EXPRDUP_REDUCE
);
1342 /* Re-enable the lookaside buffer, if it was disabled earlier. */
1345 sqlite3ExprDelete(db
, pWhere
);
1346 sqlite3ExprDelete(db
, pWhen
);
1347 sqlite3ExprListDelete(db
, pList
);
1348 sqlite3SelectDelete(db
, pSelect
);
1349 if( db
->mallocFailed
==1 ){
1350 fkTriggerDelete(db
, pTrigger
);
1354 assert( pTrigger
!=0 );
1358 pStep
->op
= TK_SELECT
;
1362 pStep
->op
= TK_DELETE
;
1365 /* no break */ deliberate_fall_through
1367 pStep
->op
= TK_UPDATE
;
1369 pStep
->pTrig
= pTrigger
;
1370 pTrigger
->pSchema
= pTab
->pSchema
;
1371 pTrigger
->pTabSchema
= pTab
->pSchema
;
1372 pFKey
->apTrigger
[iAction
] = pTrigger
;
1373 pTrigger
->op
= (pChanges
? TK_UPDATE
: TK_DELETE
);
1380 ** This function is called when deleting or updating a row to implement
1381 ** any required CASCADE, SET NULL or SET DEFAULT actions.
1383 void sqlite3FkActions(
1384 Parse
*pParse
, /* Parse context */
1385 Table
*pTab
, /* Table being updated or deleted from */
1386 ExprList
*pChanges
, /* Change-list for UPDATE, NULL for DELETE */
1387 int regOld
, /* Address of array containing old row */
1388 int *aChange
, /* Array indicating UPDATEd columns (or 0) */
1389 int bChngRowid
/* True if rowid is UPDATEd */
1391 /* If foreign-key support is enabled, iterate through all FKs that
1392 ** refer to table pTab. If there is an action associated with the FK
1393 ** for this operation (either update or delete), invoke the associated
1394 ** trigger sub-program. */
1395 if( pParse
->db
->flags
&SQLITE_ForeignKeys
){
1396 FKey
*pFKey
; /* Iterator variable */
1397 for(pFKey
= sqlite3FkReferences(pTab
); pFKey
; pFKey
=pFKey
->pNextTo
){
1398 if( aChange
==0 || fkParentIsModified(pTab
, pFKey
, aChange
, bChngRowid
) ){
1399 Trigger
*pAct
= fkActionTrigger(pParse
, pTab
, pFKey
, pChanges
);
1401 sqlite3CodeRowTriggerDirect(pParse
, pAct
, pTab
, regOld
, OE_Abort
, 0);
1408 #endif /* ifndef SQLITE_OMIT_TRIGGER */
1411 ** Free all memory associated with foreign key definitions attached to
1412 ** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash
1415 void sqlite3FkDelete(sqlite3
*db
, Table
*pTab
){
1416 FKey
*pFKey
; /* Iterator variable */
1417 FKey
*pNext
; /* Copy of pFKey->pNextFrom */
1419 assert( db
==0 || IsVirtual(pTab
)
1420 || sqlite3SchemaMutexHeld(db
, 0, pTab
->pSchema
) );
1421 for(pFKey
=pTab
->pFKey
; pFKey
; pFKey
=pNext
){
1423 /* Remove the FK from the fkeyHash hash table. */
1424 if( !db
|| db
->pnBytesFreed
==0 ){
1425 if( pFKey
->pPrevTo
){
1426 pFKey
->pPrevTo
->pNextTo
= pFKey
->pNextTo
;
1428 void *p
= (void *)pFKey
->pNextTo
;
1429 const char *z
= (p
? pFKey
->pNextTo
->zTo
: pFKey
->zTo
);
1430 sqlite3HashInsert(&pTab
->pSchema
->fkeyHash
, z
, p
);
1432 if( pFKey
->pNextTo
){
1433 pFKey
->pNextTo
->pPrevTo
= pFKey
->pPrevTo
;
1437 /* EV: R-30323-21917 Each foreign key constraint in SQLite is
1438 ** classified as either immediate or deferred.
1440 assert( pFKey
->isDeferred
==0 || pFKey
->isDeferred
==1 );
1442 /* Delete any triggers created to implement actions for this FK. */
1443 #ifndef SQLITE_OMIT_TRIGGER
1444 fkTriggerDelete(db
, pFKey
->apTrigger
[0]);
1445 fkTriggerDelete(db
, pFKey
->apTrigger
[1]);
1448 pNext
= pFKey
->pNextFrom
;
1449 sqlite3DbFree(db
, pFKey
);
1452 #endif /* ifndef SQLITE_OMIT_FOREIGN_KEY */