Index: src/fkey.c ================================================================== --- src/fkey.c +++ src/fkey.c @@ -80,10 +80,20 @@ ** 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? ** ** @@ -257,14 +267,13 @@ *ppIdx = pIdx; return 0; } /* -** This function is called when a row is inserted into the child table of -** foreign key constraint pFKey and, if pFKey is deferred, when a row is -** deleted from the child table of pFKey. If an SQL UPDATE is executed on -** the child table of pFKey, this function is invoked twice for each row +** 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 @@ -272,19 +281,20 @@ ** special action is taken. Otherwise, if the parent row can *not* be ** found in the parent table: ** ** Operation | FK type | Action taken ** -------------------------------------------------------------------------- -** INSERT immediate Throw a "foreign key constraint failed" exception. +** 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". ** -** This function is never called for a delete on the child table of an -** immediate foreign key constraint. These operations are identified in -** the comment at the top of this file (fkey.c) as "I.1" and "D.1". +** 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 */ @@ -376,21 +386,24 @@ ** 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 Throw a "foreign key constraint failed" exception. +** 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". ** -** This function is never called for an INSERT operation on the parent table -** of an immediate foreign key constraint. These operations are identified in -** the comment at the top of this file (fkey.c) as "I.2" and "D.2". +** 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 */ @@ -403,19 +416,29 @@ 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: + ** + ** = AND = ... + ** + ** 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; inCol; 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; @@ -443,11 +466,12 @@ ** 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 ){ - /* A RESTRICT Action. */ + /* 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 ){ Index: test/fkey2.test ================================================================== --- test/fkey2.test +++ test/fkey2.test @@ -186,19 +186,39 @@ catchsql { DELETE FROM i } } {1 {foreign key constraint failed}} # Use a collation sequence on the parent key. drop_all_tables -do_test fkey2-1.5.1 { +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. #