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