Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix comments in fkey2.c to reflect the immediate-constraint-counter approach. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
9fd54b0aa73ed74c65f7db53cb666752 |
User & Date: | dan 2009-09-25 12:00:02.000 |
Context
2009-09-25
| ||
17:03 | Avoid checking if an insert or delete has "fixed" an outstanding FK constraint violation if the constraint counter indicates that the database contains no such violations. (check-in: 519144ac43 user: dan tags: trunk) | |
12:00 | Fix comments in fkey2.c to reflect the immediate-constraint-counter approach. (check-in: 9fd54b0aa7 user: dan tags: trunk) | |
11:26 | Prevent ALTER TABLE from being used to add a column with a REFERENCES clause and a non-NULL default value while foreign key support is enabled. (check-in: 353b1b1825 user: dan tags: trunk) | |
Changes
Changes to src/fkey.c.
︙ | ︙ | |||
78 79 80 81 82 83 84 85 86 87 88 89 90 91 | ** This simplifies the implementation a bit. ** ** For the purposes of immediate FK constraints, the OR REPLACE conflict ** resolution is considered to delete rows before the new row is inserted. ** If a delete caused by OR REPLACE violates an FK constraint, an exception ** is thrown, even if the FK constraint would be satisfied after the new ** row is inserted. ** ** TODO: How should dropping a table be handled? How should renaming a ** table be handled? ** ** ** Query API Notes ** --------------- | > > > > > > > > > > | 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 | ** This simplifies the implementation a bit. ** ** For the purposes of immediate FK constraints, the OR REPLACE conflict ** resolution is considered to delete rows before the new row is inserted. ** If a delete caused by OR REPLACE violates an FK constraint, an exception ** is thrown, even if the FK constraint would be satisfied after the new ** row is inserted. ** ** Immediate constraints are usually handled similarly. The only difference ** is that the counter used is stored as part of each individual statement ** object (struct Vdbe). If, after the statement has run, its immediate ** constraint counter is greater than zero, it returns SQLITE_CONSTRAINT ** and the statement transaction is rolled back. An exception is an INSERT ** statement that inserts a single row only (no triggers). In this case, ** instead of using a counter, an exception is thrown immediately if the ** INSERT violates a foreign key constraint. This is necessary as such ** an INSERT does not open a statement transaction. ** ** TODO: How should dropping a table be handled? How should renaming a ** table be handled? ** ** ** Query API Notes ** --------------- |
︙ | ︙ | |||
255 256 257 258 259 260 261 | } *ppIdx = pIdx; return 0; } /* | | < | | | > > < | | | 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 | } *ppIdx = pIdx; return 0; } /* ** This function is called when a row is inserted into or deleted from the ** child table of foreign key constraint pFKey. If an SQL UPDATE is executed ** on the child table of pFKey, this function is invoked twice for each row ** affected - once to "delete" the old row, and then again to "insert" the ** new row. ** ** Each time it is called, this function generates VDBE code to locate the ** row in the parent table that corresponds to the row being inserted into ** or deleted from the child table. If the parent row can be found, no ** special action is taken. Otherwise, if the parent row can *not* be ** found in the parent table: ** ** Operation | FK type | Action taken ** -------------------------------------------------------------------------- ** INSERT immediate Increment the "immediate constraint counter". ** ** DELETE immediate Decrement the "immediate constraint counter". ** ** INSERT deferred Increment the "deferred constraint counter". ** ** DELETE deferred Decrement the "deferred constraint counter". ** ** These operations are identified in the comment at the top of this file ** (fkey.c) as "I.1" and "D.1". */ static void fkLookupParent( Parse *pParse, /* Parse context */ int iDb, /* Index of database housing pTab */ Table *pTab, /* Parent table of FK pFKey */ Index *pIdx, /* Unique index on parent key columns in pTab */ FKey *pFKey, /* Foreign key constraint */ |
︙ | ︙ | |||
374 375 376 377 378 379 380 | ** ** The code generated by this function scans through the rows in the child ** table that correspond to the parent table row being deleted or inserted. ** For each child row found, one of the following actions is taken: ** ** Operation | FK type | Action taken ** -------------------------------------------------------------------------- | > > | > > < | | > > > > > > > > > > | 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 | ** ** The code generated by this function scans through the rows in the child ** table that correspond to the parent table row being deleted or inserted. ** For each child row found, one of the following actions is taken: ** ** Operation | FK type | Action taken ** -------------------------------------------------------------------------- ** DELETE immediate Increment the "immediate constraint counter". ** Or, if the ON (UPDATE|DELETE) action is RESTRICT, ** throw a "foreign key constraint failed" exception. ** ** INSERT immediate Decrement the "immediate constraint counter". ** ** DELETE deferred Increment the "deferred constraint counter". ** Or, if the ON (UPDATE|DELETE) action is RESTRICT, ** throw a "foreign key constraint failed" exception. ** ** 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 */ 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() */ /* Create an Expr object representing an SQL expression like: ** ** <parent-key1> = <child-key1> AND <parent-key2> = <child-key2> ... ** ** The collation sequence used for the comparison should be that of ** the parent key columns. The affinity of the parent key column should ** be applied to each child key value before the comparison takes place. */ for(i=0; i<pFKey->nCol; i++){ Expr *pLeft; /* Value from parent table row */ Expr *pRight; /* Column ref to child table */ Expr *pEq; /* Expression (pLeft = pRight) */ int iCol; /* Index of column in child table */ const char *zCol; /* Name of column in child table */ pLeft = sqlite3Expr(db, TK_REGISTER, 0); if( pLeft ){ /* Set the collation sequence and affinity of the LHS of each TK_EQ ** expression to the parent key column defaults. */ if( pIdx ){ int iCol = pIdx->aiColumn[i]; Column *pCol = &pIdx->pTable->aCol[iCol]; pLeft->iTable = regData+iCol+1; pLeft->affinity = pCol->affinity; pLeft->pColl = sqlite3LocateCollSeq(pParse, pCol->zColl); }else{ |
︙ | ︙ | |||
441 442 443 444 445 446 447 | /* Create VDBE to loop through the entries in pSrc that match the WHERE ** clause. If the constraint is not deferred, throw an exception for ** each row found. Otherwise, for deferred constraints, increment the ** deferred constraint counter by nIncr for each row selected. */ pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0); if( nIncr==0 ){ | | > | 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 | /* Create VDBE to loop through the entries in pSrc that match the WHERE ** clause. If the constraint is not deferred, throw an exception for ** each row found. Otherwise, for deferred constraints, increment the ** deferred constraint counter by nIncr for each row selected. */ pWInfo = sqlite3WhereBegin(pParse, pSrc, pWhere, 0, 0); if( nIncr==0 ){ /* Special case: A RESTRICT Action. Throw an error immediately if one ** of these is encountered. */ sqlite3HaltConstraint( pParse, OE_Abort, "foreign key constraint failed", P4_STATIC ); }else{ if( nIncr>0 && pFKey->isDeferred==0 ){ sqlite3ParseToplevel(pParse)->mayAbort = 1; } |
︙ | ︙ |
Changes to test/fkey2.test.
︙ | ︙ | |||
184 185 186 187 188 189 190 | } {35.0 text 35 integer} do_test fkey2-1.5.2 { catchsql { DELETE FROM i } } {1 {foreign key constraint failed}} # Use a collation sequence on the parent key. drop_all_tables | | > > > > > > > > > > > > > > > > > > > > | 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 | } {35.0 text 35 integer} do_test fkey2-1.5.2 { catchsql { DELETE FROM i } } {1 {foreign key constraint failed}} # Use a collation sequence on the parent key. drop_all_tables do_test fkey2-1.6.1 { execsql { CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY); CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i)); INSERT INTO i VALUES('SQLite'); INSERT INTO j VALUES('sqlite'); } catchsql { DELETE FROM i } } {1 {foreign key constraint failed}} # Use the parent key collation even if it is default and the child key # has an explicit value. drop_all_tables do_test fkey2-1.6.2 { execsql { CREATE TABLE i(i TEXT PRIMARY KEY); -- Colseq is "BINARY" CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i)); INSERT INTO i VALUES('SQLite'); } catchsql { INSERT INTO j VALUES('sqlite') } } {1 {foreign key constraint failed}} do_test fkey2-1.6.3 { execsql { INSERT INTO i VALUES('sqlite'); INSERT INTO j VALUES('sqlite'); DELETE FROM i WHERE i = 'SQLite'; } catchsql { DELETE FROM i WHERE i = 'sqlite' } } {1 {foreign key constraint failed}} #------------------------------------------------------------------------- # This section (test cases fkey2-2.*) contains tests to check that the # deferred foreign key constraint logic works. # proc fkey2-2-test {tn nocommit sql {res {}}} { if {$res eq "FKV"} { |
︙ | ︙ |