1/* 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 17#ifndef SQLITE_OMIT_TRIGGER 18 19/* 20** Deferred and Immediate FKs 21** -------------------------- 22** 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. 29** 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: 38** 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. 42** 43** * If the database contains foreign key violations when the 44** transaction is opened, this may cause the mechanism to malfunction. 45** 46** Despite these problems, this approach is adopted as it seems simpler 47** than the alternatives. 48** 49** INSERT operations: 50** 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. 54** 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). 59** 60** DELETE operations: 61** 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. 66** 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. 70** 71** UPDATE operations: 72** 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). 76** 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. 79** 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 84** row is inserted. 85** 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. 95** 96** TODO: How should dropping a table be handled? How should renaming a 97** table be handled? 98** 99** 100** Query API Notes 101** --------------- 102** 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: 111** 112** sqlite3FkRequired() - Test to see if FK processing is required. 113** sqlite3FkOldmask() - Query for the set of required old.* columns. 114** 115** 116** Externally accessible module functions 117** -------------------------------------- 118** 119** sqlite3FkCheck() - Check for foreign key violations. 120** sqlite3FkActions() - Code triggers for ON UPDATE/ON DELETE actions. 121** sqlite3FkDelete() - Delete an FKey structure. 122*/ 123 124/* 125** VDBE Calling Convention 126** ----------------------- 127** 128** Example: 129** 130** For the following INSERT statement: 131** 132** CREATE TABLE t1(a, b INTEGER PRIMARY KEY, c); 133** INSERT INTO t1 VALUES(1, 2, 3.1); 134** 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) 139*/ 140 141/* 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. 146** 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. 150** 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. 160** 161** If the required index cannot be found, either because: 162** 163** 1) The named parent key columns do not exist, or 164** 165** 2) The named parent key columns do exist, but are not subject to a 166** UNIQUE or PRIMARY KEY constraint, or 167** 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 170** PRIMARY KEY, or 171** 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 175** the child table. 176** 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. 180*/ 181static 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 */ 187){ 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 */ 192 193 /* The caller is responsible for zeroing output parameters. */ 194 assert( ppIdx && *ppIdx==0 ); 195 assert( !paiCol || *paiCol==0 ); 196 assert( pParse ); 197 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. 201 ** 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. 205 */ 206 if( nCol==1 ){ 207 /* The FK maps to the IPK if any of the following are true: 208 ** 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 212 ** PRIMARY KEY. 213 */ 214 if( pParent->iPKey>=0 ){ 215 if( !zKey ) return 0; 216 if( !sqlite3StrICmp(pParent->aCol[pParent->iPKey].zName, zKey) ) return 0; 217 } 218 }else if( paiCol ){ 219 assert( nCol>1 ); 220 aiCol = (int *)sqlite3DbMallocRaw(pParse->db, nCol*sizeof(int)); 221 if( !aiCol ) return 1; 222 *paiCol = aiCol; 223 } 224 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. */ 230 231 if( zKey==0 ){ 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 ){ 236 if( aiCol ){ 237 int i; 238 for(i=0; i<nCol; i++) aiCol[i] = pFKey->aCol[i].iFrom; 239 } 240 break; 241 } 242 }else{ 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. */ 247 int i, j; 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 */ 252 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; 257 if( !zDfltColl ){ 258 zDfltColl = "BINARY"; 259 } 260 if( sqlite3StrICmp(pIdx->azColl[i], zDfltColl) ) break; 261 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; 266 break; 267 } 268 } 269 if( j==nCol ) break; 270 } 271 if( i==nCol ) break; /* pIdx is usable */ 272 } 273 } 274 } 275 276 if( !pIdx ){ 277 if( !pParse->disableTriggers ){ 278 sqlite3ErrorMsg(pParse, "foreign key mismatch"); 279 } 280 sqlite3DbFree(pParse->db, aiCol); 281 return 1; 282 } 283 284 *ppIdx = pIdx; 285 return 0; 286} 287 288/* 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 293** new row. 294** 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: 300** 301** Operation | FK type | Action taken 302** -------------------------------------------------------------------------- 303** INSERT immediate Increment the "immediate constraint counter". 304** 305** DELETE immediate Decrement the "immediate constraint counter". 306** 307** INSERT deferred Increment the "deferred constraint counter". 308** 309** DELETE deferred Decrement the "deferred constraint counter". 310** 311** These operations are identified in the comment at the top of this file 312** (fkey.c) as "I.1" and "D.1". 313*/ 314static 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 */ 324){ 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 */ 329 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. 333 ** 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. */ 337 if( nIncr<0 ){ 338 sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, iOk); 339 } 340 for(i=0; i<pFKey->nCol; i++){ 341 int iReg = aiCol[i] + regData + 1; 342 sqlite3VdbeAddOp2(v, OP_IsNull, iReg, iOk); 343 } 344 345 if( isIgnore==0 ){ 346 if( pIdx==0 ){ 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); 351 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); 359 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); 366 } 367 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); 374 }else{ 375 int nCol = pFKey->nCol; 376 int regTemp = sqlite3GetTempRange(pParse, nCol); 377 int regRec = sqlite3GetTempReg(pParse); 378 KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); 379 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_Copy, aiCol[i]+1+regData, regTemp+i); 384 } 385 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); 396 } 397 sqlite3VdbeAddOp2(v, OP_Goto, 0, iOk); 398 } 399 400 sqlite3VdbeAddOp3(v, OP_MakeRecord, regTemp, nCol, regRec); 401 sqlite3VdbeChangeP4(v, -1, sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT); 402 sqlite3VdbeAddOp4Int(v, OP_Found, iCur, iOk, regRec, 0); 403 404 sqlite3ReleaseTempReg(pParse, regRec); 405 sqlite3ReleaseTempRange(pParse, regTemp, nCol); 406 } 407 } 408 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. */ 414 assert( nIncr==1 ); 415 sqlite3HaltConstraint( 416 pParse, OE_Abort, "foreign key constraint failed", P4_STATIC 417 ); 418 }else{ 419 if( nIncr>0 && pFKey->isDeferred==0 ){ 420 sqlite3ParseToplevel(pParse)->mayAbort = 1; 421 } 422 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); 423 } 424 425 sqlite3VdbeResolveLabel(v, iOk); 426 sqlite3VdbeAddOp1(v, OP_Close, iCur); 427} 428 429/* 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. 435** 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: 439** 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. 445** 446** INSERT immediate Decrement the "immediate constraint counter". 447** 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. 451** 452** INSERT deferred Decrement the "deferred constraint counter". 453** 454** These operations are identified in the comment at the top of this file 455** (fkey.c) as "I.2" and "D.2". 456*/ 457static void fkScanChildren( 458 Parse *pParse, /* Parse context */ 459 SrcList *pSrc, /* SrcList containing the table to scan */ 460 Table *pTab, 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 */ 466){ 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); 474 475 assert( !pIdx || pIdx->pTable==pTab ); 476 477 if( nIncr<0 ){ 478 iFkIfZero = sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0); 479 } 480 481 /* Create an Expr object representing an SQL expression like: 482 ** 483 ** <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ... 484 ** 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. 488 */ 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 */ 495 496 pLeft = sqlite3Expr(db, TK_REGISTER, 0); 497 if( pLeft ){ 498 /* Set the collation sequence and affinity of the LHS of each TK_EQ 499 ** expression to the parent key column defaults. */ 500 if( pIdx ){ 501 Column *pCol; 502 iCol = pIdx->aiColumn[i]; 503 pCol = &pTab->aCol[iCol]; 504 if( pTab->iPKey==iCol ) iCol = -1; 505 pLeft->iTable = regData+iCol+1; 506 pLeft->affinity = pCol->affinity; 507 pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl); 508 }else{ 509 pLeft->iTable = regData; 510 pLeft->affinity = SQLITE_AFF_INTEGER; 511 } 512 } 513 iCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom; 514 assert( iCol>=0 ); 515 zCol = pFKey->pFrom->aCol[iCol].zName; 516 pRight = sqlite3Expr(db, TK_ID, zCol); 517 pEq = sqlite3PExpr(pParse, TK_EQ, pLeft, pRight, 0); 518 pWhere = sqlite3ExprAnd(db, pWhere, pEq); 519 } 520 521 /* If the child table is the same as the parent table, and this scan 522 ** is taking place as part of a DELETE operation (operation D.2), omit the 523 ** row being deleted from the scan by adding ($rowid != rowid) to the WHERE 524 ** clause, where $rowid is the rowid of the row being deleted. */ 525 if( pTab==pFKey->pFrom && nIncr>0 ){ 526 Expr *pEq; /* Expression (pLeft = pRight) */ 527 Expr *pLeft; /* Value from parent table row */ 528 Expr *pRight; /* Column ref to child table */ 529 pLeft = sqlite3Expr(db, TK_REGISTER, 0); 530 pRight = sqlite3Expr(db, TK_COLUMN, 0); 531 if( pLeft && pRight ){ 532 pLeft->iTable = regData; 533 pLeft->affinity = SQLITE_AFF_INTEGER; 534 pRight->iTable = pSrc->a[0].iCursor; 535 pRight->iColumn = -1; 536 } 537 pEq = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0); 538 pWhere = sqlite3ExprAnd(db, pWhere, pEq); 539 } 540 541 /* Resolve the references in the WHERE clause. */ 542 memset(&sNameContext, 0, sizeof(NameContext)); 543 sNameContext.pSrcList = pSrc; 544 sNameContext.pParse = pParse; 545 sqlite3ResolveExprNames(&sNameContext, pWhere); 546 547 /* Create VDBE to loop through the entries in pSrc that match the WHERE 548 ** clause. If the constraint is not deferred, throw an exception for 549 ** each row found. Otherwise, for deferred constraints, increment the 550 ** deferred constraint counter by nIncr for each row selected. */ 551 pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0); 552 if( nIncr>0 && pFKey->isDeferred==0 ){ 553 sqlite3ParseToplevel(pParse)->mayAbort = 1; 554 } 555 sqlite3VdbeAddOp2(v, OP_FkCounter, pFKey->isDeferred, nIncr); 556 if( pWInfo ){ 557 sqlite3WhereEnd(pWInfo); 558 } 559 560 /* Clean up the WHERE clause constructed above. */ 561 sqlite3ExprDelete(db, pWhere); 562 if( iFkIfZero ){ 563 sqlite3VdbeJumpHere(v, iFkIfZero); 564 } 565} 566 567/* 568** This function returns a pointer to the head of a linked list of FK 569** constraints for which table pTab is the parent table. For example, 570** given the following schema: 571** 572** CREATE TABLE t1(a PRIMARY KEY); 573** CREATE TABLE t2(b REFERENCES t1(a); 574** 575** Calling this function with table "t1" as an argument returns a pointer 576** to the FKey structure representing the foreign key constraint on table 577** "t2". Calling this function with "t2" as the argument would return a 578** NULL pointer (as there are no FK constraints for which t2 is the parent 579** table). 580*/ 581FKey *sqlite3FkReferences(Table *pTab){ 582 int nName = sqlite3Strlen30(pTab->zName); 583 return (FKey *)sqlite3HashFind(&pTab->pSchema->fkeyHash, pTab->zName, nName); 584} 585 586/* 587** The second argument is a Trigger structure allocated by the 588** fkActionTrigger() routine. This function deletes the Trigger structure 589** and all of its sub-components. 590** 591** The Trigger structure or any of its sub-components may be allocated from 592** the lookaside buffer belonging to database handle dbMem. 593*/ 594static void fkTriggerDelete(sqlite3 *dbMem, Trigger *p){ 595 if( p ){ 596 TriggerStep *pStep = p->step_list; 597 sqlite3ExprDelete(dbMem, pStep->pWhere); 598 sqlite3ExprListDelete(dbMem, pStep->pExprList); 599 sqlite3SelectDelete(dbMem, pStep->pSelect); 600 sqlite3ExprDelete(dbMem, p->pWhen); 601 sqlite3DbFree(dbMem, p); 602 } 603} 604 605/* 606** This function is called to generate code that runs when table pTab is 607** being dropped from the database. The SrcList passed as the second argument 608** to this function contains a single entry guaranteed to resolve to 609** table pTab. 610** 611** Normally, no code is required. However, if either 612** 613** (a) The table is the parent table of a FK constraint, or 614** (b) The table is the child table of a deferred FK constraint and it is 615** determined at runtime that there are outstanding deferred FK 616** constraint violations in the database, 617** 618** then the equivalent of "DELETE FROM <tbl>" is executed before dropping 619** the table from the database. Triggers are disabled while running this 620** DELETE, but foreign key actions are not. 621*/ 622void sqlite3FkDropTable(Parse *pParse, SrcList *pName, Table *pTab){ 623 sqlite3 *db = pParse->db; 624 if( (db->flags&SQLITE_ForeignKeys) && !IsVirtual(pTab) && !pTab->pSelect ){ 625 int iSkip = 0; 626 Vdbe *v = sqlite3GetVdbe(pParse); 627 628 assert( v ); /* VDBE has already been allocated */ 629 if( sqlite3FkReferences(pTab)==0 ){ 630 /* Search for a deferred foreign key constraint for which this table 631 ** is the child table. If one cannot be found, return without 632 ** generating any VDBE code. If one can be found, then jump over 633 ** the entire DELETE if there are no outstanding deferred constraints 634 ** when this statement is run. */ 635 FKey *p; 636 for(p=pTab->pFKey; p; p=p->pNextFrom){ 637 if( p->isDeferred ) break; 638 } 639 if( !p ) return; 640 iSkip = sqlite3VdbeMakeLabel(v); 641 sqlite3VdbeAddOp2(v, OP_FkIfZero, 1, iSkip); 642 } 643 644 pParse->disableTriggers = 1; 645 sqlite3DeleteFrom(pParse, sqlite3SrcListDup(db, pName, 0), 0); 646 pParse->disableTriggers = 0; 647 648 /* If the DELETE has generated immediate foreign key constraint 649 ** violations, halt the VDBE and return an error at this point, before 650 ** any modifications to the schema are made. This is because statement 651 ** transactions are not able to rollback schema changes. */ 652 sqlite3VdbeAddOp2(v, OP_FkIfZero, 0, sqlite3VdbeCurrentAddr(v)+2); 653 sqlite3HaltConstraint( 654 pParse, OE_Abort, "foreign key constraint failed", P4_STATIC 655 ); 656 657 if( iSkip ){ 658 sqlite3VdbeResolveLabel(v, iSkip); 659 } 660 } 661} 662 663/* 664** This function is called when inserting, deleting or updating a row of 665** table pTab to generate VDBE code to perform foreign key constraint 666** processing for the operation. 667** 668** For a DELETE operation, parameter regOld is passed the index of the 669** first register in an array of (pTab->nCol+1) registers containing the 670** rowid of the row being deleted, followed by each of the column values 671** of the row being deleted, from left to right. Parameter regNew is passed 672** zero in this case. 673** 674** For an INSERT operation, regOld is passed zero and regNew is passed the 675** first register of an array of (pTab->nCol+1) registers containing the new 676** row data. 677** 678** For an UPDATE operation, this function is called twice. Once before 679** the original record is deleted from the table using the calling convention 680** described for DELETE. Then again after the original record is deleted 681** but before the new record is inserted using the INSERT convention. 682*/ 683void sqlite3FkCheck( 684 Parse *pParse, /* Parse context */ 685 Table *pTab, /* Row is being deleted from this table */ 686 int regOld, /* Previous row data is stored here */ 687 int regNew /* New row data is stored here */ 688){ 689 sqlite3 *db = pParse->db; /* Database handle */ 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; 694 695 /* Exactly one of regOld and regNew should be non-zero. */ 696 assert( (regOld==0)!=(regNew==0) ); 697 698 /* If foreign-keys are disabled, this function is a no-op. */ 699 if( (db->flags&SQLITE_ForeignKeys)==0 ) return; 700 701 iDb = sqlite3SchemaToIndex(db, pTab->pSchema); 702 zDb = db->aDb[iDb].zName; 703 704 /* Loop through all the foreign key constraints for which pTab is the 705 ** child table (the table that the foreign key definition is part of). */ 706 for(pFKey=pTab->pFKey; pFKey; pFKey=pFKey->pNextFrom){ 707 Table *pTo; /* Parent table of foreign key pFKey */ 708 Index *pIdx = 0; /* Index on key columns in pTo */ 709 int *aiFree = 0; 710 int *aiCol; 711 int iCol; 712 int i; 713 int isIgnore = 0; 714 715 /* Find the parent table of this foreign key. Also find a unique index 716 ** on the parent key columns in the parent table. If either of these 717 ** schema items cannot be located, set an error in pParse and return 718 ** early. */ 719 if( pParse->disableTriggers ){ 720 pTo = sqlite3FindTable(db, pFKey->zTo, zDb); 721 }else{ 722 pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb); 723 } 724 if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){ 725 if( !isIgnoreErrors || db->mallocFailed ) return; 726 continue; 727 } 728 assert( pFKey->nCol==1 || (aiFree && pIdx) ); 729 730 if( aiFree ){ 731 aiCol = aiFree; 732 }else{ 733 iCol = pFKey->aCol[0].iFrom; 734 aiCol = &iCol; 735 } 736 for(i=0; i<pFKey->nCol; i++){ 737 if( aiCol[i]==pTab->iPKey ){ 738 aiCol[i] = -1; 739 } 740#ifndef SQLITE_OMIT_AUTHORIZATION 741 /* Request permission to read the parent key columns. If the 742 ** authorization callback returns SQLITE_IGNORE, behave as if any 743 ** values read from the parent table are NULL. */ 744 if( db->xAuth ){ 745 int rcauth; 746 char *zCol = pTo->aCol[pIdx ? pIdx->aiColumn[i] : pTo->iPKey].zName; 747 rcauth = sqlite3AuthReadCol(pParse, pTo->zName, zCol, iDb); 748 isIgnore = (rcauth==SQLITE_IGNORE); 749 } 750#endif 751 } 752 753 /* Take a shared-cache advisory read-lock on the parent table. Allocate 754 ** a cursor to use to search the unique index on the parent key columns 755 ** in the parent table. */ 756 sqlite3TableLock(pParse, iDb, pTo->tnum, 0, pTo->zName); 757 pParse->nTab++; 758 759 if( regOld!=0 ){ 760 /* A row is being removed from the child table. Search for the parent. 761 ** If the parent does not exist, removing the child row resolves an 762 ** outstanding foreign key constraint violation. */ 763 fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regOld, -1,isIgnore); 764 } 765 if( regNew!=0 ){ 766 /* A row is being added to the child table. If a parent row cannot 767 ** be found, adding the child row has violated the FK constraint. */ 768 fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1,isIgnore); 769 } 770 771 sqlite3DbFree(db, aiFree); 772 } 773 774 /* Loop through all the foreign key constraints that refer to this table */ 775 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ 776 Index *pIdx = 0; /* Foreign key index for pFKey */ 777 SrcList *pSrc; 778 int *aiCol = 0; 779 780 if( !pFKey->isDeferred && !pParse->pToplevel && !pParse->isMultiWrite ){ 781 assert( regOld==0 && regNew!=0 ); 782 /* Inserting a single row into a parent table cannot cause an immediate 783 ** foreign key violation. So do nothing in this case. */ 784 continue; 785 } 786 787 if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){ 788 if( !isIgnoreErrors || db->mallocFailed ) return; 789 continue; 790 } 791 assert( aiCol || pFKey->nCol==1 ); 792 793 /* Create a SrcList structure containing a single table (the table 794 ** the foreign key that refers to this table is attached to). This 795 ** is required for the sqlite3WhereXXX() interface. */ 796 pSrc = sqlite3SrcListAppend(db, 0, 0, 0); 797 if( pSrc ){ 798 struct SrcList_item *pItem = pSrc->a; 799 pItem->pTab = pFKey->pFrom; 800 pItem->zName = pFKey->pFrom->zName; 801 pItem->pTab->nRef++; 802 pItem->iCursor = pParse->nTab++; 803 804 if( regNew!=0 ){ 805 fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regNew, -1); 806 } 807 if( regOld!=0 ){ 808 /* If there is a RESTRICT action configured for the current operation 809 ** on the parent table of this FK, then throw an exception 810 ** immediately if the FK constraint is violated, even if this is a 811 ** deferred trigger. That's what RESTRICT means. To defer checking 812 ** the constraint, the FK should specify NO ACTION (represented 813 ** using OE_None). NO ACTION is the default. */ 814 fkScanChildren(pParse, pSrc, pTab, pIdx, pFKey, aiCol, regOld, 1); 815 } 816 pItem->zName = 0; 817 sqlite3SrcListDelete(db, pSrc); 818 } 819 sqlite3DbFree(db, aiCol); 820 } 821} 822 823#define COLUMN_MASK(x) (((x)>31) ? 0xffffffff : ((u32)1<<(x))) 824 825/* 826** This function is called before generating code to update or delete a 827** row contained in table pTab. 828*/ 829u32 sqlite3FkOldmask( 830 Parse *pParse, /* Parse context */ 831 Table *pTab /* Table being modified */ 832){ 833 u32 mask = 0; 834 if( pParse->db->flags&SQLITE_ForeignKeys ){ 835 FKey *p; 836 int i; 837 for(p=pTab->pFKey; p; p=p->pNextFrom){ 838 for(i=0; i<p->nCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom); 839 } 840 for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ 841 Index *pIdx = 0; 842 locateFkeyIndex(pParse, pTab, p, &pIdx, 0); 843 if( pIdx ){ 844 for(i=0; i<pIdx->nColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]); 845 } 846 } 847 } 848 return mask; 849} 850 851/* 852** This function is called before generating code to update or delete a 853** row contained in table pTab. If the operation is a DELETE, then 854** parameter aChange is passed a NULL value. For an UPDATE, aChange points 855** to an array of size N, where N is the number of columns in table pTab. 856** If the i'th column is not modified by the UPDATE, then the corresponding 857** entry in the aChange[] array is set to -1. If the column is modified, 858** the value is 0 or greater. Parameter chngRowid is set to true if the 859** UPDATE statement modifies the rowid fields of the table. 860** 861** If any foreign key processing will be required, this function returns 862** true. If there is no foreign key related processing, this function 863** returns false. 864*/ 865int sqlite3FkRequired( 866 Parse *pParse, /* Parse context */ 867 Table *pTab, /* Table being modified */ 868 int *aChange, /* Non-NULL for UPDATE operations */ 869 int chngRowid /* True for UPDATE that affects rowid */ 870){ 871 if( pParse->db->flags&SQLITE_ForeignKeys ){ 872 if( !aChange ){ 873 /* A DELETE operation. Foreign key processing is required if the 874 ** table in question is either the child or parent table for any 875 ** foreign key constraint. */ 876 return (sqlite3FkReferences(pTab) || pTab->pFKey); 877 }else{ 878 /* This is an UPDATE. Foreign key processing is only required if the 879 ** operation modifies one or more child or parent key columns. */ 880 int i; 881 FKey *p; 882 883 /* Check if any child key columns are being modified. */ 884 for(p=pTab->pFKey; p; p=p->pNextFrom){ 885 for(i=0; i<p->nCol; i++){ 886 int iChildKey = p->aCol[i].iFrom; 887 if( aChange[iChildKey]>=0 ) return 1; 888 if( iChildKey==pTab->iPKey && chngRowid ) return 1; 889 } 890 } 891 892 /* Check if any parent key columns are being modified. */ 893 for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ 894 for(i=0; i<p->nCol; i++){ 895 char *zKey = p->aCol[i].zCol; 896 int iKey; 897 for(iKey=0; iKey<pTab->nCol; iKey++){ 898 Column *pCol = &pTab->aCol[iKey]; 899 if( (zKey ? !sqlite3StrICmp(pCol->zName, zKey) : pCol->isPrimKey) ){ 900 if( aChange[iKey]>=0 ) return 1; 901 if( iKey==pTab->iPKey && chngRowid ) return 1; 902 } 903 } 904 } 905 } 906 } 907 } 908 return 0; 909} 910 911/* 912** This function is called when an UPDATE or DELETE operation is being 913** compiled on table pTab, which is the parent table of foreign-key pFKey. 914** If the current operation is an UPDATE, then the pChanges parameter is 915** passed a pointer to the list of columns being modified. If it is a 916** DELETE, pChanges is passed a NULL pointer. 917** 918** It returns a pointer to a Trigger structure containing a trigger 919** equivalent to the ON UPDATE or ON DELETE action specified by pFKey. 920** If the action is "NO ACTION" or "RESTRICT", then a NULL pointer is 921** returned (these actions require no special handling by the triggers 922** sub-system, code for them is created by fkScanChildren()). 923** 924** For example, if pFKey is the foreign key and pTab is table "p" in 925** the following schema: 926** 927** CREATE TABLE p(pk PRIMARY KEY); 928** CREATE TABLE c(ck REFERENCES p ON DELETE CASCADE); 929** 930** then the returned trigger structure is equivalent to: 931** 932** CREATE TRIGGER ... DELETE ON p BEGIN 933** DELETE FROM c WHERE ck = old.pk; 934** END; 935** 936** The returned pointer is cached as part of the foreign key object. It 937** is eventually freed along with the rest of the foreign key object by 938** sqlite3FkDelete(). 939*/ 940static Trigger *fkActionTrigger( 941 Parse *pParse, /* Parse context */ 942 Table *pTab, /* Table being updated or deleted from */ 943 FKey *pFKey, /* Foreign key to get action for */ 944 ExprList *pChanges /* Change-list for UPDATE, NULL for DELETE */ 945){ 946 sqlite3 *db = pParse->db; /* Database handle */ 947 int action; /* One of OE_None, OE_Cascade etc. */ 948 Trigger *pTrigger; /* Trigger definition to return */ 949 int iAction = (pChanges!=0); /* 1 for UPDATE, 0 for DELETE */ 950 951 action = pFKey->aAction[iAction]; 952 pTrigger = pFKey->apTrigger[iAction]; 953 954 if( action!=OE_None && !pTrigger ){ 955 u8 enableLookaside; /* Copy of db->lookaside.bEnabled */ 956 char const *zFrom; /* Name of child table */ 957 int nFrom; /* Length in bytes of zFrom */ 958 Index *pIdx = 0; /* Parent key index for this FK */ 959 int *aiCol = 0; /* child table cols -> parent key cols */ 960 TriggerStep *pStep = 0; /* First (only) step of trigger program */ 961 Expr *pWhere = 0; /* WHERE clause of trigger step */ 962 ExprList *pList = 0; /* Changes list if ON UPDATE CASCADE */ 963 Select *pSelect = 0; /* If RESTRICT, "SELECT RAISE(...)" */ 964 int i; /* Iterator variable */ 965 Expr *pWhen = 0; /* WHEN clause for the trigger */ 966 967 if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0; 968 assert( aiCol || pFKey->nCol==1 ); 969 970 for(i=0; i<pFKey->nCol; i++){ 971 Token tOld = { "old", 3 }; /* Literal "old" token */ 972 Token tNew = { "new", 3 }; /* Literal "new" token */ 973 Token tFromCol; /* Name of column in child table */ 974 Token tToCol; /* Name of column in parent table */ 975 int iFromCol; /* Idx of column in child table */ 976 Expr *pEq; /* tFromCol = OLD.tToCol */ 977 978 iFromCol = aiCol ? aiCol[i] : pFKey->aCol[0].iFrom; 979 assert( iFromCol>=0 ); 980 tToCol.z = pIdx ? pTab->aCol[pIdx->aiColumn[i]].zName : "oid"; 981 tFromCol.z = pFKey->pFrom->aCol[iFromCol].zName; 982 983 tToCol.n = sqlite3Strlen30(tToCol.z); 984 tFromCol.n = sqlite3Strlen30(tFromCol.z); 985 986 /* Create the expression "OLD.zToCol = zFromCol". It is important 987 ** that the "OLD.zToCol" term is on the LHS of the = operator, so 988 ** that the affinity and collation sequence associated with the 989 ** parent table are used for the comparison. */ 990 pEq = sqlite3PExpr(pParse, TK_EQ, 991 sqlite3PExpr(pParse, TK_DOT, 992 sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld), 993 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol) 994 , 0), 995 sqlite3PExpr(pParse, TK_ID, 0, 0, &tFromCol) 996 , 0); 997 pWhere = sqlite3ExprAnd(db, pWhere, pEq); 998 999 /* For ON UPDATE, construct the next term of the WHEN clause. 1000 ** The final WHEN clause will be like this: 1001 ** 1002 ** WHEN NOT(old.col1 IS new.col1 AND ... AND old.colN IS new.colN) 1003 */ 1004 if( pChanges ){ 1005 pEq = sqlite3PExpr(pParse, TK_IS, 1006 sqlite3PExpr(pParse, TK_DOT, 1007 sqlite3PExpr(pParse, TK_ID, 0, 0, &tOld), 1008 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol), 1009 0), 1010 sqlite3PExpr(pParse, TK_DOT, 1011 sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew), 1012 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol), 1013 0), 1014 0); 1015 pWhen = sqlite3ExprAnd(db, pWhen, pEq); 1016 } 1017 1018 if( action!=OE_Restrict && (action!=OE_Cascade || pChanges) ){ 1019 Expr *pNew; 1020 if( action==OE_Cascade ){ 1021 pNew = sqlite3PExpr(pParse, TK_DOT, 1022 sqlite3PExpr(pParse, TK_ID, 0, 0, &tNew), 1023 sqlite3PExpr(pParse, TK_ID, 0, 0, &tToCol) 1024 , 0); 1025 }else if( action==OE_SetDflt ){ 1026 Expr *pDflt = pFKey->pFrom->aCol[iFromCol].pDflt; 1027 if( pDflt ){ 1028 pNew = sqlite3ExprDup(db, pDflt, 0); 1029 }else{ 1030 pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0); 1031 } 1032 }else{ 1033 pNew = sqlite3PExpr(pParse, TK_NULL, 0, 0, 0); 1034 } 1035 pList = sqlite3ExprListAppend(pParse, pList, pNew); 1036 sqlite3ExprListSetName(pParse, pList, &tFromCol, 0); 1037 } 1038 } 1039 sqlite3DbFree(db, aiCol); 1040 1041 zFrom = pFKey->pFrom->zName; 1042 nFrom = sqlite3Strlen30(zFrom); 1043 1044 if( action==OE_Restrict ){ 1045 Token tFrom; 1046 Expr *pRaise; 1047 1048 tFrom.z = zFrom; 1049 tFrom.n = nFrom; 1050 pRaise = sqlite3Expr(db, TK_RAISE, "foreign key constraint failed"); 1051 if( pRaise ){ 1052 pRaise->affinity = OE_Abort; 1053 } 1054 pSelect = sqlite3SelectNew(pParse, 1055 sqlite3ExprListAppend(pParse, 0, pRaise), 1056 sqlite3SrcListAppend(db, 0, &tFrom, 0), 1057 pWhere, 1058 0, 0, 0, 0, 0, 0 1059 ); 1060 pWhere = 0; 1061 } 1062 1063 /* Disable lookaside memory allocation */ 1064 enableLookaside = db->lookaside.bEnabled; 1065 db->lookaside.bEnabled = 0; 1066 1067 pTrigger = (Trigger *)sqlite3DbMallocZero(db, 1068 sizeof(Trigger) + /* struct Trigger */ 1069 sizeof(TriggerStep) + /* Single step in trigger program */ 1070 nFrom + 1 /* Space for pStep->target.z */ 1071 ); 1072 if( pTrigger ){ 1073 pStep = pTrigger->step_list = (TriggerStep *)&pTrigger[1]; 1074 pStep->target.z = (char *)&pStep[1]; 1075 pStep->target.n = nFrom; 1076 memcpy((char *)pStep->target.z, zFrom, nFrom); 1077 1078 pStep->pWhere = sqlite3ExprDup(db, pWhere, EXPRDUP_REDUCE); 1079 pStep->pExprList = sqlite3ExprListDup(db, pList, EXPRDUP_REDUCE); 1080 pStep->pSelect = sqlite3SelectDup(db, pSelect, EXPRDUP_REDUCE); 1081 if( pWhen ){ 1082 pWhen = sqlite3PExpr(pParse, TK_NOT, pWhen, 0, 0); 1083 pTrigger->pWhen = sqlite3ExprDup(db, pWhen, EXPRDUP_REDUCE); 1084 } 1085 } 1086 1087 /* Re-enable the lookaside buffer, if it was disabled earlier. */ 1088 db->lookaside.bEnabled = enableLookaside; 1089 1090 sqlite3ExprDelete(db, pWhere); 1091 sqlite3ExprDelete(db, pWhen); 1092 sqlite3ExprListDelete(db, pList); 1093 sqlite3SelectDelete(db, pSelect); 1094 if( db->mallocFailed==1 ){ 1095 fkTriggerDelete(db, pTrigger); 1096 return 0; 1097 } 1098 1099 switch( action ){ 1100 case OE_Restrict: 1101 pStep->op = TK_SELECT; 1102 break; 1103 case OE_Cascade: 1104 if( !pChanges ){ 1105 pStep->op = TK_DELETE; 1106 break; 1107 } 1108 default: 1109 pStep->op = TK_UPDATE; 1110 } 1111 pStep->pTrig = pTrigger; 1112 pTrigger->pSchema = pTab->pSchema; 1113 pTrigger->pTabSchema = pTab->pSchema; 1114 pFKey->apTrigger[iAction] = pTrigger; 1115 pTrigger->op = (pChanges ? TK_UPDATE : TK_DELETE); 1116 } 1117 1118 return pTrigger; 1119} 1120 1121/* 1122** This function is called when deleting or updating a row to implement 1123** any required CASCADE, SET NULL or SET DEFAULT actions. 1124*/ 1125void sqlite3FkActions( 1126 Parse *pParse, /* Parse context */ 1127 Table *pTab, /* Table being updated or deleted from */ 1128 ExprList *pChanges, /* Change-list for UPDATE, NULL for DELETE */ 1129 int regOld /* Address of array containing old row */ 1130){ 1131 /* If foreign-key support is enabled, iterate through all FKs that 1132 ** refer to table pTab. If there is an action associated with the FK 1133 ** for this operation (either update or delete), invoke the associated 1134 ** trigger sub-program. */ 1135 if( pParse->db->flags&SQLITE_ForeignKeys ){ 1136 FKey *pFKey; /* Iterator variable */ 1137 for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ 1138 Trigger *pAction = fkActionTrigger(pParse, pTab, pFKey, pChanges); 1139 if( pAction ){ 1140 sqlite3CodeRowTriggerDirect(pParse, pAction, pTab, regOld, OE_Abort, 0); 1141 } 1142 } 1143 } 1144} 1145 1146#endif /* ifndef SQLITE_OMIT_TRIGGER */ 1147 1148/* 1149** Free all memory associated with foreign key definitions attached to 1150** table pTab. Remove the deleted foreign keys from the Schema.fkeyHash 1151** hash table. 1152*/ 1153void sqlite3FkDelete(sqlite3 *db, Table *pTab){ 1154 FKey *pFKey; /* Iterator variable */ 1155 FKey *pNext; /* Copy of pFKey->pNextFrom */ 1156 1157 assert( db==0 || sqlite3SchemaMutexHeld(db, 0, pTab->pSchema) ); 1158 for(pFKey=pTab->pFKey; pFKey; pFKey=pNext){ 1159 1160 /* Remove the FK from the fkeyHash hash table. */ 1161 if( !db || db->pnBytesFreed==0 ){ 1162 if( pFKey->pPrevTo ){ 1163 pFKey->pPrevTo->pNextTo = pFKey->pNextTo; 1164 }else{ 1165 void *p = (void *)pFKey->pNextTo; 1166 const char *z = (p ? pFKey->pNextTo->zTo : pFKey->zTo); 1167 sqlite3HashInsert(&pTab->pSchema->fkeyHash, z, sqlite3Strlen30(z), p); 1168 } 1169 if( pFKey->pNextTo ){ 1170 pFKey->pNextTo->pPrevTo = pFKey->pPrevTo; 1171 } 1172 } 1173 1174 /* EV: R-30323-21917 Each foreign key constraint in SQLite is 1175 ** classified as either immediate or deferred. 1176 */ 1177 assert( pFKey->isDeferred==0 || pFKey->isDeferred==1 ); 1178 1179 /* Delete any triggers created to implement actions for this FK. */ 1180#ifndef SQLITE_OMIT_TRIGGER 1181 fkTriggerDelete(db, pFKey->apTrigger[0]); 1182 fkTriggerDelete(db, pFKey->apTrigger[1]); 1183#endif 1184 1185 pNext = pFKey->pNextFrom; 1186 sqlite3DbFree(db, pFKey); 1187 } 1188} 1189#endif /* ifndef SQLITE_OMIT_FOREIGN_KEY */ 1190