blob: c94f9692381d429db57d2bd1c95a29b97f3de632 [file] [log] [blame]
dan1da40a32009-09-19 17:00:31 +00001/*
2**
3** The author disclaims copyright to this source code. In place of
4** a legal notice, here is a blessing:
5**
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.
9**
10*************************************************************************
11** This file contains code used by the compiler to add foreign key
12** support to compiled SQL statements.
13*/
14#include "sqliteInt.h"
15
16#ifndef SQLITE_OMIT_FOREIGN_KEY
dan75cbd982009-09-21 16:06:03 +000017#ifndef SQLITE_OMIT_TRIGGER
dan1da40a32009-09-19 17:00:31 +000018
19/*
20** Deferred and Immediate FKs
21** --------------------------
22**
23** Foreign keys in SQLite come in two flavours: deferred and immediate.
drhd91c1a12013-02-09 13:58:25 +000024** If an immediate foreign key constraint is violated,
25** SQLITE_CONSTRAINT_FOREIGNKEY is returned and the current
26** statement transaction rolled back. If a
dan1da40a32009-09-19 17:00:31 +000027** 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.
30**
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:
39**
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.
43**
44** * If the database contains foreign key violations when the
45** transaction is opened, this may cause the mechanism to malfunction.
46**
47** Despite these problems, this approach is adopted as it seems simpler
48** than the alternatives.
49**
50** INSERT operations:
51**
dan8099ce62009-09-23 08:43:35 +000052** I.1) For each FK for which the table is the child table, search
dan8a2fff72009-09-23 18:07:22 +000053** the parent table for a match. If none is found increment the
54** constraint counter.
dan1da40a32009-09-19 17:00:31 +000055**
dan8a2fff72009-09-23 18:07:22 +000056** I.2) For each FK for which the table is the parent table,
dan8099ce62009-09-23 08:43:35 +000057** search the child table for rows that correspond to the new
58** row in the parent table. Decrement the counter for each row
dan1da40a32009-09-19 17:00:31 +000059** found (as the constraint is now satisfied).
60**
61** DELETE operations:
62**
dan8a2fff72009-09-23 18:07:22 +000063** D.1) For each FK for which the table is the child table,
dan8099ce62009-09-23 08:43:35 +000064** 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,
dan1da40a32009-09-19 17:00:31 +000066** decrement the counter.
67**
dan8099ce62009-09-23 08:43:35 +000068** 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
dan8a2fff72009-09-23 18:07:22 +000070** in the parent table. For each found increment the counter.
dan1da40a32009-09-19 17:00:31 +000071**
72** UPDATE operations:
73**
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).
77**
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.
80**
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
85** row is inserted.
86**
danbd747832009-09-25 12:00:01 +000087** 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
drhd91c1a12013-02-09 13:58:25 +000090** constraint counter is greater than zero,
91** it returns SQLITE_CONSTRAINT_FOREIGNKEY
danbd747832009-09-25 12:00:01 +000092** 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.
97**
dan1da40a32009-09-19 17:00:31 +000098** TODO: How should dropping a table be handled? How should renaming a
99** table be handled?
dan8099ce62009-09-23 08:43:35 +0000100**
101**
dan1da40a32009-09-19 17:00:31 +0000102** Query API Notes
103** ---------------
104**
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
dan8099ce62009-09-23 08:43:35 +0000111** coding an INSERT operation. The functions used by the UPDATE/DELETE
112** generation code to query for this information are:
dan1da40a32009-09-19 17:00:31 +0000113**
dan8099ce62009-09-23 08:43:35 +0000114** sqlite3FkRequired() - Test to see if FK processing is required.
115** sqlite3FkOldmask() - Query for the set of required old.* columns.
116**
117**
118** Externally accessible module functions
119** --------------------------------------
120**
121** sqlite3FkCheck() - Check for foreign key violations.
122** sqlite3FkActions() - Code triggers for ON UPDATE/ON DELETE actions.
123** sqlite3FkDelete() - Delete an FKey structure.
dan1da40a32009-09-19 17:00:31 +0000124*/
125
126/*
127** VDBE Calling Convention
128** -----------------------
129**
130** Example:
131**
132** For the following INSERT statement:
133**
134** CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c);
135** INSERT INTO t1 VALUES(1, 2, 3.1);
136**
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)
141*/
142
143/*
dan8099ce62009-09-23 08:43:35 +0000144** A foreign key constraint requires that the key columns in the parent
dan1da40a32009-09-19 17:00:31 +0000145** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
dan8099ce62009-09-23 08:43:35 +0000146** Given that pParent is the parent table for foreign key constraint pFKey,
drh6c5b9152012-12-17 16:46:37 +0000147** search the schema for a unique index on the parent key columns.
dan1da40a32009-09-19 17:00:31 +0000148**
dan8099ce62009-09-23 08:43:35 +0000149** 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.
152**
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.
162**
163** If the required index cannot be found, either because:
164**
165** 1) The named parent key columns do not exist, or
166**
167** 2) The named parent key columns do exist, but are not subject to a
168** UNIQUE or PRIMARY KEY constraint, or
169**
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
172** PRIMARY KEY, or
173**
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 a different number of columns to the child key in
177** the child table.
178**
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.
dan1da40a32009-09-19 17:00:31 +0000182*/
drh6c5b9152012-12-17 16:46:37 +0000183int sqlite3FkLocateIndex(
dan1da40a32009-09-19 17:00:31 +0000184 Parse *pParse, /* Parse context to store any error in */
dan8099ce62009-09-23 08:43:35 +0000185 Table *pParent, /* Parent table of FK constraint pFKey */
dan1da40a32009-09-19 17:00:31 +0000186 FKey *pFKey, /* Foreign key to find index for */
dan8099ce62009-09-23 08:43:35 +0000187 Index **ppIdx, /* OUT: Unique index on parent table */
dan1da40a32009-09-19 17:00:31 +0000188 int **paiCol /* OUT: Map of index columns in pFKey */
189){
dan8099ce62009-09-23 08:43:35 +0000190 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 */
dan1da40a32009-09-19 17:00:31 +0000194
195 /* The caller is responsible for zeroing output parameters. */
196 assert( ppIdx && *ppIdx==0 );
197 assert( !paiCol || *paiCol==0 );
danf7a94542009-09-30 08:11:07 +0000198 assert( pParse );
dan1da40a32009-09-19 17:00:31 +0000199
200 /* If this is a non-composite (single column) foreign key, check if it
dan8099ce62009-09-23 08:43:35 +0000201 ** maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx
dan1da40a32009-09-19 17:00:31 +0000202 ** and *paiCol set to zero and return early.
203 **
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.
207 */
208 if( nCol==1 ){
209 /* The FK maps to the IPK if any of the following are true:
210 **
dand981d442009-09-23 13:59:17 +0000211 ** 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
dan1da40a32009-09-19 17:00:31 +0000214 ** PRIMARY KEY.
215 */
dan8099ce62009-09-23 08:43:35 +0000216 if( pParent->iPKey>=0 ){
217 if( !zKey ) return 0;
218 if( !sqlite3StrICmp(pParent->aCol[pParent->iPKey].zName, zKey) ) return 0;
dan1da40a32009-09-19 17:00:31 +0000219 }
220 }else if( paiCol ){
221 assert( nCol>1 );
222 aiCol = (int *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int));
223 if( !aiCol ) return 1;
224 *paiCol = aiCol;
225 }
226
dan8099ce62009-09-23 08:43:35 +0000227 for(pIdx=pParent->pIndex; pIdx; pIdx=pIdx->pNext){
drhbbbdc832013-10-22 18:01:40 +0000228 if( pIdx->nKeyCol==nCol && pIdx->onError!=OE_None ){
dan1da40a32009-09-19 17:00:31 +0000229 /* 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. */
232
dan8099ce62009-09-23 08:43:35 +0000233 if( zKey==0 ){
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
dan1da40a32009-09-19 17:00:31 +0000236 ** identified by the test (Index.autoIndex==2). */
237 if( pIdx->autoIndex==2 ){
dan8a2fff72009-09-23 18:07:22 +0000238 if( aiCol ){
239 int i;
240 for(i=0; i<nCol; i++) aiCol[i] = pFKey->aCol[i].iFrom;
241 }
dan1da40a32009-09-19 17:00:31 +0000242 break;
243 }
244 }else{
dan8099ce62009-09-23 08:43:35 +0000245 /* 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
dan9707c7b2009-09-29 15:41:57 +0000247 ** index matches those columns. Also, check that the index uses
248 ** the default collation sequences for each column. */
dan1da40a32009-09-19 17:00:31 +0000249 int i, j;
250 for(i=0; i<nCol; i++){
drhbbbdc832013-10-22 18:01:40 +0000251 i16 iCol = pIdx->aiColumn[i]; /* Index of column in parent tbl */
dan9707c7b2009-09-29 15:41:57 +0000252 char *zDfltColl; /* Def. collation for column */
253 char *zIdxCol; /* Name of indexed column */
254
255 /* If the index uses a collation sequence that is different from
256 ** the default collation sequence for the column, this index is
257 ** unusable. Bail out early in this case. */
258 zDfltColl = pParent->aCol[iCol].zColl;
259 if( !zDfltColl ){
260 zDfltColl = "BINARY";
261 }
262 if( sqlite3StrICmp(pIdx->azColl[i], zDfltColl) ) break;
263
264 zIdxCol = pParent->aCol[iCol].zName;
dan1da40a32009-09-19 17:00:31 +0000265 for(j=0; j<nCol; j++){
266 if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){
267 if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom;
268 break;
269 }
270 }
271 if( j==nCol ) break;
272 }
273 if( i==nCol ) break; /* pIdx is usable */
274 }
275 }
276 }
277
danf7a94542009-09-30 08:11:07 +0000278 if( !pIdx ){
danf0662562009-09-28 18:52:11 +0000279 if( !pParse->disableTriggers ){
drh9148def2012-12-17 20:40:39 +0000280 sqlite3ErrorMsg(pParse,
281 "foreign key mismatch - \"%w\" referencing \"%w\"",
282 pFKey->pFrom->zName, pFKey->zTo);
danf0662562009-09-28 18:52:11 +0000283 }
dan1da40a32009-09-19 17:00:31 +0000284 sqlite3DbFree(pParse->db, aiCol);
285 return 1;
286 }
287
288 *ppIdx = pIdx;
289 return 0;
290}
291
dan8099ce62009-09-23 08:43:35 +0000292/*
danbd747832009-09-25 12:00:01 +0000293** 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
dan8099ce62009-09-23 08:43:35 +0000296** affected - once to "delete" the old row, and then again to "insert" the
297** new row.
298**
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:
304**
305** Operation | FK type | Action taken
306** --------------------------------------------------------------------------
danbd747832009-09-25 12:00:01 +0000307** INSERT immediate Increment the "immediate constraint counter".
308**
309** DELETE immediate Decrement the "immediate constraint counter".
dan8099ce62009-09-23 08:43:35 +0000310**
311** INSERT deferred Increment the "deferred constraint counter".
312**
313** DELETE deferred Decrement the "deferred constraint counter".
314**
danbd747832009-09-25 12:00:01 +0000315** These operations are identified in the comment at the top of this file
316** (fkey.c) as "I.1" and "D.1".
dan8099ce62009-09-23 08:43:35 +0000317*/
318static void fkLookupParent(
dan1da40a32009-09-19 17:00:31 +0000319 Parse *pParse, /* Parse context */
320 int iDb, /* Index of database housing pTab */
dan8099ce62009-09-23 08:43:35 +0000321 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 */
dan02470b22009-10-03 07:04:11 +0000326 int nIncr, /* Increment constraint counter by this */
327 int isIgnore /* If true, pretend pTab contains all NULL values */
dan1da40a32009-09-19 17:00:31 +0000328){
dan8099ce62009-09-23 08:43:35 +0000329 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(v); /* jump here if parent key found */
dan1da40a32009-09-19 17:00:31 +0000333
dan0ff297e2009-09-25 17:03:14 +0000334 /* If nIncr is less than zero, then check at runtime if there are any
335 ** outstanding constraints to resolve. If there are not, there is no need
336 ** to check if deleting this row resolves any outstanding violations.
337 **
338 ** Check if any of the key columns in the child table row are NULL. If
339 ** any are, then the constraint is considered satisfied. No need to
340 ** search for a matching row in the parent table. */
341 if( nIncr<0 ){
342 sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, iOk);
drh688852a2014-02-17 22:40:43 +0000343 VdbeCoverage(v);
dan0ff297e2009-09-25 17:03:14 +0000344 }
dan1da40a32009-09-19 17:00:31 +0000345 for(i=0; i<pFKey->nCol; i++){
dan36062642009-09-21 18:56:23 +0000346 int iReg = aiCol[i] + regData + 1;
drh688852a2014-02-17 22:40:43 +0000347 sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk); VdbeCoverage(v);
dan1da40a32009-09-19 17:00:31 +0000348 }
349
dan02470b22009-10-03 07:04:11 +0000350 if( isIgnore==0 ){
351 if( pIdx==0 ){
352 /* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY
353 ** column of the parent table (table pTab). */
354 int iMustBeInt; /* Address of MustBeInt instruction */
355 int regTemp = sqlite3GetTempReg(pParse);
356
357 /* Invoke MustBeInt to coerce the child key value to an integer (i.e.
358 ** apply the affinity of the parent key). If this fails, then there
359 ** is no matching parent key. Before using MustBeInt, make a copy of
360 ** the value. Otherwise, the value inserted into the child key column
361 ** will have INTEGER affinity applied to it, which may not be correct. */
362 sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[0]+1+regData, regTemp);
363 iMustBeInt = sqlite3VdbeAddOp2(v, OP_MustBeInt, regTemp, 0);
drh688852a2014-02-17 22:40:43 +0000364 VdbeCoverage(v);
dan02470b22009-10-03 07:04:11 +0000365
366 /* If the parent table is the same as the child table, and we are about
367 ** to increment the constraint-counter (i.e. this is an INSERT operation),
368 ** then check if the row being inserted matches itself. If so, do not
369 ** increment the constraint-counter. */
370 if( pTab==pFKey->pFrom && nIncr==1 ){
drh688852a2014-02-17 22:40:43 +0000371 sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk, regTemp); VdbeCoverage(v);
dan9277efa2009-09-28 11:54:21 +0000372 }
dan02470b22009-10-03 07:04:11 +0000373
374 sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
drh688852a2014-02-17 22:40:43 +0000375 sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp); VdbeCoverage(v);
dan9277efa2009-09-28 11:54:21 +0000376 sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
dan02470b22009-10-03 07:04:11 +0000377 sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
378 sqlite3VdbeJumpHere(v, iMustBeInt);
379 sqlite3ReleaseTempReg(pParse, regTemp);
380 }else{
381 int nCol = pFKey->nCol;
382 int regTemp = sqlite3GetTempRange(pParse, nCol);
383 int regRec = sqlite3GetTempReg(pParse);
dan02470b22009-10-03 07:04:11 +0000384
385 sqlite3VdbeAddOp3(v, OP_OpenRead, iCur, pIdx->tnum, iDb);
drh2ec2fb22013-11-06 19:59:23 +0000386 sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
dan02470b22009-10-03 07:04:11 +0000387 for(i=0; i<nCol; i++){
drhebc16712010-09-28 00:25:58 +0000388 sqlite3VdbeAddOp2(v, OP_Copy, aiCol[i]+1+regData, regTemp+i);
dan02470b22009-10-03 07:04:11 +0000389 }
390
391 /* If the parent table is the same as the child table, and we are about
392 ** to increment the constraint-counter (i.e. this is an INSERT operation),
393 ** then check if the row being inserted matches itself. If so, do not
danb328deb2011-06-10 16:33:25 +0000394 ** increment the constraint-counter.
395 **
396 ** If any of the parent-key values are NULL, then the row cannot match
397 ** itself. So set JUMPIFNULL to make sure we do the OP_Found if any
398 ** of the parent-key values are NULL (at this point it is known that
399 ** none of the child key values are).
400 */
dan02470b22009-10-03 07:04:11 +0000401 if( pTab==pFKey->pFrom && nIncr==1 ){
402 int iJump = sqlite3VdbeCurrentAddr(v) + nCol + 1;
403 for(i=0; i<nCol; i++){
404 int iChild = aiCol[i]+1+regData;
405 int iParent = pIdx->aiColumn[i]+1+regData;
danb328deb2011-06-10 16:33:25 +0000406 assert( aiCol[i]!=pTab->iPKey );
407 if( pIdx->aiColumn[i]==pTab->iPKey ){
408 /* The parent key is a composite key that includes the IPK column */
409 iParent = regData;
410 }
drh688852a2014-02-17 22:40:43 +0000411 sqlite3VdbeAddOp3(v, OP_Ne, iChild, iJump, iParent); VdbeCoverage(v);
danb328deb2011-06-10 16:33:25 +0000412 sqlite3VdbeChangeP5(v, SQLITE_JUMPIFNULL);
dan02470b22009-10-03 07:04:11 +0000413 }
414 sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
415 }
416
drh57bf4a82014-02-17 14:59:22 +0000417 sqlite3VdbeAddOp4(v, OP_MakeRecord, regTemp, nCol, regRec,
418 sqlite3IndexAffinityStr(v,pIdx), nCol);
drh688852a2014-02-17 22:40:43 +0000419 sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0); VdbeCoverage(v);
dan02470b22009-10-03 07:04:11 +0000420
421 sqlite3ReleaseTempReg(pParse, regRec);
422 sqlite3ReleaseTempRange(pParse, regTemp, nCol);
dan9277efa2009-09-28 11:54:21 +0000423 }
dan1da40a32009-09-19 17:00:31 +0000424 }
425
drh648e2642013-07-11 15:03:32 +0000426 if( !pFKey->isDeferred && !(pParse->db->flags & SQLITE_DeferFKs)
427 && !pParse->pToplevel
428 && !pParse->isMultiWrite
429 ){
dan32b09f22009-09-23 17:29:59 +0000430 /* Special case: If this is an INSERT statement that will insert exactly
431 ** one row into the table, raise a constraint immediately instead of
432 ** incrementing a counter. This is necessary as the VM code is being
433 ** generated for will not open a statement transaction. */
434 assert( nIncr==1 );
drhd91c1a12013-02-09 13:58:25 +0000435 sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
drhf9c8ce32013-11-05 13:33:55 +0000436 OE_Abort, 0, P4_STATIC, P5_ConstraintFK);
dan32b09f22009-09-23 17:29:59 +0000437 }else{
438 if( nIncr>0 && pFKey->isDeferred==0 ){
439 sqlite3ParseToplevel(pParse)->mayAbort = 1;
440 }
dan0ff297e2009-09-25 17:03:14 +0000441 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
dan1da40a32009-09-19 17:00:31 +0000442 }
443
444 sqlite3VdbeResolveLabel(v, iOk);
daned81bf62009-10-07 16:04:46 +0000445 sqlite3VdbeAddOp1(v, OP_Close, iCur);
dan1da40a32009-09-19 17:00:31 +0000446}
447
drh90e758f2013-11-04 13:56:00 +0000448
449/*
450** Return an Expr object that refers to a memory register corresponding
451** to column iCol of table pTab.
452**
453** regBase is the first of an array of register that contains the data
454** for pTab. regBase itself holds the rowid. regBase+1 holds the first
455** column. regBase+2 holds the second column, and so forth.
456*/
457static Expr *exprTableRegister(
458 Parse *pParse, /* Parsing and code generating context */
459 Table *pTab, /* The table whose content is at r[regBase]... */
460 int regBase, /* Contents of table pTab */
461 i16 iCol /* Which column of pTab is desired */
462){
463 Expr *pExpr;
464 Column *pCol;
465 const char *zColl;
466 sqlite3 *db = pParse->db;
467
468 pExpr = sqlite3Expr(db, TK_REGISTER, 0);
469 if( pExpr ){
470 if( iCol>=0 && iCol!=pTab->iPKey ){
471 pCol = &pTab->aCol[iCol];
472 pExpr->iTable = regBase + iCol + 1;
473 pExpr->affinity = pCol->affinity;
474 zColl = pCol->zColl;
475 if( zColl==0 ) zColl = db->pDfltColl->zName;
476 pExpr = sqlite3ExprAddCollateString(pParse, pExpr, zColl);
477 }else{
478 pExpr->iTable = regBase;
479 pExpr->affinity = SQLITE_AFF_INTEGER;
480 }
481 }
482 return pExpr;
483}
484
485/*
486** Return an Expr object that refers to column iCol of table pTab which
487** has cursor iCur.
488*/
489static Expr *exprTableColumn(
490 sqlite3 *db, /* The database connection */
491 Table *pTab, /* The table whose column is desired */
492 int iCursor, /* The open cursor on the table */
493 i16 iCol /* The column that is wanted */
494){
495 Expr *pExpr = sqlite3Expr(db, TK_COLUMN, 0);
496 if( pExpr ){
497 pExpr->pTab = pTab;
498 pExpr->iTable = iCursor;
499 pExpr->iColumn = iCol;
500 }
501 return pExpr;
502}
503
dan8099ce62009-09-23 08:43:35 +0000504/*
505** This function is called to generate code executed when a row is deleted
506** from the parent table of foreign key constraint pFKey and, if pFKey is
507** deferred, when a row is inserted into the same table. When generating
508** code for an SQL UPDATE operation, this function may be called twice -
509** once to "delete" the old row and once to "insert" the new row.
510**
511** The code generated by this function scans through the rows in the child
512** table that correspond to the parent table row being deleted or inserted.
513** For each child row found, one of the following actions is taken:
514**
515** Operation | FK type | Action taken
516** --------------------------------------------------------------------------
danbd747832009-09-25 12:00:01 +0000517** DELETE immediate Increment the "immediate constraint counter".
518** Or, if the ON (UPDATE|DELETE) action is RESTRICT,
drhf9c8ce32013-11-05 13:33:55 +0000519** throw a "FOREIGN KEY constraint failed" exception.
danbd747832009-09-25 12:00:01 +0000520**
521** INSERT immediate Decrement the "immediate constraint counter".
dan8099ce62009-09-23 08:43:35 +0000522**
523** DELETE deferred Increment the "deferred constraint counter".
524** Or, if the ON (UPDATE|DELETE) action is RESTRICT,
drhf9c8ce32013-11-05 13:33:55 +0000525** throw a "FOREIGN KEY constraint failed" exception.
dan8099ce62009-09-23 08:43:35 +0000526**
527** INSERT deferred Decrement the "deferred constraint counter".
528**
danbd747832009-09-25 12:00:01 +0000529** These operations are identified in the comment at the top of this file
530** (fkey.c) as "I.2" and "D.2".
dan8099ce62009-09-23 08:43:35 +0000531*/
532static void fkScanChildren(
dan1da40a32009-09-19 17:00:31 +0000533 Parse *pParse, /* Parse context */
drhbd50a922013-11-03 02:27:58 +0000534 SrcList *pSrc, /* The child table to be scanned */
535 Table *pTab, /* The parent table */
536 Index *pIdx, /* Index on parent covering the foreign key */
537 FKey *pFKey, /* The foreign key linking pSrc to pTab */
dan8099ce62009-09-23 08:43:35 +0000538 int *aiCol, /* Map from pIdx cols to child table cols */
drhbd50a922013-11-03 02:27:58 +0000539 int regData, /* Parent row data starts here */
dan1da40a32009-09-19 17:00:31 +0000540 int nIncr /* Amount to increment deferred counter by */
541){
542 sqlite3 *db = pParse->db; /* Database handle */
543 int i; /* Iterator variable */
544 Expr *pWhere = 0; /* WHERE clause to scan with */
545 NameContext sNameContext; /* Context used to resolve WHERE clause */
546 WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */
dan0ff297e2009-09-25 17:03:14 +0000547 int iFkIfZero = 0; /* Address of OP_FkIfZero */
548 Vdbe *v = sqlite3GetVdbe(pParse);
549
drhbd50a922013-11-03 02:27:58 +0000550 assert( pIdx==0 || pIdx->pTable==pTab );
551 assert( pIdx==0 || pIdx->nKeyCol==pFKey->nCol );
552 assert( pIdx!=0 || pFKey->nCol==1 );
drh2bea7cd2013-11-18 11:20:50 +0000553 assert( pIdx!=0 || HasRowid(pTab) );
dan9277efa2009-09-28 11:54:21 +0000554
dan0ff297e2009-09-25 17:03:14 +0000555 if( nIncr<0 ){
556 iFkIfZero = sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0);
drh688852a2014-02-17 22:40:43 +0000557 VdbeCoverage(v);
dan0ff297e2009-09-25 17:03:14 +0000558 }
dan1da40a32009-09-19 17:00:31 +0000559
danbd747832009-09-25 12:00:01 +0000560 /* Create an Expr object representing an SQL expression like:
561 **
562 ** <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
563 **
564 ** The collation sequence used for the comparison should be that of
565 ** the parent key columns. The affinity of the parent key column should
566 ** be applied to each child key value before the comparison takes place.
567 */
dan1da40a32009-09-19 17:00:31 +0000568 for(i=0; i<pFKey->nCol; i++){
dan8099ce62009-09-23 08:43:35 +0000569 Expr *pLeft; /* Value from parent table row */
570 Expr *pRight; /* Column ref to child table */
dan1da40a32009-09-19 17:00:31 +0000571 Expr *pEq; /* Expression (pLeft = pRight) */
drhbbbdc832013-10-22 18:01:40 +0000572 i16 iCol; /* Index of column in child table */
dan8099ce62009-09-23 08:43:35 +0000573 const char *zCol; /* Name of column in child table */
dan1da40a32009-09-19 17:00:31 +0000574
drh90e758f2013-11-04 13:56:00 +0000575 iCol = pIdx ? pIdx->aiColumn[i] : -1;
576 pLeft = exprTableRegister(pParse, pTab, regData, iCol);
dan1da40a32009-09-19 17:00:31 +0000577 iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
dana8f0bf62009-09-23 12:06:52 +0000578 assert( iCol>=0 );
579 zCol = pFKey->pFrom->aCol[iCol].zName;
dan1da40a32009-09-19 17:00:31 +0000580 pRight = sqlite3Expr(db, TK_ID, zCol);
581 pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0);
582 pWhere = sqlite3ExprAnd(db, pWhere, pEq);
583 }
584
drh90e758f2013-11-04 13:56:00 +0000585 /* If the child table is the same as the parent table, then add terms
586 ** to the WHERE clause that prevent this entry from being scanned.
587 ** The added WHERE clause terms are like this:
588 **
589 ** $current_rowid!=rowid
590 ** NOT( $current_a==a AND $current_b==b AND ... )
591 **
592 ** The first form is used for rowid tables. The second form is used
593 ** for WITHOUT ROWID tables. In the second form, the primary key is
594 ** (a,b,...)
595 */
596 if( pTab==pFKey->pFrom && nIncr>0 ){
drhbd50a922013-11-03 02:27:58 +0000597 Expr *pNe; /* Expression (pLeft != pRight) */
dan9277efa2009-09-28 11:54:21 +0000598 Expr *pLeft; /* Value from parent table row */
599 Expr *pRight; /* Column ref to child table */
drh90e758f2013-11-04 13:56:00 +0000600 if( HasRowid(pTab) ){
601 pLeft = exprTableRegister(pParse, pTab, regData, -1);
602 pRight = exprTableColumn(db, pTab, pSrc->a[0].iCursor, -1);
603 pNe = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0);
604 }else{
drh90e758f2013-11-04 13:56:00 +0000605 Expr *pEq, *pAll = 0;
606 Index *pPk = sqlite3PrimaryKeyIndex(pTab);
drh2bea7cd2013-11-18 11:20:50 +0000607 assert( pIdx!=0 );
drh90e758f2013-11-04 13:56:00 +0000608 for(i=0; i<pPk->nKeyCol; i++){
609 i16 iCol = pIdx->aiColumn[i];
610 pLeft = exprTableRegister(pParse, pTab, regData, iCol);
611 pRight = exprTableColumn(db, pTab, pSrc->a[0].iCursor, iCol);
612 pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0);
613 pAll = sqlite3ExprAnd(db, pAll, pEq);
614 }
615 pNe = sqlite3PExpr(pParse, TK_NOT, pAll, 0, 0);
dan9277efa2009-09-28 11:54:21 +0000616 }
drhbd50a922013-11-03 02:27:58 +0000617 pWhere = sqlite3ExprAnd(db, pWhere, pNe);
dan9277efa2009-09-28 11:54:21 +0000618 }
619
dan1da40a32009-09-19 17:00:31 +0000620 /* Resolve the references in the WHERE clause. */
621 memset(&sNameContext, 0, sizeof(NameContext));
622 sNameContext.pSrcList = pSrc;
623 sNameContext.pParse = pParse;
624 sqlite3ResolveExprNames(&sNameContext, pWhere);
625
626 /* Create VDBE to loop through the entries in pSrc that match the WHERE
627 ** clause. If the constraint is not deferred, throw an exception for
628 ** each row found. Otherwise, for deferred constraints, increment the
629 ** deferred constraint counter by nIncr for each row selected. */
dan0efb72c2012-08-24 18:44:56 +0000630 pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0, 0, 0);
danf7a94542009-09-30 08:11:07 +0000631 if( nIncr>0 && pFKey->isDeferred==0 ){
632 sqlite3ParseToplevel(pParse)->mayAbort = 1;
dan1da40a32009-09-19 17:00:31 +0000633 }
danf7a94542009-09-30 08:11:07 +0000634 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
danf59c5ca2009-09-22 16:55:38 +0000635 if( pWInfo ){
636 sqlite3WhereEnd(pWInfo);
637 }
dan1da40a32009-09-19 17:00:31 +0000638
639 /* Clean up the WHERE clause constructed above. */
640 sqlite3ExprDelete(db, pWhere);
dan0ff297e2009-09-25 17:03:14 +0000641 if( iFkIfZero ){
642 sqlite3VdbeJumpHere(v, iFkIfZero);
643 }
dan1da40a32009-09-19 17:00:31 +0000644}
645
646/*
drhbd50a922013-11-03 02:27:58 +0000647** This function returns a linked list of FKey objects (connected by
648** FKey.pNextTo) holding all children of table pTab. For example,
dan1da40a32009-09-19 17:00:31 +0000649** given the following schema:
650**
651** CREATE TABLE t1(a PRIMARY KEY);
652** CREATE TABLE t2(b REFERENCES t1(a);
653**
654** Calling this function with table "t1" as an argument returns a pointer
655** to the FKey structure representing the foreign key constraint on table
656** "t2". Calling this function with "t2" as the argument would return a
dan8099ce62009-09-23 08:43:35 +0000657** NULL pointer (as there are no FK constraints for which t2 is the parent
658** table).
dan1da40a32009-09-19 17:00:31 +0000659*/
dan432cc5b2009-09-26 17:51:48 +0000660FKey *sqlite3FkReferences(Table *pTab){
dan1da40a32009-09-19 17:00:31 +0000661 int nName = sqlite3Strlen30(pTab->zName);
662 return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName);
663}
664
dan8099ce62009-09-23 08:43:35 +0000665/*
666** The second argument is a Trigger structure allocated by the
667** fkActionTrigger() routine. This function deletes the Trigger structure
668** and all of its sub-components.
669**
670** The Trigger structure or any of its sub-components may be allocated from
671** the lookaside buffer belonging to database handle dbMem.
672*/
dan75cbd982009-09-21 16:06:03 +0000673static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){
674 if( p ){
675 TriggerStep *pStep = p->step_list;
676 sqlite3ExprDelete(dbMem, pStep->pWhere);
677 sqlite3ExprListDelete(dbMem, pStep->pExprList);
dan9277efa2009-09-28 11:54:21 +0000678 sqlite3SelectDelete(dbMem, pStep->pSelect);
drh788536b2009-09-23 03:01:58 +0000679 sqlite3ExprDelete(dbMem, p->pWhen);
dan75cbd982009-09-21 16:06:03 +0000680 sqlite3DbFree(dbMem, p);
681 }
682}
683
dan8099ce62009-09-23 08:43:35 +0000684/*
dand66c8302009-09-28 14:49:01 +0000685** This function is called to generate code that runs when table pTab is
686** being dropped from the database. The SrcList passed as the second argument
687** to this function contains a single entry guaranteed to resolve to
688** table pTab.
689**
690** Normally, no code is required. However, if either
691**
692** (a) The table is the parent table of a FK constraint, or
693** (b) The table is the child table of a deferred FK constraint and it is
694** determined at runtime that there are outstanding deferred FK
695** constraint violations in the database,
696**
697** then the equivalent of "DELETE FROM <tbl>" is executed before dropping
698** the table from the database. Triggers are disabled while running this
699** DELETE, but foreign key actions are not.
700*/
701void sqlite3FkDropTable(Parse *pParse, SrcList *pName, Table *pTab){
702 sqlite3 *db = pParse->db;
703 if( (db->flags&SQLITE_ForeignKeys) && !IsVirtual(pTab) && !pTab->pSelect ){
704 int iSkip = 0;
705 Vdbe *v = sqlite3GetVdbe(pParse);
706
707 assert( v ); /* VDBE has already been allocated */
708 if( sqlite3FkReferences(pTab)==0 ){
709 /* Search for a deferred foreign key constraint for which this table
710 ** is the child table. If one cannot be found, return without
711 ** generating any VDBE code. If one can be found, then jump over
712 ** the entire DELETE if there are no outstanding deferred constraints
713 ** when this statement is run. */
714 FKey *p;
715 for(p=pTab->pFKey; p; p=p->pNextFrom){
dana8dbada2013-10-12 15:12:43 +0000716 if( p->isDeferred || (db->flags & SQLITE_DeferFKs) ) break;
dand66c8302009-09-28 14:49:01 +0000717 }
718 if( !p ) return;
719 iSkip = sqlite3VdbeMakeLabel(v);
drh688852a2014-02-17 22:40:43 +0000720 sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip); VdbeCoverage(v);
dand66c8302009-09-28 14:49:01 +0000721 }
722
723 pParse->disableTriggers = 1;
724 sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0);
725 pParse->disableTriggers = 0;
726
727 /* If the DELETE has generated immediate foreign key constraint
728 ** violations, halt the VDBE and return an error at this point, before
729 ** any modifications to the schema are made. This is because statement
dana8dbada2013-10-12 15:12:43 +0000730 ** transactions are not able to rollback schema changes.
731 **
732 ** If the SQLITE_DeferFKs flag is set, then this is not required, as
733 ** the statement transaction will not be rolled back even if FK
734 ** constraints are violated.
735 */
736 if( (db->flags & SQLITE_DeferFKs)==0 ){
737 sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2);
drh688852a2014-02-17 22:40:43 +0000738 VdbeCoverage(v);
dana8dbada2013-10-12 15:12:43 +0000739 sqlite3HaltConstraint(pParse, SQLITE_CONSTRAINT_FOREIGNKEY,
drhf9c8ce32013-11-05 13:33:55 +0000740 OE_Abort, 0, P4_STATIC, P5_ConstraintFK);
dana8dbada2013-10-12 15:12:43 +0000741 }
dand66c8302009-09-28 14:49:01 +0000742
743 if( iSkip ){
744 sqlite3VdbeResolveLabel(v, iSkip);
745 }
746 }
747}
748
dan8ff2d952013-09-05 18:40:29 +0000749
750/*
751** The second argument points to an FKey object representing a foreign key
752** for which pTab is the child table. An UPDATE statement against pTab
753** is currently being processed. For each column of the table that is
754** actually updated, the corresponding element in the aChange[] array
755** is zero or greater (if a column is unmodified the corresponding element
756** is set to -1). If the rowid column is modified by the UPDATE statement
757** the bChngRowid argument is non-zero.
758**
759** This function returns true if any of the columns that are part of the
760** child key for FK constraint *p are modified.
761*/
762static int fkChildIsModified(
763 Table *pTab, /* Table being updated */
764 FKey *p, /* Foreign key for which pTab is the child */
765 int *aChange, /* Array indicating modified columns */
766 int bChngRowid /* True if rowid is modified by this update */
767){
768 int i;
769 for(i=0; i<p->nCol; i++){
770 int iChildKey = p->aCol[i].iFrom;
771 if( aChange[iChildKey]>=0 ) return 1;
772 if( iChildKey==pTab->iPKey && bChngRowid ) return 1;
773 }
774 return 0;
775}
776
777/*
778** The second argument points to an FKey object representing a foreign key
779** for which pTab is the parent table. An UPDATE statement against pTab
780** is currently being processed. For each column of the table that is
781** actually updated, the corresponding element in the aChange[] array
782** is zero or greater (if a column is unmodified the corresponding element
783** is set to -1). If the rowid column is modified by the UPDATE statement
784** the bChngRowid argument is non-zero.
785**
786** This function returns true if any of the columns that are part of the
787** parent key for FK constraint *p are modified.
788*/
789static int fkParentIsModified(
790 Table *pTab,
791 FKey *p,
792 int *aChange,
793 int bChngRowid
794){
795 int i;
796 for(i=0; i<p->nCol; i++){
797 char *zKey = p->aCol[i].zCol;
798 int iKey;
799 for(iKey=0; iKey<pTab->nCol; iKey++){
800 if( aChange[iKey]>=0 || (iKey==pTab->iPKey && bChngRowid) ){
801 Column *pCol = &pTab->aCol[iKey];
802 if( zKey ){
803 if( 0==sqlite3StrICmp(pCol->zName, zKey) ) return 1;
804 }else if( pCol->colFlags & COLFLAG_PRIMKEY ){
805 return 1;
806 }
807 }
808 }
809 }
810 return 0;
811}
812
dand66c8302009-09-28 14:49:01 +0000813/*
dan8099ce62009-09-23 08:43:35 +0000814** This function is called when inserting, deleting or updating a row of
815** table pTab to generate VDBE code to perform foreign key constraint
816** processing for the operation.
817**
818** For a DELETE operation, parameter regOld is passed the index of the
819** first register in an array of (pTab->nCol+1) registers containing the
820** rowid of the row being deleted, followed by each of the column values
821** of the row being deleted, from left to right. Parameter regNew is passed
822** zero in this case.
823**
dan8099ce62009-09-23 08:43:35 +0000824** For an INSERT operation, regOld is passed zero and regNew is passed the
825** first register of an array of (pTab->nCol+1) registers containing the new
826** row data.
827**
dan9277efa2009-09-28 11:54:21 +0000828** For an UPDATE operation, this function is called twice. Once before
829** the original record is deleted from the table using the calling convention
830** described for DELETE. Then again after the original record is deleted
dane7a94d82009-10-01 16:09:04 +0000831** but before the new record is inserted using the INSERT convention.
dan8099ce62009-09-23 08:43:35 +0000832*/
dan1da40a32009-09-19 17:00:31 +0000833void sqlite3FkCheck(
834 Parse *pParse, /* Parse context */
835 Table *pTab, /* Row is being deleted from this table */
dan1da40a32009-09-19 17:00:31 +0000836 int regOld, /* Previous row data is stored here */
dan8ff2d952013-09-05 18:40:29 +0000837 int regNew, /* New row data is stored here */
838 int *aChange, /* Array indicating UPDATEd columns (or 0) */
839 int bChngRowid /* True if rowid is UPDATEd */
dan1da40a32009-09-19 17:00:31 +0000840){
841 sqlite3 *db = pParse->db; /* Database handle */
dan1da40a32009-09-19 17:00:31 +0000842 FKey *pFKey; /* Used to iterate through FKs */
843 int iDb; /* Index of database containing pTab */
844 const char *zDb; /* Name of database containing pTab */
danf0662562009-09-28 18:52:11 +0000845 int isIgnoreErrors = pParse->disableTriggers;
dan1da40a32009-09-19 17:00:31 +0000846
dan792e9202009-09-29 11:28:51 +0000847 /* Exactly one of regOld and regNew should be non-zero. */
848 assert( (regOld==0)!=(regNew==0) );
dan1da40a32009-09-19 17:00:31 +0000849
850 /* If foreign-keys are disabled, this function is a no-op. */
851 if( (db->flags&SQLITE_ForeignKeys)==0 ) return;
852
dan1da40a32009-09-19 17:00:31 +0000853 iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
854 zDb = db->aDb[iDb].zName;
855
dan8099ce62009-09-23 08:43:35 +0000856 /* Loop through all the foreign key constraints for which pTab is the
857 ** child table (the table that the foreign key definition is part of). */
dan1da40a32009-09-19 17:00:31 +0000858 for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
dan8099ce62009-09-23 08:43:35 +0000859 Table *pTo; /* Parent table of foreign key pFKey */
dan1da40a32009-09-19 17:00:31 +0000860 Index *pIdx = 0; /* Index on key columns in pTo */
dan36062642009-09-21 18:56:23 +0000861 int *aiFree = 0;
862 int *aiCol;
863 int iCol;
864 int i;
dan02470b22009-10-03 07:04:11 +0000865 int isIgnore = 0;
dan1da40a32009-09-19 17:00:31 +0000866
dan8ff2d952013-09-05 18:40:29 +0000867 if( aChange
868 && sqlite3_stricmp(pTab->zName, pFKey->zTo)!=0
869 && fkChildIsModified(pTab, pFKey, aChange, bChngRowid)==0
870 ){
871 continue;
872 }
873
dan8099ce62009-09-23 08:43:35 +0000874 /* Find the parent table of this foreign key. Also find a unique index
875 ** on the parent key columns in the parent table. If either of these
876 ** schema items cannot be located, set an error in pParse and return
877 ** early. */
danf0662562009-09-28 18:52:11 +0000878 if( pParse->disableTriggers ){
879 pTo = sqlite3FindTable(db, pFKey->zTo, zDb);
880 }else{
881 pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);
882 }
drh6c5b9152012-12-17 16:46:37 +0000883 if( !pTo || sqlite3FkLocateIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){
dan3098dc52011-08-22 09:54:26 +0000884 assert( isIgnoreErrors==0 || (regOld!=0 && regNew==0) );
danf0662562009-09-28 18:52:11 +0000885 if( !isIgnoreErrors || db->mallocFailed ) return;
drh9147c7b2011-08-22 20:33:12 +0000886 if( pTo==0 ){
dan3098dc52011-08-22 09:54:26 +0000887 /* If isIgnoreErrors is true, then a table is being dropped. In this
888 ** case SQLite runs a "DELETE FROM xxx" on the table being dropped
889 ** before actually dropping it in order to check FK constraints.
890 ** If the parent table of an FK constraint on the current table is
891 ** missing, behave as if it is empty. i.e. decrement the relevant
892 ** FK counter for each row of the current table with non-NULL keys.
893 */
894 Vdbe *v = sqlite3GetVdbe(pParse);
895 int iJump = sqlite3VdbeCurrentAddr(v) + pFKey->nCol + 1;
896 for(i=0; i<pFKey->nCol; i++){
897 int iReg = pFKey->aCol[i].iFrom + regOld + 1;
drh688852a2014-02-17 22:40:43 +0000898 sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iJump); VdbeCoverage(v);
dan3098dc52011-08-22 09:54:26 +0000899 }
900 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, -1);
901 }
danf0662562009-09-28 18:52:11 +0000902 continue;
903 }
dan36062642009-09-21 18:56:23 +0000904 assert( pFKey->nCol==1 || (aiFree && pIdx) );
dan1da40a32009-09-19 17:00:31 +0000905
dan36062642009-09-21 18:56:23 +0000906 if( aiFree ){
907 aiCol = aiFree;
908 }else{
909 iCol = pFKey->aCol[0].iFrom;
910 aiCol = &iCol;
911 }
912 for(i=0; i<pFKey->nCol; i++){
913 if( aiCol[i]==pTab->iPKey ){
914 aiCol[i] = -1;
915 }
dan47a06342009-10-02 14:23:41 +0000916#ifndef SQLITE_OMIT_AUTHORIZATION
dan02470b22009-10-03 07:04:11 +0000917 /* Request permission to read the parent key columns. If the
918 ** authorization callback returns SQLITE_IGNORE, behave as if any
919 ** values read from the parent table are NULL. */
dan47a06342009-10-02 14:23:41 +0000920 if( db->xAuth ){
dan02470b22009-10-03 07:04:11 +0000921 int rcauth;
dan47a06342009-10-02 14:23:41 +0000922 char *zCol = pTo->aCol[pIdx ? pIdx->aiColumn[i] : pTo->iPKey].zName;
dan02470b22009-10-03 07:04:11 +0000923 rcauth = sqlite3AuthReadCol(pParse, pTo->zName, zCol, iDb);
924 isIgnore = (rcauth==SQLITE_IGNORE);
dan47a06342009-10-02 14:23:41 +0000925 }
926#endif
dan36062642009-09-21 18:56:23 +0000927 }
928
dan8099ce62009-09-23 08:43:35 +0000929 /* Take a shared-cache advisory read-lock on the parent table. Allocate
930 ** a cursor to use to search the unique index on the parent key columns
931 ** in the parent table. */
dan1da40a32009-09-19 17:00:31 +0000932 sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName);
933 pParse->nTab++;
934
dan32b09f22009-09-23 17:29:59 +0000935 if( regOld!=0 ){
936 /* A row is being removed from the child table. Search for the parent.
937 ** If the parent does not exist, removing the child row resolves an
938 ** outstanding foreign key constraint violation. */
dan02470b22009-10-03 07:04:11 +0000939 fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1,isIgnore);
dan1da40a32009-09-19 17:00:31 +0000940 }
941 if( regNew!=0 ){
dan32b09f22009-09-23 17:29:59 +0000942 /* A row is being added to the child table. If a parent row cannot
943 ** be found, adding the child row has violated the FK constraint. */
dan02470b22009-10-03 07:04:11 +0000944 fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1,isIgnore);
dan1da40a32009-09-19 17:00:31 +0000945 }
946
dan36062642009-09-21 18:56:23 +0000947 sqlite3DbFree(db, aiFree);
dan1da40a32009-09-19 17:00:31 +0000948 }
949
drhbd50a922013-11-03 02:27:58 +0000950 /* Loop through all the foreign key constraints that refer to this table.
951 ** (the "child" constraints) */
dan432cc5b2009-09-26 17:51:48 +0000952 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
dan1da40a32009-09-19 17:00:31 +0000953 Index *pIdx = 0; /* Foreign key index for pFKey */
954 SrcList *pSrc;
955 int *aiCol = 0;
956
dan8ff2d952013-09-05 18:40:29 +0000957 if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){
958 continue;
959 }
960
drh648e2642013-07-11 15:03:32 +0000961 if( !pFKey->isDeferred && !(db->flags & SQLITE_DeferFKs)
962 && !pParse->pToplevel && !pParse->isMultiWrite
963 ){
dan32b09f22009-09-23 17:29:59 +0000964 assert( regOld==0 && regNew!=0 );
965 /* Inserting a single row into a parent table cannot cause an immediate
966 ** foreign key violation. So do nothing in this case. */
danf0662562009-09-28 18:52:11 +0000967 continue;
dan1da40a32009-09-19 17:00:31 +0000968 }
969
drh6c5b9152012-12-17 16:46:37 +0000970 if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){
danf0662562009-09-28 18:52:11 +0000971 if( !isIgnoreErrors || db->mallocFailed ) return;
972 continue;
973 }
dan1da40a32009-09-19 17:00:31 +0000974 assert( aiCol || pFKey->nCol==1 );
975
drhbd50a922013-11-03 02:27:58 +0000976 /* Create a SrcList structure containing the child table. We need the
977 ** child table as a SrcList for sqlite3WhereBegin() */
dan1da40a32009-09-19 17:00:31 +0000978 pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
danf59c5ca2009-09-22 16:55:38 +0000979 if( pSrc ){
drh9a616f52009-10-12 20:01:49 +0000980 struct SrcList_item *pItem = pSrc->a;
981 pItem->pTab = pFKey->pFrom;
982 pItem->zName = pFKey->pFrom->zName;
983 pItem->pTab->nRef++;
984 pItem->iCursor = pParse->nTab++;
danf59c5ca2009-09-22 16:55:38 +0000985
dan32b09f22009-09-23 17:29:59 +0000986 if( regNew!=0 ){
dan9277efa2009-09-28 11:54:21 +0000987 fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1);
danf59c5ca2009-09-22 16:55:38 +0000988 }
989 if( regOld!=0 ){
990 /* If there is a RESTRICT action configured for the current operation
dan8099ce62009-09-23 08:43:35 +0000991 ** on the parent table of this FK, then throw an exception
danf59c5ca2009-09-22 16:55:38 +0000992 ** immediately if the FK constraint is violated, even if this is a
993 ** deferred trigger. That's what RESTRICT means. To defer checking
994 ** the constraint, the FK should specify NO ACTION (represented
995 ** using OE_None). NO ACTION is the default. */
dan9277efa2009-09-28 11:54:21 +0000996 fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1);
danf59c5ca2009-09-22 16:55:38 +0000997 }
drh9a616f52009-10-12 20:01:49 +0000998 pItem->zName = 0;
danf59c5ca2009-09-22 16:55:38 +0000999 sqlite3SrcListDelete(db, pSrc);
dan1da40a32009-09-19 17:00:31 +00001000 }
dan1da40a32009-09-19 17:00:31 +00001001 sqlite3DbFree(db, aiCol);
1002 }
1003}
1004
1005#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
1006
1007/*
1008** This function is called before generating code to update or delete a
dane7a94d82009-10-01 16:09:04 +00001009** row contained in table pTab.
dan1da40a32009-09-19 17:00:31 +00001010*/
1011u32 sqlite3FkOldmask(
1012 Parse *pParse, /* Parse context */
dane7a94d82009-10-01 16:09:04 +00001013 Table *pTab /* Table being modified */
dan1da40a32009-09-19 17:00:31 +00001014){
1015 u32 mask = 0;
1016 if( pParse->db->flags&SQLITE_ForeignKeys ){
1017 FKey *p;
1018 int i;
1019 for(p=pTab->pFKey; p; p=p->pNextFrom){
dan32b09f22009-09-23 17:29:59 +00001020 for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
dan1da40a32009-09-19 17:00:31 +00001021 }
dan432cc5b2009-09-26 17:51:48 +00001022 for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
dan1da40a32009-09-19 17:00:31 +00001023 Index *pIdx = 0;
drh6c5b9152012-12-17 16:46:37 +00001024 sqlite3FkLocateIndex(pParse, pTab, p, &pIdx, 0);
dan1da40a32009-09-19 17:00:31 +00001025 if( pIdx ){
drhbbbdc832013-10-22 18:01:40 +00001026 for(i=0; i<pIdx->nKeyCol; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
dan1da40a32009-09-19 17:00:31 +00001027 }
1028 }
1029 }
1030 return mask;
1031}
1032
dan8ff2d952013-09-05 18:40:29 +00001033
dan1da40a32009-09-19 17:00:31 +00001034/*
1035** This function is called before generating code to update or delete a
dane7a94d82009-10-01 16:09:04 +00001036** row contained in table pTab. If the operation is a DELETE, then
1037** parameter aChange is passed a NULL value. For an UPDATE, aChange points
1038** to an array of size N, where N is the number of columns in table pTab.
1039** If the i'th column is not modified by the UPDATE, then the corresponding
1040** entry in the aChange[] array is set to -1. If the column is modified,
1041** the value is 0 or greater. Parameter chngRowid is set to true if the
1042** UPDATE statement modifies the rowid fields of the table.
dan1da40a32009-09-19 17:00:31 +00001043**
1044** If any foreign key processing will be required, this function returns
1045** true. If there is no foreign key related processing, this function
1046** returns false.
1047*/
1048int sqlite3FkRequired(
1049 Parse *pParse, /* Parse context */
1050 Table *pTab, /* Table being modified */
dane7a94d82009-10-01 16:09:04 +00001051 int *aChange, /* Non-NULL for UPDATE operations */
1052 int chngRowid /* True for UPDATE that affects rowid */
dan1da40a32009-09-19 17:00:31 +00001053){
1054 if( pParse->db->flags&SQLITE_ForeignKeys ){
dane7a94d82009-10-01 16:09:04 +00001055 if( !aChange ){
1056 /* A DELETE operation. Foreign key processing is required if the
1057 ** table in question is either the child or parent table for any
1058 ** foreign key constraint. */
1059 return (sqlite3FkReferences(pTab) || pTab->pFKey);
1060 }else{
1061 /* This is an UPDATE. Foreign key processing is only required if the
1062 ** operation modifies one or more child or parent key columns. */
dane7a94d82009-10-01 16:09:04 +00001063 FKey *p;
1064
1065 /* Check if any child key columns are being modified. */
1066 for(p=pTab->pFKey; p; p=p->pNextFrom){
dan8ff2d952013-09-05 18:40:29 +00001067 if( fkChildIsModified(pTab, p, aChange, chngRowid) ) return 1;
dane7a94d82009-10-01 16:09:04 +00001068 }
1069
1070 /* Check if any parent key columns are being modified. */
1071 for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
dan8ff2d952013-09-05 18:40:29 +00001072 if( fkParentIsModified(pTab, p, aChange, chngRowid) ) return 1;
dane7a94d82009-10-01 16:09:04 +00001073 }
1074 }
dan1da40a32009-09-19 17:00:31 +00001075 }
1076 return 0;
1077}
1078
dan8099ce62009-09-23 08:43:35 +00001079/*
1080** This function is called when an UPDATE or DELETE operation is being
1081** compiled on table pTab, which is the parent table of foreign-key pFKey.
1082** If the current operation is an UPDATE, then the pChanges parameter is
1083** passed a pointer to the list of columns being modified. If it is a
1084** DELETE, pChanges is passed a NULL pointer.
1085**
1086** It returns a pointer to a Trigger structure containing a trigger
1087** equivalent to the ON UPDATE or ON DELETE action specified by pFKey.
1088** If the action is "NO ACTION" or "RESTRICT", then a NULL pointer is
1089** returned (these actions require no special handling by the triggers
1090** sub-system, code for them is created by fkScanChildren()).
1091**
1092** For example, if pFKey is the foreign key and pTab is table "p" in
1093** the following schema:
1094**
1095** CREATE TABLE p(pk PRIMARY KEY);
1096** CREATE TABLE c(ck REFERENCES p ON DELETE CASCADE);
1097**
1098** then the returned trigger structure is equivalent to:
1099**
1100** CREATE TRIGGER ... DELETE ON p BEGIN
1101** DELETE FROM c WHERE ck = old.pk;
1102** END;
1103**
1104** The returned pointer is cached as part of the foreign key object. It
1105** is eventually freed along with the rest of the foreign key object by
1106** sqlite3FkDelete().
1107*/
dan1da40a32009-09-19 17:00:31 +00001108static Trigger *fkActionTrigger(
dan8099ce62009-09-23 08:43:35 +00001109 Parse *pParse, /* Parse context */
dan1da40a32009-09-19 17:00:31 +00001110 Table *pTab, /* Table being updated or deleted from */
1111 FKey *pFKey, /* Foreign key to get action for */
1112 ExprList *pChanges /* Change-list for UPDATE, NULL for DELETE */
1113){
1114 sqlite3 *db = pParse->db; /* Database handle */
dan29c7f9c2009-09-22 15:53:47 +00001115 int action; /* One of OE_None, OE_Cascade etc. */
1116 Trigger *pTrigger; /* Trigger definition to return */
dan8099ce62009-09-23 08:43:35 +00001117 int iAction = (pChanges!=0); /* 1 for UPDATE, 0 for DELETE */
dan1da40a32009-09-19 17:00:31 +00001118
dan8099ce62009-09-23 08:43:35 +00001119 action = pFKey->aAction[iAction];
1120 pTrigger = pFKey->apTrigger[iAction];
dan1da40a32009-09-19 17:00:31 +00001121
dan9277efa2009-09-28 11:54:21 +00001122 if( action!=OE_None && !pTrigger ){
dan29c7f9c2009-09-22 15:53:47 +00001123 u8 enableLookaside; /* Copy of db->lookaside.bEnabled */
dan8099ce62009-09-23 08:43:35 +00001124 char const *zFrom; /* Name of child table */
dan1da40a32009-09-19 17:00:31 +00001125 int nFrom; /* Length in bytes of zFrom */
dan29c7f9c2009-09-22 15:53:47 +00001126 Index *pIdx = 0; /* Parent key index for this FK */
1127 int *aiCol = 0; /* child table cols -> parent key cols */
drhd3ceeb52009-10-13 13:08:19 +00001128 TriggerStep *pStep = 0; /* First (only) step of trigger program */
dan29c7f9c2009-09-22 15:53:47 +00001129 Expr *pWhere = 0; /* WHERE clause of trigger step */
1130 ExprList *pList = 0; /* Changes list if ON UPDATE CASCADE */
dan9277efa2009-09-28 11:54:21 +00001131 Select *pSelect = 0; /* If RESTRICT, "SELECT RAISE(...)" */
dan29c7f9c2009-09-22 15:53:47 +00001132 int i; /* Iterator variable */
drh788536b2009-09-23 03:01:58 +00001133 Expr *pWhen = 0; /* WHEN clause for the trigger */
dan1da40a32009-09-19 17:00:31 +00001134
drh6c5b9152012-12-17 16:46:37 +00001135 if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
dan1da40a32009-09-19 17:00:31 +00001136 assert( aiCol || pFKey->nCol==1 );
1137
dan1da40a32009-09-19 17:00:31 +00001138 for(i=0; i<pFKey->nCol; i++){
dan1da40a32009-09-19 17:00:31 +00001139 Token tOld = { "old", 3 }; /* Literal "old" token */
1140 Token tNew = { "new", 3 }; /* Literal "new" token */
dan8099ce62009-09-23 08:43:35 +00001141 Token tFromCol; /* Name of column in child table */
1142 Token tToCol; /* Name of column in parent table */
1143 int iFromCol; /* Idx of column in child table */
dan29c7f9c2009-09-22 15:53:47 +00001144 Expr *pEq; /* tFromCol = OLD.tToCol */
dan1da40a32009-09-19 17:00:31 +00001145
1146 iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
dana8f0bf62009-09-23 12:06:52 +00001147 assert( iFromCol>=0 );
dan1da40a32009-09-19 17:00:31 +00001148 tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
dana8f0bf62009-09-23 12:06:52 +00001149 tFromCol.z = pFKey->pFrom->aCol[iFromCol].zName;
dan1da40a32009-09-19 17:00:31 +00001150
1151 tToCol.n = sqlite3Strlen30(tToCol.z);
1152 tFromCol.n = sqlite3Strlen30(tFromCol.z);
1153
dan652ac1d2009-09-29 16:38:59 +00001154 /* Create the expression "OLD.zToCol = zFromCol". It is important
1155 ** that the "OLD.zToCol" term is on the LHS of the = operator, so
1156 ** that the affinity and collation sequence associated with the
1157 ** parent table are used for the comparison. */
dan1da40a32009-09-19 17:00:31 +00001158 pEq = sqlite3PExpr(pParse, TK_EQ,
dan1da40a32009-09-19 17:00:31 +00001159 sqlite3PExpr(pParse, TK_DOT,
1160 sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
1161 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
dan652ac1d2009-09-29 16:38:59 +00001162 , 0),
1163 sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol)
dan1da40a32009-09-19 17:00:31 +00001164 , 0);
dan29c7f9c2009-09-22 15:53:47 +00001165 pWhere = sqlite3ExprAnd(db, pWhere, pEq);
dan1da40a32009-09-19 17:00:31 +00001166
drh788536b2009-09-23 03:01:58 +00001167 /* For ON UPDATE, construct the next term of the WHEN clause.
1168 ** The final WHEN clause will be like this:
1169 **
1170 ** WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN)
1171 */
1172 if( pChanges ){
1173 pEq = sqlite3PExpr(pParse, TK_IS,
1174 sqlite3PExpr(pParse, TK_DOT,
1175 sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
1176 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol),
1177 0),
1178 sqlite3PExpr(pParse, TK_DOT,
1179 sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
1180 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol),
1181 0),
1182 0);
1183 pWhen = sqlite3ExprAnd(db, pWhen, pEq);
1184 }
1185
dan9277efa2009-09-28 11:54:21 +00001186 if( action!=OE_Restrict && (action!=OE_Cascade || pChanges) ){
dan1da40a32009-09-19 17:00:31 +00001187 Expr *pNew;
1188 if( action==OE_Cascade ){
1189 pNew = sqlite3PExpr(pParse, TK_DOT,
1190 sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
1191 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
1192 , 0);
1193 }else if( action==OE_SetDflt ){
dan934ce302009-09-22 16:08:58 +00001194 Expr *pDflt = pFKey->pFrom->aCol[iFromCol].pDflt;
dan1da40a32009-09-19 17:00:31 +00001195 if( pDflt ){
1196 pNew = sqlite3ExprDup(db, pDflt, 0);
1197 }else{
1198 pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
1199 }
1200 }else{
1201 pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
1202 }
1203 pList = sqlite3ExprListAppend(pParse, pList, pNew);
1204 sqlite3ExprListSetName(pParse, pList, &tFromCol, 0);
1205 }
1206 }
dan29c7f9c2009-09-22 15:53:47 +00001207 sqlite3DbFree(db, aiCol);
dan1da40a32009-09-19 17:00:31 +00001208
dan9277efa2009-09-28 11:54:21 +00001209 zFrom = pFKey->pFrom->zName;
1210 nFrom = sqlite3Strlen30(zFrom);
1211
1212 if( action==OE_Restrict ){
1213 Token tFrom;
1214 Expr *pRaise;
1215
1216 tFrom.z = zFrom;
1217 tFrom.n = nFrom;
drhf9c8ce32013-11-05 13:33:55 +00001218 pRaise = sqlite3Expr(db, TK_RAISE, "FOREIGN KEY constraint failed");
dan9277efa2009-09-28 11:54:21 +00001219 if( pRaise ){
1220 pRaise->affinity = OE_Abort;
1221 }
1222 pSelect = sqlite3SelectNew(pParse,
1223 sqlite3ExprListAppend(pParse, 0, pRaise),
1224 sqlite3SrcListAppend(db, 0, &tFrom, 0),
1225 pWhere,
1226 0, 0, 0, 0, 0, 0
1227 );
1228 pWhere = 0;
1229 }
1230
drhb2468952010-07-23 17:06:32 +00001231 /* Disable lookaside memory allocation */
dan29c7f9c2009-09-22 15:53:47 +00001232 enableLookaside = db->lookaside.bEnabled;
drh46803c32009-09-24 14:27:33 +00001233 db->lookaside.bEnabled = 0;
dan29c7f9c2009-09-22 15:53:47 +00001234
dan29c7f9c2009-09-22 15:53:47 +00001235 pTrigger = (Trigger *)sqlite3DbMallocZero(db,
1236 sizeof(Trigger) + /* struct Trigger */
1237 sizeof(TriggerStep) + /* Single step in trigger program */
1238 nFrom + 1 /* Space for pStep->target.z */
1239 );
1240 if( pTrigger ){
1241 pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
1242 pStep->target.z = (char *)&pStep[1];
1243 pStep->target.n = nFrom;
1244 memcpy((char *)pStep->target.z, zFrom, nFrom);
1245
1246 pStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
1247 pStep->pExprList = sqlite3ExprListDup(db, pList, EXPRDUP_REDUCE);
dan9277efa2009-09-28 11:54:21 +00001248 pStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
drh788536b2009-09-23 03:01:58 +00001249 if( pWhen ){
1250 pWhen = sqlite3PExpr(pParse, TK_NOT, pWhen, 0, 0);
1251 pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE);
1252 }
dan29c7f9c2009-09-22 15:53:47 +00001253 }
1254
1255 /* Re-enable the lookaside buffer, if it was disabled earlier. */
1256 db->lookaside.bEnabled = enableLookaside;
1257
drh788536b2009-09-23 03:01:58 +00001258 sqlite3ExprDelete(db, pWhere);
1259 sqlite3ExprDelete(db, pWhen);
1260 sqlite3ExprListDelete(db, pList);
dan9277efa2009-09-28 11:54:21 +00001261 sqlite3SelectDelete(db, pSelect);
dan29c7f9c2009-09-22 15:53:47 +00001262 if( db->mallocFailed==1 ){
1263 fkTriggerDelete(db, pTrigger);
1264 return 0;
1265 }
drhb07028f2011-10-14 21:49:18 +00001266 assert( pStep!=0 );
dan1da40a32009-09-19 17:00:31 +00001267
dan9277efa2009-09-28 11:54:21 +00001268 switch( action ){
1269 case OE_Restrict:
1270 pStep->op = TK_SELECT;
1271 break;
1272 case OE_Cascade:
1273 if( !pChanges ){
1274 pStep->op = TK_DELETE;
1275 break;
1276 }
1277 default:
1278 pStep->op = TK_UPDATE;
1279 }
dan1da40a32009-09-19 17:00:31 +00001280 pStep->pTrig = pTrigger;
1281 pTrigger->pSchema = pTab->pSchema;
1282 pTrigger->pTabSchema = pTab->pSchema;
dan8099ce62009-09-23 08:43:35 +00001283 pFKey->apTrigger[iAction] = pTrigger;
1284 pTrigger->op = (pChanges ? TK_UPDATE : TK_DELETE);
dan1da40a32009-09-19 17:00:31 +00001285 }
1286
1287 return pTrigger;
1288}
1289
dan1da40a32009-09-19 17:00:31 +00001290/*
1291** This function is called when deleting or updating a row to implement
1292** any required CASCADE, SET NULL or SET DEFAULT actions.
1293*/
1294void sqlite3FkActions(
1295 Parse *pParse, /* Parse context */
1296 Table *pTab, /* Table being updated or deleted from */
1297 ExprList *pChanges, /* Change-list for UPDATE, NULL for DELETE */
dan8ff2d952013-09-05 18:40:29 +00001298 int regOld, /* Address of array containing old row */
1299 int *aChange, /* Array indicating UPDATEd columns (or 0) */
1300 int bChngRowid /* True if rowid is UPDATEd */
dan1da40a32009-09-19 17:00:31 +00001301){
1302 /* If foreign-key support is enabled, iterate through all FKs that
1303 ** refer to table pTab. If there is an action associated with the FK
1304 ** for this operation (either update or delete), invoke the associated
1305 ** trigger sub-program. */
1306 if( pParse->db->flags&SQLITE_ForeignKeys ){
1307 FKey *pFKey; /* Iterator variable */
dan432cc5b2009-09-26 17:51:48 +00001308 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
dan8ff2d952013-09-05 18:40:29 +00001309 if( aChange==0 || fkParentIsModified(pTab, pFKey, aChange, bChngRowid) ){
1310 Trigger *pAct = fkActionTrigger(pParse, pTab, pFKey, pChanges);
1311 if( pAct ){
1312 sqlite3CodeRowTriggerDirect(pParse, pAct, pTab, regOld, OE_Abort, 0);
1313 }
dan1da40a32009-09-19 17:00:31 +00001314 }
1315 }
1316 }
1317}
1318
dan75cbd982009-09-21 16:06:03 +00001319#endif /* ifndef SQLITE_OMIT_TRIGGER */
1320
dan1da40a32009-09-19 17:00:31 +00001321/*
1322** Free all memory associated with foreign key definitions attached to
1323** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash
1324** hash table.
1325*/
dan1feeaed2010-07-23 15:41:47 +00001326void sqlite3FkDelete(sqlite3 *db, Table *pTab){
dan1da40a32009-09-19 17:00:31 +00001327 FKey *pFKey; /* Iterator variable */
1328 FKey *pNext; /* Copy of pFKey->pNextFrom */
1329
drh21206082011-04-04 18:22:02 +00001330 assert( db==0 || sqlite3SchemaMutexHeld(db, 0, pTab->pSchema) );
dan1da40a32009-09-19 17:00:31 +00001331 for(pFKey=pTab->pFKey; pFKey; pFKey=pNext){
1332
1333 /* Remove the FK from the fkeyHash hash table. */
dand46def72010-07-24 11:28:28 +00001334 if( !db || db->pnBytesFreed==0 ){
1335 if( pFKey->pPrevTo ){
1336 pFKey->pPrevTo->pNextTo = pFKey->pNextTo;
1337 }else{
1338 void *p = (void *)pFKey->pNextTo;
1339 const char *z = (p ? pFKey->pNextTo->zTo : pFKey->zTo);
1340 sqlite3HashInsert(&pTab->pSchema->fkeyHash, z, sqlite3Strlen30(z), p);
1341 }
1342 if( pFKey->pNextTo ){
1343 pFKey->pNextTo->pPrevTo = pFKey->pPrevTo;
1344 }
dan1da40a32009-09-19 17:00:31 +00001345 }
dand46def72010-07-24 11:28:28 +00001346
1347 /* EV: R-30323-21917 Each foreign key constraint in SQLite is
1348 ** classified as either immediate or deferred.
1349 */
1350 assert( pFKey->isDeferred==0 || pFKey->isDeferred==1 );
dan1da40a32009-09-19 17:00:31 +00001351
1352 /* Delete any triggers created to implement actions for this FK. */
dan75cbd982009-09-21 16:06:03 +00001353#ifndef SQLITE_OMIT_TRIGGER
dan1feeaed2010-07-23 15:41:47 +00001354 fkTriggerDelete(db, pFKey->apTrigger[0]);
1355 fkTriggerDelete(db, pFKey->apTrigger[1]);
dan75cbd982009-09-21 16:06:03 +00001356#endif
dan1da40a32009-09-19 17:00:31 +00001357
dan1da40a32009-09-19 17:00:31 +00001358 pNext = pFKey->pNextFrom;
dan1feeaed2010-07-23 15:41:47 +00001359 sqlite3DbFree(db, pFKey);
dan1da40a32009-09-19 17:00:31 +00001360 }
1361}
dan75cbd982009-09-21 16:06:03 +00001362#endif /* ifndef SQLITE_OMIT_FOREIGN_KEY */