Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Improved comments on foreign key logic. |
---|---|
Downloads: | Tarball | ZIP archive | SQL archive |
Timelines: | family | ancestors | descendants | both | omit-rowid |
Files: | files | file ages | folders |
SHA1: |
1315d9109c7105f4a62bb2d43ca6948d |
User & Date: | drh 2013-11-03 02:27:58 |
Context
2013-11-04
| ||
13:56 | Correctly handle self-referential foreign keys on WITHOUT ROWID tables. check-in: af128862 user: drh tags: omit-rowid | |
2013-11-03
| ||
02:27 | Improved comments on foreign key logic. check-in: 1315d910 user: drh tags: omit-rowid | |
2013-11-02
| ||
22:29 | Improved Synopsis on register comparison operators. Fix a bug on the constraint check generator. check-in: a7a18b65 user: drh tags: omit-rowid | |
Changes
Changes to src/build.c.
2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 |
} /* ** This routine is called to create a new foreign key on the table ** currently under construction. pFromCol determines which columns ** in the current table point to the foreign key. If pFromCol==0 then ** connect the key to the last column inserted. pTo is the name of ** the table referred to. pToCol is a list of tables in the other ** pTo table that the foreign key points to. flags contains all ** information about the conflict resolution algorithms specified ** in the ON DELETE, ON UPDATE and ON INSERT clauses. ** ** An FKey structure is created and added to the table currently ** under construction in the pParse->pNewTable field. ** ** The foreign key is set for IMMEDIATE processing. A subsequent call |
| | |
2462 2463 2464 2465 2466 2467 2468 2469 2470 2471 2472 2473 2474 2475 2476 2477 |
} /* ** This routine is called to create a new foreign key on the table ** currently under construction. pFromCol determines which columns ** in the current table point to the foreign key. If pFromCol==0 then ** connect the key to the last column inserted. pTo is the name of ** the table referred to (a.k.a the "parent" table). pToCol is a list ** of tables in the parent pTo table. flags contains all ** information about the conflict resolution algorithms specified ** in the ON DELETE, ON UPDATE and ON INSERT clauses. ** ** An FKey structure is created and added to the table currently ** under construction in the pParse->pNewTable field. ** ** The foreign key is set for IMMEDIATE processing. A subsequent call |
Changes to src/fkey.c.
471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 ... 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 ... 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 ... 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 ... 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 |
** INSERT deferred Decrement the "deferred constraint counter". ** ** These operations are identified in the comment at the top of this file ** (fkey.c) as "I.2" and "D.2". */ static void fkScanChildren( Parse *pParse, /* Parse context */ SrcList *pSrc, /* SrcList containing the table to scan */ Table *pTab, Index *pIdx, /* Foreign key index */ FKey *pFKey, /* Foreign key relationship */ int *aiCol, /* Map from pIdx cols to child table cols */ int regData, /* Referenced table data starts here */ int nIncr /* Amount to increment deferred counter by */ ){ sqlite3 *db = pParse->db; /* Database handle */ int i; /* Iterator variable */ Expr *pWhere = 0; /* WHERE clause to scan with */ NameContext sNameContext; /* Context used to resolve WHERE clause */ WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */ int iFkIfZero = 0; /* Address of OP_FkIfZero */ Vdbe *v = sqlite3GetVdbe(pParse); assert( !pIdx || pIdx->pTable==pTab ); if( nIncr<0 ){ iFkIfZero = sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0); } /* Create an Expr object representing an SQL expression like: ** ................................................................................ } /* If the child table is the same as the parent table, and this scan ** is taking place as part of a DELETE operation (operation D.2), omit the ** row being deleted from the scan by adding ($rowid != rowid) to the WHERE ** clause, where $rowid is the rowid of the row being deleted. */ if( pTab==pFKey->pFrom && nIncr>0 && HasRowid(pTab) /*FIXME*/ ){ Expr *pEq; /* Expression (pLeft = pRight) */ Expr *pLeft; /* Value from parent table row */ Expr *pRight; /* Column ref to child table */ pLeft = sqlite3Expr(db, TK_REGISTER, 0); pRight = sqlite3Expr(db, TK_COLUMN, 0); if( pLeft && pRight ){ pLeft->iTable = regData; pLeft->affinity = SQLITE_AFF_INTEGER; pRight->iTable = pSrc->a[0].iCursor; pRight->iColumn = -1; } pEq = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0); pWhere = sqlite3ExprAnd(db, pWhere, pEq); } /* Resolve the references in the WHERE clause. */ memset(&sNameContext, 0, sizeof(NameContext)); sNameContext.pSrcList = pSrc; sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); ................................................................................ sqlite3ExprDelete(db, pWhere); if( iFkIfZero ){ sqlite3VdbeJumpHere(v, iFkIfZero); } } /* ** This function returns a pointer to the head of a linked list of FK ** constraints for which table pTab is the parent table. For example, ** given the following schema: ** ** CREATE TABLE t1(a PRIMARY KEY); ** CREATE TABLE t2(b REFERENCES t1(a); ** ** Calling this function with table "t1" as an argument returns a pointer ** to the FKey structure representing the foreign key constraint on table ................................................................................ ** be found, adding the child row has violated the FK constraint. */ fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1,isIgnore); } sqlite3DbFree(db, aiFree); } /* Loop through all the foreign key constraints that refer to this table */ for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ Index *pIdx = 0; /* Foreign key index for pFKey */ SrcList *pSrc; int *aiCol = 0; if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){ continue; ................................................................................ if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){ if( !isIgnoreErrors || db->mallocFailed ) return; continue; } assert( aiCol || pFKey->nCol==1 ); /* Create a SrcList structure containing a single table (the table ** the foreign key that refers to this table is attached to). This ** is required for the sqlite3WhereXXX() interface. */ pSrc = sqlite3SrcListAppend(db, 0, 0, 0); if( pSrc ){ struct SrcList_item *pItem = pSrc->a; pItem->pTab = pFKey->pFrom; pItem->zName = pFKey->pFrom->zName; pItem->pTab->nRef++; pItem->iCursor = pParse->nTab++; |
| | | | | | > > | | | | | | > | | < |
471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 ... 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 ... 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 ... 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 ... 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 |
** INSERT deferred Decrement the "deferred constraint counter". ** ** These operations are identified in the comment at the top of this file ** (fkey.c) as "I.2" and "D.2". */ static void fkScanChildren( Parse *pParse, /* Parse context */ SrcList *pSrc, /* The child table to be scanned */ Table *pTab, /* The parent table */ Index *pIdx, /* Index on parent covering the foreign key */ FKey *pFKey, /* The foreign key linking pSrc to pTab */ int *aiCol, /* Map from pIdx cols to child table cols */ int regData, /* Parent row data starts here */ int nIncr /* Amount to increment deferred counter by */ ){ sqlite3 *db = pParse->db; /* Database handle */ int i; /* Iterator variable */ Expr *pWhere = 0; /* WHERE clause to scan with */ NameContext sNameContext; /* Context used to resolve WHERE clause */ WhereInfo *pWInfo; /* Context used by sqlite3WhereXXX() */ int iFkIfZero = 0; /* Address of OP_FkIfZero */ Vdbe *v = sqlite3GetVdbe(pParse); assert( pIdx==0 || pIdx->pTable==pTab ); assert( pIdx==0 || pIdx->nKeyCol==pFKey->nCol ); assert( pIdx!=0 || pFKey->nCol==1 ); if( nIncr<0 ){ iFkIfZero = sqlite3VdbeAddOp2(v, OP_FkIfZero, pFKey->isDeferred, 0); } /* Create an Expr object representing an SQL expression like: ** ................................................................................ } /* If the child table is the same as the parent table, and this scan ** is taking place as part of a DELETE operation (operation D.2), omit the ** row being deleted from the scan by adding ($rowid != rowid) to the WHERE ** clause, where $rowid is the rowid of the row being deleted. */ if( pTab==pFKey->pFrom && nIncr>0 && HasRowid(pTab) /*FIXME*/ ){ Expr *pNe; /* Expression (pLeft != pRight) */ Expr *pLeft; /* Value from parent table row */ Expr *pRight; /* Column ref to child table */ pLeft = sqlite3Expr(db, TK_REGISTER, 0); pRight = sqlite3Expr(db, TK_COLUMN, 0); if( pLeft && pRight ){ pLeft->iTable = regData; pLeft->affinity = SQLITE_AFF_INTEGER; pRight->iTable = pSrc->a[0].iCursor; pRight->iColumn = -1; } pNe = sqlite3PExpr(pParse, TK_NE, pLeft, pRight, 0); pWhere = sqlite3ExprAnd(db, pWhere, pNe); } /* Resolve the references in the WHERE clause. */ memset(&sNameContext, 0, sizeof(NameContext)); sNameContext.pSrcList = pSrc; sNameContext.pParse = pParse; sqlite3ResolveExprNames(&sNameContext, pWhere); ................................................................................ sqlite3ExprDelete(db, pWhere); if( iFkIfZero ){ sqlite3VdbeJumpHere(v, iFkIfZero); } } /* ** This function returns a linked list of FKey objects (connected by ** FKey.pNextTo) holding all children of table pTab. For example, ** given the following schema: ** ** CREATE TABLE t1(a PRIMARY KEY); ** CREATE TABLE t2(b REFERENCES t1(a); ** ** Calling this function with table "t1" as an argument returns a pointer ** to the FKey structure representing the foreign key constraint on table ................................................................................ ** be found, adding the child row has violated the FK constraint. */ fkLookupParent(pParse, iDb, pTo, pIdx, pFKey, aiCol, regNew, +1,isIgnore); } sqlite3DbFree(db, aiFree); } /* Loop through all the foreign key constraints that refer to this table. ** (the "child" constraints) */ for(pFKey = sqlite3FkReferences(pTab); pFKey; pFKey=pFKey->pNextTo){ Index *pIdx = 0; /* Foreign key index for pFKey */ SrcList *pSrc; int *aiCol = 0; if( aChange && fkParentIsModified(pTab, pFKey, aChange, bChngRowid)==0 ){ continue; ................................................................................ if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){ if( !isIgnoreErrors || db->mallocFailed ) return; continue; } assert( aiCol || pFKey->nCol==1 ); /* Create a SrcList structure containing the child table. We need the ** child table as a SrcList for sqlite3WhereBegin() */ pSrc = sqlite3SrcListAppend(db, 0, 0, 0); if( pSrc ){ struct SrcList_item *pItem = pSrc->a; pItem->pTab = pFKey->pFrom; pItem->zName = pFKey->pFrom->zName; pItem->pTab->nRef++; pItem->iCursor = pParse->nTab++; |
Changes to src/sqliteInt.h.
1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 |
** ** CREATE TABLE ex1( ** a INTEGER PRIMARY KEY, ** b INTEGER CONSTRAINT fk1 REFERENCES ex2(x) ** ); ** ** For foreign key "fk1", the from-table is "ex1" and the to-table is "ex2". ** ** Each REFERENCES clause generates an instance of the following structure ** which is attached to the from-table. The to-table need not exist when ** the from-table is created. The existence of the to-table is not checked. */ struct FKey { Table *pFrom; /* Table containing the REFERENCES clause (aka: Child) */ FKey *pNextFrom; /* Next foreign key in pFrom */ char *zTo; /* Name of table that the key points to (aka: Parent) */ FKey *pNextTo; /* Next foreign key on table named zTo */ FKey *pPrevTo; /* Previous foreign key on table named zTo */ int nCol; /* Number of columns in this key */ /* EV: R-30323-21917 */ u8 isDeferred; /* True if constraint checking is deferred till COMMIT */ u8 aAction[2]; /* ON DELETE and ON UPDATE actions, respectively */ Trigger *apTrigger[2]; /* Triggers for aAction[] actions */ struct sColMap { /* Mapping of columns in pFrom to columns in zTo */ int iFrom; /* Index of column in pFrom */ char *zCol; /* Name of column in zTo. If 0 use PRIMARY KEY */ } aCol[1]; /* One entry for each of nCol column s */ }; /* ** SQLite supports many different ways to resolve a constraint ** error. ROLLBACK processing means that a constraint violation ** causes the operation in process to fail and for the current transaction ** to be rolled back. ABORT processing means the operation in process |
> > > > > > > > > | | | | | | | | | | |
1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 |
** ** CREATE TABLE ex1( ** a INTEGER PRIMARY KEY, ** b INTEGER CONSTRAINT fk1 REFERENCES ex2(x) ** ); ** ** For foreign key "fk1", the from-table is "ex1" and the to-table is "ex2". ** Equivalent names: ** ** from-table == child-table ** to-table == parent-table ** ** Each REFERENCES clause generates an instance of the following structure ** which is attached to the from-table. The to-table need not exist when ** the from-table is created. The existence of the to-table is not checked. ** ** The list of all parents for child Table X is held at X.pFKey. ** ** A list of all children for a table named Z (which might not even exist) ** is held in Schema.fkeyHash with a hash key of Z. */ struct FKey { Table *pFrom; /* Table containing the REFERENCES clause (aka: Child) */ FKey *pNextFrom; /* Next FKey with the same in pFrom. Next parent of pFrom */ char *zTo; /* Name of table that the key points to (aka: Parent) */ FKey *pNextTo; /* Next with the same zTo. Next child of zTo. */ FKey *pPrevTo; /* Previous with the same zTo */ int nCol; /* Number of columns in this key */ /* EV: R-30323-21917 */ u8 isDeferred; /* True if constraint checking is deferred till COMMIT */ u8 aAction[2]; /* ON DELETE and ON UPDATE actions, respectively */ Trigger *apTrigger[2];/* Triggers for aAction[] actions */ struct sColMap { /* Mapping of columns in pFrom to columns in zTo */ int iFrom; /* Index of column in pFrom */ char *zCol; /* Name of column in zTo. If NULL use PRIMARY KEY */ } aCol[1]; /* One entry for each of nCol columns */ }; /* ** SQLite supports many different ways to resolve a constraint ** error. ROLLBACK processing means that a constraint violation ** causes the operation in process to fail and for the current transaction ** to be rolled back. ABORT processing means the operation in process |