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, SQLITE_CONSTRAINT
25
** is returned and the current statement transaction rolled back. If a
26
** deferred foreign key constraint is violated, no action is taken
27
** immediately. However if the application attempts to commit the
28
** transaction before fixing the constraint violation, the attempt fails.
30
** Deferred constraints are implemented using a simple counter associated
31
** with the database handle. The counter is set to zero each time a
32
** database transaction is opened. Each time a statement is executed
33
** that causes a foreign key violation, the counter is incremented. Each
34
** time a statement is executed that removes an existing violation from
35
** the database, the counter is decremented. When the transaction is
36
** committed, the commit fails if the current value of the counter is
37
** greater than zero. This scheme has two big drawbacks:
39
** * When a commit fails due to a deferred foreign key constraint,
40
** there is no way to tell which foreign constraint is not satisfied,
41
** or which row it is not satisfied for.
43
** * If the database contains foreign key violations when the
44
** transaction is opened, this may cause the mechanism to malfunction.
46
** Despite these problems, this approach is adopted as it seems simpler
47
** than the alternatives.
51
** I.1) For each FK for which the table is the child table, search
52
** the parent table for a match. If none is found increment the
53
** constraint counter.
55
** I.2) For each FK for which the table is the parent table,
56
** search the child table for rows that correspond to the new
57
** row in the parent table. Decrement the counter for each row
58
** found (as the constraint is now satisfied).
62
** D.1) For each FK for which the table is the child table,
63
** search the parent table for a row that corresponds to the
64
** deleted row in the child table. If such a row is not found,
65
** decrement the counter.
67
** D.2) For each FK for which the table is the parent table, search
68
** the child table for rows that correspond to the deleted row
69
** in the parent table. For each found increment the counter.
73
** An UPDATE command requires that all 4 steps above are taken, but only
74
** for FK constraints for which the affected columns are actually
75
** modified (values must be compared at runtime).
77
** Note that I.1 and D.1 are very similar operations, as are I.2 and D.2.
78
** This simplifies the implementation a bit.
80
** For the purposes of immediate FK constraints, the OR REPLACE conflict
81
** resolution is considered to delete rows before the new row is inserted.
82
** If a delete caused by OR REPLACE violates an FK constraint, an exception
83
** is thrown, even if the FK constraint would be satisfied after the new
86
** Immediate constraints are usually handled similarly. The only difference
87
** is that the counter used is stored as part of each individual statement
88
** object (struct Vdbe). If, after the statement has run, its immediate
89
** constraint counter is greater than zero, it returns SQLITE_CONSTRAINT
90
** and the statement transaction is rolled back. An exception is an INSERT
91
** statement that inserts a single row only (no triggers). In this case,
92
** instead of using a counter, an exception is thrown immediately if the
93
** INSERT violates a foreign key constraint. This is necessary as such
94
** an INSERT does not open a statement transaction.
96
** TODO: How should dropping a table be handled? How should renaming a
103
** Before coding an UPDATE or DELETE row operation, the code-generator
104
** for those two operations needs to know whether or not the operation
105
** requires any FK processing and, if so, which columns of the original
106
** row are required by the FK processing VDBE code (i.e. if FKs were
107
** implemented using triggers, which of the old.* columns would be
108
** accessed). No information is required by the code-generator before
109
** coding an INSERT operation. The functions used by the UPDATE/DELETE
110
** generation code to query for this information are:
112
** sqlite3FkRequired() - Test to see if FK processing is required.
113
** sqlite3FkOldmask() - Query for the set of required old.* columns.
116
** Externally accessible module functions
117
** --------------------------------------
119
** sqlite3FkCheck() - Check for foreign key violations.
120
** sqlite3FkActions() - Code triggers for ON UPDATE/ON DELETE actions.
121
** sqlite3FkDelete() - Delete an FKey structure.
125
** VDBE Calling Convention
126
** -----------------------
130
** For the following INSERT statement:
132
** CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c);
133
** INSERT INTO t1 VALUES(1, 2, 3.1);
135
** Register (x): 2 (type integer)
136
** Register (x+1): 1 (type integer)
137
** Register (x+2): NULL (type NULL)
138
** Register (x+3): 3.1 (type real)
142
** A foreign key constraint requires that the key columns in the parent
143
** table are collectively subject to a UNIQUE or PRIMARY KEY constraint.
144
** Given that pParent is the parent table for foreign key constraint pFKey,
145
** search the schema a unique index on the parent key columns.
147
** If successful, zero is returned. If the parent key is an INTEGER PRIMARY
148
** KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx
149
** is set to point to the unique index.
151
** If the parent key consists of a single column (the foreign key constraint
152
** is not a composite foreign key), output variable *paiCol is set to NULL.
153
** Otherwise, it is set to point to an allocated array of size N, where
154
** N is the number of columns in the parent key. The first element of the
155
** array is the index of the child table column that is mapped by the FK
156
** constraint to the parent table column stored in the left-most column
157
** of index *ppIdx. The second element of the array is the index of the
158
** child table column that corresponds to the second left-most column of
159
** *ppIdx, and so on.
161
** If the required index cannot be found, either because:
163
** 1) The named parent key columns do not exist, or
165
** 2) The named parent key columns do exist, but are not subject to a
166
** UNIQUE or PRIMARY KEY constraint, or
168
** 3) No parent key columns were provided explicitly as part of the
169
** foreign key definition, and the parent table does not have a
172
** 4) No parent key columns were provided explicitly as part of the
173
** foreign key definition, and the PRIMARY KEY of the parent table
174
** consists of a a different number of columns to the child key in
177
** then non-zero is returned, and a "foreign key mismatch" error loaded
178
** into pParse. If an OOM error occurs, non-zero is returned and the
179
** pParse->db->mallocFailed flag is set.
181
static int locateFkeyIndex(
182
Parse *pParse, /* Parse context to store any error in */
183
Table *pParent, /* Parent table of FK constraint pFKey */
184
FKey *pFKey, /* Foreign key to find index for */
185
Index **ppIdx, /* OUT: Unique index on parent table */
186
int **paiCol /* OUT: Map of index columns in pFKey */
188
Index *pIdx = 0; /* Value to return via *ppIdx */
189
int *aiCol = 0; /* Value to return via *paiCol */
190
int nCol = pFKey->nCol; /* Number of columns in parent key */
191
char *zKey = pFKey->aCol[0].zCol; /* Name of left-most parent key column */
193
/* The caller is responsible for zeroing output parameters. */
194
assert( ppIdx && *ppIdx==0 );
195
assert( !paiCol || *paiCol==0 );
198
/* If this is a non-composite (single column) foreign key, check if it
199
** maps to the INTEGER PRIMARY KEY of table pParent. If so, leave *ppIdx
200
** and *paiCol set to zero and return early.
202
** Otherwise, for a composite foreign key (more than one column), allocate
203
** space for the aiCol array (returned via output parameter *paiCol).
204
** Non-composite foreign keys do not require the aiCol array.
207
/* The FK maps to the IPK if any of the following are true:
209
** 1) There is an INTEGER PRIMARY KEY column and the FK is implicitly
210
** mapped to the primary key of table pParent, or
211
** 2) The FK is explicitly mapped to a column declared as INTEGER
214
if( pParent->iPKey>=0 ){
215
if( !zKey ) return 0;
216
if( !sqlite3StrICmp(pParent->aCol[pParent->iPKey].zName, zKey) ) return 0;
220
aiCol = (int *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int));
221
if( !aiCol ) return 1;
225
for(pIdx=pParent->pIndex; pIdx; pIdx=pIdx->pNext){
226
if( pIdx->nColumn==nCol && pIdx->onError!=OE_None ){
227
/* pIdx is a UNIQUE index (or a PRIMARY KEY) and has the right number
228
** of columns. If each indexed column corresponds to a foreign key
229
** column of pFKey, then this index is a winner. */
232
/* If zKey is NULL, then this foreign key is implicitly mapped to
233
** the PRIMARY KEY of table pParent. The PRIMARY KEY index may be
234
** identified by the test (Index.autoIndex==2). */
235
if( pIdx->autoIndex==2 ){
238
for(i=0; i<nCol; i++) aiCol[i] = pFKey->aCol[i].iFrom;
243
/* If zKey is non-NULL, then this foreign key was declared to
244
** map to an explicit list of columns in table pParent. Check if this
245
** index matches those columns. Also, check that the index uses
246
** the default collation sequences for each column. */
248
for(i=0; i<nCol; i++){
249
int iCol = pIdx->aiColumn[i]; /* Index of column in parent tbl */
250
char *zDfltColl; /* Def. collation for column */
251
char *zIdxCol; /* Name of indexed column */
253
/* If the index uses a collation sequence that is different from
254
** the default collation sequence for the column, this index is
255
** unusable. Bail out early in this case. */
256
zDfltColl = pParent->aCol[iCol].zColl;
258
zDfltColl = "BINARY";
260
if( sqlite3StrICmp(pIdx->azColl[i], zDfltColl) ) break;
262
zIdxCol = pParent->aCol[iCol].zName;
263
for(j=0; j<nCol; j++){
264
if( sqlite3StrICmp(pFKey->aCol[j].zCol, zIdxCol)==0 ){
265
if( aiCol ) aiCol[i] = pFKey->aCol[j].iFrom;
271
if( i==nCol ) break; /* pIdx is usable */
277
if( !pParse->disableTriggers ){
278
sqlite3ErrorMsg(pParse, "foreign key mismatch");
280
sqlite3DbFree(pParse->db, aiCol);
289
** This function is called when a row is inserted into or deleted from the
290
** child table of foreign key constraint pFKey. If an SQL UPDATE is executed
291
** on the child table of pFKey, this function is invoked twice for each row
292
** affected - once to "delete" the old row, and then again to "insert" the
295
** Each time it is called, this function generates VDBE code to locate the
296
** row in the parent table that corresponds to the row being inserted into
297
** or deleted from the child table. If the parent row can be found, no
298
** special action is taken. Otherwise, if the parent row can *not* be
299
** found in the parent table:
301
** Operation | FK type | Action taken
302
** --------------------------------------------------------------------------
303
** INSERT immediate Increment the "immediate constraint counter".
305
** DELETE immediate Decrement the "immediate constraint counter".
307
** INSERT deferred Increment the "deferred constraint counter".
309
** DELETE deferred Decrement the "deferred constraint counter".
311
** These operations are identified in the comment at the top of this file
312
** (fkey.c) as "I.1" and "D.1".
314
static void fkLookupParent(
315
Parse *pParse, /* Parse context */
316
int iDb, /* Index of database housing pTab */
317
Table *pTab, /* Parent table of FK pFKey */
318
Index *pIdx, /* Unique index on parent key columns in pTab */
319
FKey *pFKey, /* Foreign key constraint */
320
int *aiCol, /* Map from parent key columns to child table columns */
321
int regData, /* Address of array containing child table row */
322
int nIncr, /* Increment constraint counter by this */
323
int isIgnore /* If true, pretend pTab contains all NULL values */
325
int i; /* Iterator variable */
326
Vdbe *v = sqlite3GetVdbe(pParse); /* Vdbe to add code to */
327
int iCur = pParse->nTab - 1; /* Cursor number to use */
328
int iOk = sqlite3VdbeMakeLabel(v); /* jump here if parent key found */
330
/* If nIncr is less than zero, then check at runtime if there are any
331
** outstanding constraints to resolve. If there are not, there is no need
332
** to check if deleting this row resolves any outstanding violations.
334
** Check if any of the key columns in the child table row are NULL. If
335
** any are, then the constraint is considered satisfied. No need to
336
** search for a matching row in the parent table. */
338
sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, iOk);
340
for(i=0; i<pFKey->nCol; i++){
341
int iReg = aiCol[i] + regData + 1;
342
sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk);
347
/* If pIdx is NULL, then the parent key is the INTEGER PRIMARY KEY
348
** column of the parent table (table pTab). */
349
int iMustBeInt; /* Address of MustBeInt instruction */
350
int regTemp = sqlite3GetTempReg(pParse);
352
/* Invoke MustBeInt to coerce the child key value to an integer (i.e.
353
** apply the affinity of the parent key). If this fails, then there
354
** is no matching parent key. Before using MustBeInt, make a copy of
355
** the value. Otherwise, the value inserted into the child key column
356
** will have INTEGER affinity applied to it, which may not be correct. */
357
sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[0]+1+regData, regTemp);
358
iMustBeInt = sqlite3VdbeAddOp2(v, OP_MustBeInt, regTemp, 0);
360
/* If the parent table is the same as the child table, and we are about
361
** to increment the constraint-counter (i.e. this is an INSERT operation),
362
** then check if the row being inserted matches itself. If so, do not
363
** increment the constraint-counter. */
364
if( pTab==pFKey->pFrom && nIncr==1 ){
365
sqlite3VdbeAddOp3(v, OP_Eq, regData, iOk, regTemp);
368
sqlite3OpenTable(pParse, iCur, iDb, pTab, OP_OpenRead);
369
sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, regTemp);
370
sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
371
sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
372
sqlite3VdbeJumpHere(v, iMustBeInt);
373
sqlite3ReleaseTempReg(pParse, regTemp);
375
int nCol = pFKey->nCol;
376
int regTemp = sqlite3GetTempRange(pParse, nCol);
377
int regRec = sqlite3GetTempReg(pParse);
378
KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
380
sqlite3VdbeAddOp3(v, OP_OpenRead, iCur, pIdx->tnum, iDb);
381
sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
382
for(i=0; i<nCol; i++){
383
sqlite3VdbeAddOp2(v, OP_SCopy, aiCol[i]+1+regData, regTemp+i);
386
/* If the parent table is the same as the child table, and we are about
387
** to increment the constraint-counter (i.e. this is an INSERT operation),
388
** then check if the row being inserted matches itself. If so, do not
389
** increment the constraint-counter. */
390
if( pTab==pFKey->pFrom && nIncr==1 ){
391
int iJump = sqlite3VdbeCurrentAddr(v) + nCol + 1;
392
for(i=0; i<nCol; i++){
393
int iChild = aiCol[i]+1+regData;
394
int iParent = pIdx->aiColumn[i]+1+regData;
395
sqlite3VdbeAddOp3(v, OP_Ne, iChild, iJump, iParent);
397
sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk);
400
sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec);
401
sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v, pIdx), 0);
402
sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0);
404
sqlite3ReleaseTempReg(pParse, regRec);
405
sqlite3ReleaseTempRange(pParse, regTemp, nCol);
409
if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
410
/* Special case: If this is an INSERT statement that will insert exactly
411
** one row into the table, raise a constraint immediately instead of
412
** incrementing a counter. This is necessary as the VM code is being
413
** generated for will not open a statement transaction. */
415
sqlite3HaltConstraint(
416
pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
419
if( nIncr>0 && pFKey->isDeferred==0 ){
420
sqlite3ParseToplevel(pParse)->mayAbort = 1;
422
sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
425
sqlite3VdbeResolveLabel(v, iOk);
426
sqlite3VdbeAddOp1(v, OP_Close, iCur);
430
** This function is called to generate code executed when a row is deleted
431
** from the parent table of foreign key constraint pFKey and, if pFKey is
432
** deferred, when a row is inserted into the same table. When generating
433
** code for an SQL UPDATE operation, this function may be called twice -
434
** once to "delete" the old row and once to "insert" the new row.
436
** The code generated by this function scans through the rows in the child
437
** table that correspond to the parent table row being deleted or inserted.
438
** For each child row found, one of the following actions is taken:
440
** Operation | FK type | Action taken
441
** --------------------------------------------------------------------------
442
** DELETE immediate Increment the "immediate constraint counter".
443
** Or, if the ON (UPDATE|DELETE) action is RESTRICT,
444
** throw a "foreign key constraint failed" exception.
446
** INSERT immediate Decrement the "immediate constraint counter".
448
** DELETE deferred Increment the "deferred constraint counter".
449
** Or, if the ON (UPDATE|DELETE) action is RESTRICT,
450
** throw a "foreign key constraint failed" exception.
452
** INSERT deferred Decrement the "deferred constraint counter".
454
** These operations are identified in the comment at the top of this file
455
** (fkey.c) as "I.2" and "D.2".
457
static void fkScanChildren(
458
Parse *pParse, /* Parse context */
459
SrcList *pSrc, /* SrcList containing the table to scan */
461
Index *pIdx, /* Foreign key index */
462
FKey *pFKey, /* Foreign key relationship */
463
int *aiCol, /* Map from pIdx cols to child table cols */
464
int regData, /* Referenced table data starts here */
465
int nIncr /* Amount to increment deferred counter by */
467
sqlite3 *db = pParse->db; /* Database handle */
468
int i; /* Iterator variable */
469
Expr *pWhere = 0; /* WHERE clause to scan with */
470
NameContext sNameContext; /* Context used to resolve WHERE clause */
471
WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */
472
int iFkIfZero = 0; /* Address of OP_FkIfZero */
473
Vdbe *v = sqlite3GetVdbe(pParse);
475
assert( !pIdx || pIdx->pTable==pTab );
478
iFkIfZero = sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0);
481
/* Create an Expr object representing an SQL expression like:
483
** <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ...
485
** The collation sequence used for the comparison should be that of
486
** the parent key columns. The affinity of the parent key column should
487
** be applied to each child key value before the comparison takes place.
489
for(i=0; i<pFKey->nCol; i++){
490
Expr *pLeft; /* Value from parent table row */
491
Expr *pRight; /* Column ref to child table */
492
Expr *pEq; /* Expression (pLeft = pRight) */
493
int iCol; /* Index of column in child table */
494
const char *zCol; /* Name of column in child table */
496
pLeft = sqlite3Expr(db, TK_REGISTER, 0);
498
/* Set the collation sequence and affinity of the LHS of each TK_EQ
499
** expression to the parent key column defaults. */
502
iCol = pIdx->aiColumn[i];
503
pCol = &pIdx->pTable->aCol[iCol];
504
pLeft->iTable = regData+iCol+1;
505
pLeft->affinity = pCol->affinity;
506
pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl);
508
pLeft->iTable = regData;
509
pLeft->affinity = SQLITE_AFF_INTEGER;
512
iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
514
zCol = pFKey->pFrom->aCol[iCol].zName;
515
pRight = sqlite3Expr(db, TK_ID, zCol);
516
pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0);
517
pWhere = sqlite3ExprAnd(db, pWhere, pEq);
520
/* If the child table is the same as the parent table, and this scan
521
** is taking place as part of a DELETE operation (operation D.2), omit the
522
** row being deleted from the scan by adding ($rowid != rowid) to the WHERE
523
** clause, where $rowid is the rowid of the row being deleted. */
524
if( pTab==pFKey->pFrom && nIncr>0 ){
525
Expr *pEq; /* Expression (pLeft = pRight) */
526
Expr *pLeft; /* Value from parent table row */
527
Expr *pRight; /* Column ref to child table */
528
pLeft = sqlite3Expr(db, TK_REGISTER, 0);
529
pRight = sqlite3Expr(db, TK_COLUMN, 0);
530
if( pLeft && pRight ){
531
pLeft->iTable = regData;
532
pLeft->affinity = SQLITE_AFF_INTEGER;
533
pRight->iTable = pSrc->a[0].iCursor;
534
pRight->iColumn = -1;
536
pEq = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0);
537
pWhere = sqlite3ExprAnd(db, pWhere, pEq);
540
/* Resolve the references in the WHERE clause. */
541
memset(&sNameContext, 0, sizeof(NameContext));
542
sNameContext.pSrcList = pSrc;
543
sNameContext.pParse = pParse;
544
sqlite3ResolveExprNames(&sNameContext, pWhere);
546
/* Create VDBE to loop through the entries in pSrc that match the WHERE
547
** clause. If the constraint is not deferred, throw an exception for
548
** each row found. Otherwise, for deferred constraints, increment the
549
** deferred constraint counter by nIncr for each row selected. */
550
pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0);
551
if( nIncr>0 && pFKey->isDeferred==0 ){
552
sqlite3ParseToplevel(pParse)->mayAbort = 1;
554
sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr);
556
sqlite3WhereEnd(pWInfo);
559
/* Clean up the WHERE clause constructed above. */
560
sqlite3ExprDelete(db, pWhere);
562
sqlite3VdbeJumpHere(v, iFkIfZero);
567
** This function returns a pointer to the head of a linked list of FK
568
** constraints for which table pTab is the parent table. For example,
569
** given the following schema:
571
** CREATE TABLE t1(a PRIMARY KEY);
572
** CREATE TABLE t2(b REFERENCES t1(a);
574
** Calling this function with table "t1" as an argument returns a pointer
575
** to the FKey structure representing the foreign key constraint on table
576
** "t2". Calling this function with "t2" as the argument would return a
577
** NULL pointer (as there are no FK constraints for which t2 is the parent
580
FKey *sqlite3FkReferences(Table *pTab){
581
int nName = sqlite3Strlen30(pTab->zName);
582
return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName);
586
** The second argument is a Trigger structure allocated by the
587
** fkActionTrigger() routine. This function deletes the Trigger structure
588
** and all of its sub-components.
590
** The Trigger structure or any of its sub-components may be allocated from
591
** the lookaside buffer belonging to database handle dbMem.
593
static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){
595
TriggerStep *pStep = p->step_list;
596
sqlite3ExprDelete(dbMem, pStep->pWhere);
597
sqlite3ExprListDelete(dbMem, pStep->pExprList);
598
sqlite3SelectDelete(dbMem, pStep->pSelect);
599
sqlite3ExprDelete(dbMem, p->pWhen);
600
sqlite3DbFree(dbMem, p);
605
** This function is called to generate code that runs when table pTab is
606
** being dropped from the database. The SrcList passed as the second argument
607
** to this function contains a single entry guaranteed to resolve to
610
** Normally, no code is required. However, if either
612
** (a) The table is the parent table of a FK constraint, or
613
** (b) The table is the child table of a deferred FK constraint and it is
614
** determined at runtime that there are outstanding deferred FK
615
** constraint violations in the database,
617
** then the equivalent of "DELETE FROM <tbl>" is executed before dropping
618
** the table from the database. Triggers are disabled while running this
619
** DELETE, but foreign key actions are not.
621
void sqlite3FkDropTable(Parse *pParse, SrcList *pName, Table *pTab){
622
sqlite3 *db = pParse->db;
623
if( (db->flags&SQLITE_ForeignKeys) && !IsVirtual(pTab) && !pTab->pSelect ){
625
Vdbe *v = sqlite3GetVdbe(pParse);
627
assert( v ); /* VDBE has already been allocated */
628
if( sqlite3FkReferences(pTab)==0 ){
629
/* Search for a deferred foreign key constraint for which this table
630
** is the child table. If one cannot be found, return without
631
** generating any VDBE code. If one can be found, then jump over
632
** the entire DELETE if there are no outstanding deferred constraints
633
** when this statement is run. */
635
for(p=pTab->pFKey; p; p=p->pNextFrom){
636
if( p->isDeferred ) break;
639
iSkip = sqlite3VdbeMakeLabel(v);
640
sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip);
643
pParse->disableTriggers = 1;
644
sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0);
645
pParse->disableTriggers = 0;
647
/* If the DELETE has generated immediate foreign key constraint
648
** violations, halt the VDBE and return an error at this point, before
649
** any modifications to the schema are made. This is because statement
650
** transactions are not able to rollback schema changes. */
651
sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2);
652
sqlite3HaltConstraint(
653
pParse, OE_Abort, "foreign key constraint failed", P4_STATIC
657
sqlite3VdbeResolveLabel(v, iSkip);
663
** This function is called when inserting, deleting or updating a row of
664
** table pTab to generate VDBE code to perform foreign key constraint
665
** processing for the operation.
667
** For a DELETE operation, parameter regOld is passed the index of the
668
** first register in an array of (pTab->nCol+1) registers containing the
669
** rowid of the row being deleted, followed by each of the column values
670
** of the row being deleted, from left to right. Parameter regNew is passed
671
** zero in this case.
673
** For an INSERT operation, regOld is passed zero and regNew is passed the
674
** first register of an array of (pTab->nCol+1) registers containing the new
677
** For an UPDATE operation, this function is called twice. Once before
678
** the original record is deleted from the table using the calling convention
679
** described for DELETE. Then again after the original record is deleted
680
** but before the new record is inserted using the INSERT convention.
683
Parse *pParse, /* Parse context */
684
Table *pTab, /* Row is being deleted from this table */
685
int regOld, /* Previous row data is stored here */
686
int regNew /* New row data is stored here */
688
sqlite3 *db = pParse->db; /* Database handle */
689
Vdbe *v; /* VM to write code to */
690
FKey *pFKey; /* Used to iterate through FKs */
691
int iDb; /* Index of database containing pTab */
692
const char *zDb; /* Name of database containing pTab */
693
int isIgnoreErrors = pParse->disableTriggers;
695
/* Exactly one of regOld and regNew should be non-zero. */
696
assert( (regOld==0)!=(regNew==0) );
698
/* If foreign-keys are disabled, this function is a no-op. */
699
if( (db->flags&SQLITE_ForeignKeys)==0 ) return;
701
v = sqlite3GetVdbe(pParse);
702
iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
703
zDb = db->aDb[iDb].zName;
705
/* Loop through all the foreign key constraints for which pTab is the
706
** child table (the table that the foreign key definition is part of). */
707
for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){
708
Table *pTo; /* Parent table of foreign key pFKey */
709
Index *pIdx = 0; /* Index on key columns in pTo */
716
/* Find the parent table of this foreign key. Also find a unique index
717
** on the parent key columns in the parent table. If either of these
718
** schema items cannot be located, set an error in pParse and return
720
if( pParse->disableTriggers ){
721
pTo = sqlite3FindTable(db, pFKey->zTo, zDb);
723
pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb);
725
if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){
726
if( !isIgnoreErrors || db->mallocFailed ) return;
729
assert( pFKey->nCol==1 || (aiFree && pIdx) );
734
iCol = pFKey->aCol[0].iFrom;
737
for(i=0; i<pFKey->nCol; i++){
738
if( aiCol[i]==pTab->iPKey ){
741
#ifndef SQLITE_OMIT_AUTHORIZATION
742
/* Request permission to read the parent key columns. If the
743
** authorization callback returns SQLITE_IGNORE, behave as if any
744
** values read from the parent table are NULL. */
747
char *zCol = pTo->aCol[pIdx ? pIdx->aiColumn[i] : pTo->iPKey].zName;
748
rcauth = sqlite3AuthReadCol(pParse, pTo->zName, zCol, iDb);
749
isIgnore = (rcauth==SQLITE_IGNORE);
754
/* Take a shared-cache advisory read-lock on the parent table. Allocate
755
** a cursor to use to search the unique index on the parent key columns
756
** in the parent table. */
757
sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName);
761
/* A row is being removed from the child table. Search for the parent.
762
** If the parent does not exist, removing the child row resolves an
763
** outstanding foreign key constraint violation. */
764
fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1,isIgnore);
767
/* A row is being added to the child table. If a parent row cannot
768
** be found, adding the child row has violated the FK constraint. */
769
fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1,isIgnore);
772
sqlite3DbFree(db, aiFree);
775
/* Loop through all the foreign key constraints that refer to this table */
776
for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
777
Index *pIdx = 0; /* Foreign key index for pFKey */
781
if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){
782
assert( regOld==0 && regNew!=0 );
783
/* Inserting a single row into a parent table cannot cause an immediate
784
** foreign key violation. So do nothing in this case. */
788
if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){
789
if( !isIgnoreErrors || db->mallocFailed ) return;
792
assert( aiCol || pFKey->nCol==1 );
794
/* Create a SrcList structure containing a single table (the table
795
** the foreign key that refers to this table is attached to). This
796
** is required for the sqlite3WhereXXX() interface. */
797
pSrc = sqlite3SrcListAppend(db, 0, 0, 0);
799
struct SrcList_item *pItem = pSrc->a;
800
pItem->pTab = pFKey->pFrom;
801
pItem->zName = pFKey->pFrom->zName;
803
pItem->iCursor = pParse->nTab++;
806
fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1);
809
/* If there is a RESTRICT action configured for the current operation
810
** on the parent table of this FK, then throw an exception
811
** immediately if the FK constraint is violated, even if this is a
812
** deferred trigger. That's what RESTRICT means. To defer checking
813
** the constraint, the FK should specify NO ACTION (represented
814
** using OE_None). NO ACTION is the default. */
815
fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1);
818
sqlite3SrcListDelete(db, pSrc);
820
sqlite3DbFree(db, aiCol);
824
#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x)))
827
** This function is called before generating code to update or delete a
828
** row contained in table pTab.
830
u32 sqlite3FkOldmask(
831
Parse *pParse, /* Parse context */
832
Table *pTab /* Table being modified */
835
if( pParse->db->flags&SQLITE_ForeignKeys ){
838
for(p=pTab->pFKey; p; p=p->pNextFrom){
839
for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom);
841
for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
843
locateFkeyIndex(pParse, pTab, p, &pIdx, 0);
845
for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]);
853
** This function is called before generating code to update or delete a
854
** row contained in table pTab. If the operation is a DELETE, then
855
** parameter aChange is passed a NULL value. For an UPDATE, aChange points
856
** to an array of size N, where N is the number of columns in table pTab.
857
** If the i'th column is not modified by the UPDATE, then the corresponding
858
** entry in the aChange[] array is set to -1. If the column is modified,
859
** the value is 0 or greater. Parameter chngRowid is set to true if the
860
** UPDATE statement modifies the rowid fields of the table.
862
** If any foreign key processing will be required, this function returns
863
** true. If there is no foreign key related processing, this function
866
int sqlite3FkRequired(
867
Parse *pParse, /* Parse context */
868
Table *pTab, /* Table being modified */
869
int *aChange, /* Non-NULL for UPDATE operations */
870
int chngRowid /* True for UPDATE that affects rowid */
872
if( pParse->db->flags&SQLITE_ForeignKeys ){
874
/* A DELETE operation. Foreign key processing is required if the
875
** table in question is either the child or parent table for any
876
** foreign key constraint. */
877
return (sqlite3FkReferences(pTab) || pTab->pFKey);
879
/* This is an UPDATE. Foreign key processing is only required if the
880
** operation modifies one or more child or parent key columns. */
884
/* Check if any child key columns are being modified. */
885
for(p=pTab->pFKey; p; p=p->pNextFrom){
886
for(i=0; i<p->nCol; i++){
887
int iChildKey = p->aCol[i].iFrom;
888
if( aChange[iChildKey]>=0 ) return 1;
889
if( iChildKey==pTab->iPKey && chngRowid ) return 1;
893
/* Check if any parent key columns are being modified. */
894
for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){
895
for(i=0; i<p->nCol; i++){
896
char *zKey = p->aCol[i].zCol;
898
for(iKey=0; iKey<pTab->nCol; iKey++){
899
Column *pCol = &pTab->aCol[iKey];
900
if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey) : pCol->isPrimKey) ){
901
if( aChange[iKey]>=0 ) return 1;
902
if( iKey==pTab->iPKey && chngRowid ) return 1;
913
** This function is called when an UPDATE or DELETE operation is being
914
** compiled on table pTab, which is the parent table of foreign-key pFKey.
915
** If the current operation is an UPDATE, then the pChanges parameter is
916
** passed a pointer to the list of columns being modified. If it is a
917
** DELETE, pChanges is passed a NULL pointer.
919
** It returns a pointer to a Trigger structure containing a trigger
920
** equivalent to the ON UPDATE or ON DELETE action specified by pFKey.
921
** If the action is "NO ACTION" or "RESTRICT", then a NULL pointer is
922
** returned (these actions require no special handling by the triggers
923
** sub-system, code for them is created by fkScanChildren()).
925
** For example, if pFKey is the foreign key and pTab is table "p" in
926
** the following schema:
928
** CREATE TABLE p(pk PRIMARY KEY);
929
** CREATE TABLE c(ck REFERENCES p ON DELETE CASCADE);
931
** then the returned trigger structure is equivalent to:
933
** CREATE TRIGGER ... DELETE ON p BEGIN
934
** DELETE FROM c WHERE ck = old.pk;
937
** The returned pointer is cached as part of the foreign key object. It
938
** is eventually freed along with the rest of the foreign key object by
939
** sqlite3FkDelete().
941
static Trigger *fkActionTrigger(
942
Parse *pParse, /* Parse context */
943
Table *pTab, /* Table being updated or deleted from */
944
FKey *pFKey, /* Foreign key to get action for */
945
ExprList *pChanges /* Change-list for UPDATE, NULL for DELETE */
947
sqlite3 *db = pParse->db; /* Database handle */
948
int action; /* One of OE_None, OE_Cascade etc. */
949
Trigger *pTrigger; /* Trigger definition to return */
950
int iAction = (pChanges!=0); /* 1 for UPDATE, 0 for DELETE */
952
action = pFKey->aAction[iAction];
953
pTrigger = pFKey->apTrigger[iAction];
955
if( action!=OE_None && !pTrigger ){
956
u8 enableLookaside; /* Copy of db->lookaside.bEnabled */
957
char const *zFrom; /* Name of child table */
958
int nFrom; /* Length in bytes of zFrom */
959
Index *pIdx = 0; /* Parent key index for this FK */
960
int *aiCol = 0; /* child table cols -> parent key cols */
961
TriggerStep *pStep = 0; /* First (only) step of trigger program */
962
Expr *pWhere = 0; /* WHERE clause of trigger step */
963
ExprList *pList = 0; /* Changes list if ON UPDATE CASCADE */
964
Select *pSelect = 0; /* If RESTRICT, "SELECT RAISE(...)" */
965
int i; /* Iterator variable */
966
Expr *pWhen = 0; /* WHEN clause for the trigger */
968
if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0;
969
assert( aiCol || pFKey->nCol==1 );
971
for(i=0; i<pFKey->nCol; i++){
972
Token tOld = { "old", 3 }; /* Literal "old" token */
973
Token tNew = { "new", 3 }; /* Literal "new" token */
974
Token tFromCol; /* Name of column in child table */
975
Token tToCol; /* Name of column in parent table */
976
int iFromCol; /* Idx of column in child table */
977
Expr *pEq; /* tFromCol = OLD.tToCol */
979
iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom;
980
assert( iFromCol>=0 );
981
tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid";
982
tFromCol.z = pFKey->pFrom->aCol[iFromCol].zName;
984
tToCol.n = sqlite3Strlen30(tToCol.z);
985
tFromCol.n = sqlite3Strlen30(tFromCol.z);
987
/* Create the expression "OLD.zToCol = zFromCol". It is important
988
** that the "OLD.zToCol" term is on the LHS of the = operator, so
989
** that the affinity and collation sequence associated with the
990
** parent table are used for the comparison. */
991
pEq = sqlite3PExpr(pParse, TK_EQ,
992
sqlite3PExpr(pParse, TK_DOT,
993
sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
994
sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
996
sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol)
998
pWhere = sqlite3ExprAnd(db, pWhere, pEq);
1000
/* For ON UPDATE, construct the next term of the WHEN clause.
1001
** The final WHEN clause will be like this:
1003
** WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN)
1006
pEq = sqlite3PExpr(pParse, TK_IS,
1007
sqlite3PExpr(pParse, TK_DOT,
1008
sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld),
1009
sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol),
1011
sqlite3PExpr(pParse, TK_DOT,
1012
sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
1013
sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol),
1016
pWhen = sqlite3ExprAnd(db, pWhen, pEq);
1019
if( action!=OE_Restrict && (action!=OE_Cascade || pChanges) ){
1021
if( action==OE_Cascade ){
1022
pNew = sqlite3PExpr(pParse, TK_DOT,
1023
sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew),
1024
sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol)
1026
}else if( action==OE_SetDflt ){
1027
Expr *pDflt = pFKey->pFrom->aCol[iFromCol].pDflt;
1029
pNew = sqlite3ExprDup(db, pDflt, 0);
1031
pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
1034
pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0);
1036
pList = sqlite3ExprListAppend(pParse, pList, pNew);
1037
sqlite3ExprListSetName(pParse, pList, &tFromCol, 0);
1040
sqlite3DbFree(db, aiCol);
1042
zFrom = pFKey->pFrom->zName;
1043
nFrom = sqlite3Strlen30(zFrom);
1045
if( action==OE_Restrict ){
1051
pRaise = sqlite3Expr(db, TK_RAISE, "foreign key constraint failed");
1053
pRaise->affinity = OE_Abort;
1055
pSelect = sqlite3SelectNew(pParse,
1056
sqlite3ExprListAppend(pParse, 0, pRaise),
1057
sqlite3SrcListAppend(db, 0, &tFrom, 0),
1064
/* In the current implementation, pTab->dbMem==0 for all tables except
1065
** for temporary tables used to describe subqueries. And temporary
1066
** tables do not have foreign key constraints. Hence, pTab->dbMem
1067
** should always be 0 there.
1069
enableLookaside = db->lookaside.bEnabled;
1070
db->lookaside.bEnabled = 0;
1072
pTrigger = (Trigger *)sqlite3DbMallocZero(db,
1073
sizeof(Trigger) + /* struct Trigger */
1074
sizeof(TriggerStep) + /* Single step in trigger program */
1075
nFrom + 1 /* Space for pStep->target.z */
1078
pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1];
1079
pStep->target.z = (char *)&pStep[1];
1080
pStep->target.n = nFrom;
1081
memcpy((char *)pStep->target.z, zFrom, nFrom);
1083
pStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE);
1084
pStep->pExprList = sqlite3ExprListDup(db, pList, EXPRDUP_REDUCE);
1085
pStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE);
1087
pWhen = sqlite3PExpr(pParse, TK_NOT, pWhen, 0, 0);
1088
pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE);
1092
/* Re-enable the lookaside buffer, if it was disabled earlier. */
1093
db->lookaside.bEnabled = enableLookaside;
1095
sqlite3ExprDelete(db, pWhere);
1096
sqlite3ExprDelete(db, pWhen);
1097
sqlite3ExprListDelete(db, pList);
1098
sqlite3SelectDelete(db, pSelect);
1099
if( db->mallocFailed==1 ){
1100
fkTriggerDelete(db, pTrigger);
1106
pStep->op = TK_SELECT;
1110
pStep->op = TK_DELETE;
1114
pStep->op = TK_UPDATE;
1116
pStep->pTrig = pTrigger;
1117
pTrigger->pSchema = pTab->pSchema;
1118
pTrigger->pTabSchema = pTab->pSchema;
1119
pFKey->apTrigger[iAction] = pTrigger;
1120
pTrigger->op = (pChanges ? TK_UPDATE : TK_DELETE);
1127
** This function is called when deleting or updating a row to implement
1128
** any required CASCADE, SET NULL or SET DEFAULT actions.
1130
void sqlite3FkActions(
1131
Parse *pParse, /* Parse context */
1132
Table *pTab, /* Table being updated or deleted from */
1133
ExprList *pChanges, /* Change-list for UPDATE, NULL for DELETE */
1134
int regOld /* Address of array containing old row */
1136
/* If foreign-key support is enabled, iterate through all FKs that
1137
** refer to table pTab. If there is an action associated with the FK
1138
** for this operation (either update or delete), invoke the associated
1139
** trigger sub-program. */
1140
if( pParse->db->flags&SQLITE_ForeignKeys ){
1141
FKey *pFKey; /* Iterator variable */
1142
for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){
1143
Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges);
1145
sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0);
1151
#endif /* ifndef SQLITE_OMIT_TRIGGER */
1154
** Free all memory associated with foreign key definitions attached to
1155
** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash
1158
void sqlite3FkDelete(Table *pTab){
1159
FKey *pFKey; /* Iterator variable */
1160
FKey *pNext; /* Copy of pFKey->pNextFrom */
1162
for(pFKey=pTab->pFKey; pFKey; pFKey=pNext){
1164
/* Remove the FK from the fkeyHash hash table. */
1165
if( pFKey->pPrevTo ){
1166
pFKey->pPrevTo->pNextTo = pFKey->pNextTo;
1168
void *data = (void *)pFKey->pNextTo;
1169
const char *z = (data ? pFKey->pNextTo->zTo : pFKey->zTo);
1170
sqlite3HashInsert(&pTab->pSchema->fkeyHash, z, sqlite3Strlen30(z), data);
1172
if( pFKey->pNextTo ){
1173
pFKey->pNextTo->pPrevTo = pFKey->pPrevTo;
1176
/* Delete any triggers created to implement actions for this FK. */
1177
#ifndef SQLITE_OMIT_TRIGGER
1178
fkTriggerDelete(pTab->dbMem, pFKey->apTrigger[0]);
1179
fkTriggerDelete(pTab->dbMem, pFKey->apTrigger[1]);
1182
/* EV: R-30323-21917 Each foreign key constraint in SQLite is
1183
** classified as either immediate or deferred.
1185
assert( pFKey->isDeferred==0 || pFKey->isDeferred==1 );
1187
pNext = pFKey->pNextFrom;
1188
sqlite3DbFree(pTab->dbMem, pFKey);
1191
#endif /* ifndef SQLITE_OMIT_FOREIGN_KEY */
added
removed
src/fkey.c
