Index: src/fkey.c ================================================================== --- src/fkey.c +++ src/fkey.c @@ -140,11 +140,11 @@ /* ** A foreign key constraint requires that the key columns in the parent ** table are collectively subject to a UNIQUE or PRIMARY KEY constraint. ** Given that pParent is the parent table for foreign key constraint pFKey, -** search the schema a unique index on the parent key columns. +** search the schema for a unique index on the parent key columns. ** ** If successful, zero is returned. If the parent key is an INTEGER PRIMARY ** KEY column, then output variable *ppIdx is set to NULL. Otherwise, *ppIdx ** is set to point to the unique index. ** @@ -176,11 +176,11 @@ ** ** then non-zero is returned, and a "foreign key mismatch" error loaded ** into pParse. If an OOM error occurs, non-zero is returned and the ** pParse->db->mallocFailed flag is set. */ -static int locateFkeyIndex( +int sqlite3FkLocateIndex( Parse *pParse, /* Parse context to store any error in */ Table *pParent, /* Parent table of FK constraint pFKey */ FKey *pFKey, /* Foreign key to find index for */ Index **ppIdx, /* OUT: Unique index on parent table */ int **paiCol /* OUT: Map of index columns in pFKey */ @@ -273,11 +273,13 @@ } } if( !pIdx ){ if( !pParse->disableTriggers ){ - sqlite3ErrorMsg(pParse, "foreign key mismatch"); + sqlite3ErrorMsg(pParse, + "foreign key mismatch - \"%w\" referencing \"%w\"", + pFKey->pFrom->zName, pFKey->zTo); } sqlite3DbFree(pParse->db, aiCol); return 1; } @@ -734,11 +736,11 @@ if( pParse->disableTriggers ){ pTo = sqlite3FindTable(db, pFKey->zTo, zDb); }else{ pTo = sqlite3LocateTable(pParse, 0, pFKey->zTo, zDb); } - if( !pTo || locateFkeyIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){ + if( !pTo || sqlite3FkLocateIndex(pParse, pTo, pFKey, &pIdx, &aiFree) ){ assert( isIgnoreErrors==0 || (regOld!=0 && regNew==0) ); if( !isIgnoreErrors || db->mallocFailed ) return; if( pTo==0 ){ /* If isIgnoreErrors is true, then a table is being dropped. In this ** case SQLite runs a "DELETE FROM xxx" on the table being dropped @@ -814,11 +816,11 @@ /* Inserting a single row into a parent table cannot cause an immediate ** foreign key violation. So do nothing in this case. */ continue; } - if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){ + if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ){ if( !isIgnoreErrors || db->mallocFailed ) return; continue; } assert( aiCol || pFKey->nCol==1 ); @@ -869,11 +871,11 @@ for(p=pTab->pFKey; p; p=p->pNextFrom){ for(i=0; inCol; i++) mask |= COLUMN_MASK(p->aCol[i].iFrom); } for(p=sqlite3FkReferences(pTab); p; p=p->pNextTo){ Index *pIdx = 0; - locateFkeyIndex(pParse, pTab, p, &pIdx, 0); + sqlite3FkLocateIndex(pParse, pTab, p, &pIdx, 0); if( pIdx ){ for(i=0; inColumn; i++) mask |= COLUMN_MASK(pIdx->aiColumn[i]); } } } @@ -995,11 +997,11 @@ ExprList *pList = 0; /* Changes list if ON UPDATE CASCADE */ Select *pSelect = 0; /* If RESTRICT, "SELECT RAISE(...)" */ int i; /* Iterator variable */ Expr *pWhen = 0; /* WHEN clause for the trigger */ - if( locateFkeyIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0; + if( sqlite3FkLocateIndex(pParse, pTab, pFKey, &pIdx, &aiCol) ) return 0; assert( aiCol || pFKey->nCol==1 ); for(i=0; inCol; i++){ Token tOld = { "old", 3 }; /* Literal "old" token */ Token tNew = { "new", 3 }; /* Literal "new" token */ Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -1109,10 +1109,124 @@ ++i; pFK = pFK->pNextFrom; } } } + }else +#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */ + +#ifndef SQLITE_OMIT_FOREIGN_KEY + if( sqlite3StrICmp(zLeft, "foreign_key_check")==0 ){ + FKey *pFK; /* A foreign key constraint */ + Table *pTab; /* Child table contain "REFERENCES" keyword */ + Table *pParent; /* Parent table that child points to */ + Index *pIdx; /* Index in the parent table */ + int i; /* Loop counter: Foreign key number for pTab */ + int j; /* Loop counter: Field of the foreign key */ + HashElem *k; /* Loop counter: Next table in schema */ + int x; /* result variable */ + int regResult; /* 3 registers to hold a result row */ + int regKey; /* Register to hold key for checking the FK */ + int regRow; /* Registers to hold a row from pTab */ + int addrTop; /* Top of a loop checking foreign keys */ + int addrOk; /* Jump here if the key is OK */ + int *aiCols; /* child to parent column mapping */ + + if( sqlite3ReadSchema(pParse) ) goto pragma_out; + regResult = pParse->nMem+1; + pParse->nMem += 4; + regKey = ++pParse->nMem; + regRow = ++pParse->nMem; + v = sqlite3GetVdbe(pParse); + sqlite3VdbeSetNumCols(v, 4); + sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "table", SQLITE_STATIC); + sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "rowid", SQLITE_STATIC); + sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "parent", SQLITE_STATIC); + sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "fkid", SQLITE_STATIC); + sqlite3CodeVerifySchema(pParse, iDb); + k = sqliteHashFirst(&db->aDb[iDb].pSchema->tblHash); + while( k ){ + if( zRight ){ + pTab = sqlite3LocateTable(pParse, 0, zRight, zDb); + k = 0; + }else{ + pTab = (Table*)sqliteHashData(k); + k = sqliteHashNext(k); + } + if( pTab==0 || pTab->pFKey==0 ) continue; + sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName); + if( pTab->nCol+regRow>pParse->nMem ) pParse->nMem = pTab->nCol + regRow; + sqlite3OpenTable(pParse, 0, iDb, pTab, OP_OpenRead); + sqlite3VdbeAddOp4(v, OP_String8, 0, regResult, 0, pTab->zName, + P4_TRANSIENT); + for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){ + pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb); + if( pParent==0 ) break; + pIdx = 0; + sqlite3TableLock(pParse, iDb, pParent->tnum, 0, pParent->zName); + x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0); + if( x==0 ){ + if( pIdx==0 ){ + sqlite3OpenTable(pParse, i, iDb, pParent, OP_OpenRead); + }else{ + KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx); + sqlite3VdbeAddOp3(v, OP_OpenRead, i, pIdx->tnum, iDb); + sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF); + } + }else{ + k = 0; + break; + } + } + if( pFK ) break; + if( pParse->nTabnTab = i; + addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0); + for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){ + pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb); + assert( pParent!=0 ); + pIdx = 0; + aiCols = 0; + x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols); + assert( x==0 ); + addrOk = sqlite3VdbeMakeLabel(v); + if( pIdx==0 ){ + int iKey = pFK->aCol[0].iFrom; + assert( iKey>=0 && iKeynCol ); + if( iKey!=pTab->iPKey ){ + sqlite3VdbeAddOp3(v, OP_Column, 0, iKey, regRow); + sqlite3ColumnDefault(v, pTab, iKey, regRow); + sqlite3VdbeAddOp2(v, OP_IsNull, regRow, addrOk); + sqlite3VdbeAddOp2(v, OP_MustBeInt, regRow, + sqlite3VdbeCurrentAddr(v)+3); + }else{ + sqlite3VdbeAddOp2(v, OP_Rowid, 0, regRow); + } + sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow); + sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk); + sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2); + }else{ + for(j=0; jnCol; j++){ + sqlite3ExprCodeGetColumnOfTable(v, pTab, 0, + aiCols ? aiCols[j] : pFK->aCol[0].iFrom, regRow+j); + sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk); + } + sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey); + sqlite3VdbeChangeP4(v, -1, + sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT); + sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0); + } + sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1); + sqlite3VdbeAddOp4(v, OP_String8, 0, regResult+2, 0, + pFK->zTo, P4_TRANSIENT); + sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+3); + sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 4); + sqlite3VdbeResolveLabel(v, addrOk); + sqlite3DbFree(db, aiCols); + } + sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop+1); + sqlite3VdbeJumpHere(v, addrTop); + } }else #endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */ #ifndef NDEBUG if( sqlite3StrICmp(zLeft, "parser_trace")==0 ){ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -3207,12 +3207,14 @@ #define sqlite3FkOldmask(a,b) 0 #define sqlite3FkRequired(a,b,c,d) 0 #endif #ifndef SQLITE_OMIT_FOREIGN_KEY void sqlite3FkDelete(sqlite3 *, Table*); + int sqlite3FkLocateIndex(Parse*,Table*,FKey*,Index**,int**); #else #define sqlite3FkDelete(a,b) + #define sqlite3FkLocateIndex(a,b,c,d,e) #endif /* ** Available fault injectors. Should be numbered beginning with 0. Index: test/e_fkey.test ================================================================== --- test/e_fkey.test +++ test/e_fkey.test @@ -625,11 +625,12 @@ proc test_efkey_57 {tn isError sql} { catchsql { DROP TABLE t1 } execsql $sql do_test e_fkey-18.$tn { catchsql { INSERT INTO t2 VALUES(NULL) } - } [lindex {{0 {}} {1 {foreign key mismatch}}} $isError] + } [lindex {{0 {}} {/1 {foreign key mismatch - ".*" referencing ".*"}/}} \ + $isError] } test_efkey_57 2 0 { CREATE TABLE t1(x PRIMARY KEY) } test_efkey_57 3 0 { CREATE TABLE t1(x UNIQUE) } test_efkey_57 4 0 { CREATE TABLE t1(x); CREATE UNIQUE INDEX t1i ON t1(x) } test_efkey_57 5 1 { @@ -696,20 +697,20 @@ INSERT INTO child3 VALUES(3, 4); } } {} do_test e_fkey-19.2 { catchsql { INSERT INTO child4 VALUES('xxx', 5) } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "child4" referencing "parent"}} do_test e_fkey-19.3 { catchsql { INSERT INTO child5 VALUES('xxx', 6) } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "child5" referencing "parent"}} do_test e_fkey-19.4 { catchsql { INSERT INTO child6 VALUES(2, 3) } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "child6" referencing "parent"}} do_test e_fkey-19.5 { catchsql { INSERT INTO child7 VALUES(3) } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "child7" referencing "parent"}} #------------------------------------------------------------------------- # Test errors in the database schema that are detected while preparing # DML statements. The error text for these messages always matches # either "foreign key mismatch" or "no such table*" (using [string match]). @@ -763,16 +764,16 @@ } } {} foreach {tn tbl ptbl err} { 2 c1 {} "no such table: main.nosuchtable" - 3 c2 p2 "foreign key mismatch" - 4 c3 p3 "foreign key mismatch" - 5 c4 p4 "foreign key mismatch" - 6 c5 p5 "foreign key mismatch" - 7 c6 p6 "foreign key mismatch" - 8 c7 p7 "foreign key mismatch" + 3 c2 p2 "foreign key mismatch - \"c2\" referencing \"p2\"" + 4 c3 p3 "foreign key mismatch - \"c3\" referencing \"p3\"" + 5 c4 p4 "foreign key mismatch - \"c4\" referencing \"p4\"" + 6 c5 p5 "foreign key mismatch - \"c5\" referencing \"p5\"" + 7 c6 p6 "foreign key mismatch - \"c6\" referencing \"p6\"" + 8 c7 p7 "foreign key mismatch - \"c7\" referencing \"p7\"" } { do_test e_fkey-20.$tn.1 { catchsql "INSERT INTO $tbl VALUES('a', 'b')" } [list 1 $err] do_test e_fkey-20.$tn.2 { @@ -818,26 +819,26 @@ INSERT INTO child8 VALUES('I', 'II'); } } {} do_test e_fkey-21.3 { catchsql { INSERT INTO child9 VALUES('I') } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "child9" referencing "parent2"}} do_test e_fkey-21.4 { catchsql { INSERT INTO child9 VALUES('II') } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "child9" referencing "parent2"}} do_test e_fkey-21.5 { catchsql { INSERT INTO child9 VALUES(NULL) } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "child9" referencing "parent2"}} do_test e_fkey-21.6 { catchsql { INSERT INTO child10 VALUES('I', 'II', 'III') } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "child10" referencing "parent2"}} do_test e_fkey-21.7 { catchsql { INSERT INTO child10 VALUES(1, 2, 3) } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "child10" referencing "parent2"}} do_test e_fkey-21.8 { catchsql { INSERT INTO child10 VALUES(NULL, NULL, NULL) } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "child10" referencing "parent2"}} #------------------------------------------------------------------------- # Test errors that are reported when creating the child table. # Specifically: # @@ -1149,19 +1150,19 @@ execsql { CREATE TABLE p(x PRIMARY KEY); CREATE TABLE c(a, b, FOREIGN KEY(a,b) REFERENCES p); } catchsql {DELETE FROM p} -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "c" referencing "p"}} do_test e_fkey-28.9 { drop_all_tables execsql { CREATE TABLE p(x, y, PRIMARY KEY(x,y)); CREATE TABLE c(a REFERENCES p); } catchsql {DELETE FROM p} -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "c" referencing "p"}} #------------------------------------------------------------------------- # EVIDENCE-OF: R-24676-09859 # @@ -2727,15 +2728,15 @@ } } {{} 2} do_test e_fkey-60.4 { execsql { CREATE TABLE nosuchtable(x PRIMARY KEY) } catchsql { DELETE FROM p } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "c2" referencing "p"}} do_test e_fkey-60.5 { execsql { DROP TABLE c1 } catchsql { DELETE FROM p } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "c2" referencing "p"}} do_test e_fkey-60.6 { execsql { DROP TABLE c2 } execsql { DELETE FROM p } } {} Index: test/fkey2.test ================================================================== --- test/fkey2.test +++ test/fkey2.test @@ -137,26 +137,39 @@ 4.15 "UPDATE t7 SET b = 5" {1 {foreign key constraint failed}} 4.16 "UPDATE t7 SET rowid = 5" {1 {foreign key constraint failed}} 4.17 "UPDATE t7 SET a = 10" {0 {}} 5.1 "INSERT INTO t9 VALUES(1, 3)" {1 {no such table: main.nosuchtable}} - 5.2 "INSERT INTO t10 VALUES(1, 3)" {1 {foreign key mismatch}} + 5.2 "INSERT INTO t10 VALUES(1, 3)" + {1 {foreign key mismatch - "t10" referencing "t9"}} } do_test fkey2-1.1.0 { execsql [string map {/D/ {}} $FkeySimpleSchema] } {} foreach {tn zSql res} $FkeySimpleTests { - do_test fkey2-1.1.$tn { catchsql $zSql } $res + do_test fkey2-1.1.$tn.1 { catchsql $zSql } $res + do_test fkey2-1.1.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {} + do_test fkey2-1.1.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {} + do_test fkey2-1.1.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {} + do_test fkey2-1.1.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {} + do_test fkey2-1.1.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {} + do_test fkey2-1.1.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {} } drop_all_tables do_test fkey2-1.2.0 { execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema] } {} foreach {tn zSql res} $FkeySimpleTests { do_test fkey2-1.2.$tn { catchsql $zSql } $res + do_test fkey2-1.2.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {} + do_test fkey2-1.2.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {} + do_test fkey2-1.2.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {} + do_test fkey2-1.2.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {} + do_test fkey2-1.2.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {} + do_test fkey2-1.2.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {} } drop_all_tables do_test fkey2-1.3.0 { execsql [string map {/D/ {}} $FkeySimpleSchema] @@ -163,10 +176,16 @@ execsql { PRAGMA count_changes = 1 } } {} foreach {tn zSql res} $FkeySimpleTests { if {$res == "0 {}"} { set res {0 1} } do_test fkey2-1.3.$tn { catchsql $zSql } $res + do_test fkey2-1.3.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {} + do_test fkey2-1.3.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {} + do_test fkey2-1.3.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {} + do_test fkey2-1.3.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {} + do_test fkey2-1.3.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {} + do_test fkey2-1.3.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {} } execsql { PRAGMA count_changes = 0 } drop_all_tables do_test fkey2-1.4.0 { @@ -679,11 +698,11 @@ }] { drop_all_tables do_test fkey2-10.1.[incr tn] { execsql $zSql catchsql { INSERT INTO c DEFAULT VALUES } - } {1 {foreign key mismatch}} + } {/1 {foreign key mismatch - "c" referencing "."}/} } # "rowid" cannot be used as part of a child or parent key definition # unless it happens to be the name of an explicitly declared column. # @@ -707,11 +726,11 @@ CREATE TABLE t1(a, b); CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid)); INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1); INSERT INTO t2 VALUES(1, 1); } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "t2" referencing "t1"}} do_test fkey2-10.2.2 { drop_all_tables catchsql { CREATE TABLE t1(rowid PRIMARY KEY, b); CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid)); @@ -1221,11 +1240,11 @@ execsql { CREATE TABLE pp(x, y, PRIMARY KEY(x, y)); CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z)); } catchsql { INSERT INTO cc VALUES(1, 2) } -} {1 {foreign key mismatch}} +} {1 {foreign key mismatch - "cc" referencing "pp"}} do_test fkey-2.14.3.9 { execsql { DROP TABLE cc } } {} do_test fkey-2.14.3.10 { execsql { ADDED test/fkey5.test Index: test/fkey5.test ================================================================== --- /dev/null +++ test/fkey5.test @@ -0,0 +1,310 @@ +# 2012 December 17 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#*********************************************************************** +# This file implements regression tests for SQLite library. +# +# This file tests the PRAGMA foreign_key_check command. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +ifcapable {!foreignkey} { + finish_test + return +} + +do_test fkey5-1.1 { + db eval { + CREATE TABLE p1(a INTEGER PRIMARY KEY); INSERT INTO p1 VALUES(88),(89); + CREATE TABLE p2(a INT PRIMARY KEY); INSERT INTO p2 VALUES(77),(78); + CREATE TABLE p3(a TEXT PRIMARY KEY); + INSERT INTO p3 VALUES(66),(67),('alpha'),('BRAVO'); + CREATE TABLE p4(a TEXT PRIMARY KEY COLLATE nocase); + INSERT INTO p4 VALUES('alpha'),('BRAVO'),('55'),('Delta'),('ECHO'); + CREATE TABLE p5(a INTEGER PRIMARY KEY, b, c, UNIQUE(b,c)); + INSERT INTO p5 VALUES(1,'Alpha','abc'),(2,'beta','def'); + CREATE TABLE p6(a INTEGER PRIMARY KEY, b TEXT COLLATE nocase, + c TEXT COLLATE rtrim, UNIQUE(b,c)); + INSERT INTO p6 VALUES(1,'Alpha','abc '),(2,'bETA','def '); + + CREATE TABLE c1(x INTEGER PRIMARY KEY references p1); + CREATE TABLE c2(x INTEGER PRIMARY KEY references p2); + CREATE TABLE c3(x INTEGER PRIMARY KEY references p3); + CREATE TABLE c4(x INTEGER PRIMARY KEY references p4); + CREATE TABLE c5(x INT references p1); + CREATE TABLE c6(x INT references p2); + CREATE TABLE c7(x INT references p3); + CREATE TABLE c8(x INT references p4); + CREATE TABLE c9(x TEXT UNIQUE references p1); + CREATE TABLE c10(x TEXT UNIQUE references p2); + CREATE TABLE c11(x TEXT UNIQUE references p3); + CREATE TABLE c12(x TEXT UNIQUE references p4); + CREATE TABLE c13(x TEXT COLLATE nocase references p3); + CREATE TABLE c14(x TEXT COLLATE nocase references p4); + CREATE TABLE c15(x, y, FOREIGN KEY(x,y) REFERENCES p5(b,c)); + CREATE TABLE c16(x, y, FOREIGN KEY(x,y) REFERENCES p5(c,b)); + CREATE TABLE c17(x, y, FOREIGN KEY(x,y) REFERENCES p6(b,c)); + CREATE TABLE c18(x, y, FOREIGN KEY(x,y) REFERENCES p6(c,b)); + CREATE TABLE c19(x TEXT COLLATE nocase, y TEXT COLLATE rtrim, + FOREIGN KEY(x,y) REFERENCES p5(b,c)); + CREATE TABLE c20(x TEXT COLLATE nocase, y TEXT COLLATE rtrim, + FOREIGN KEY(x,y) REFERENCES p5(c,b)); + CREATE TABLE c21(x TEXT COLLATE nocase, y TEXT COLLATE rtrim, + FOREIGN KEY(x,y) REFERENCES p6(b,c)); + CREATE TABLE c22(x TEXT COLLATE nocase, y TEXT COLLATE rtrim, + FOREIGN KEY(x,y) REFERENCES p6(c,b)); + + PRAGMA foreign_key_check; + } +} {} +do_test fkey5-1.2 { + db eval { + INSERT INTO c1 VALUES(90),(87),(88); + PRAGMA foreign_key_check; + } +} {c1 87 p1 0 c1 90 p1 0} +do_test fkey5-1.3 { + db eval { + PRAGMA foreign_key_check(c1); + } +} {c1 87 p1 0 c1 90 p1 0} +do_test fkey5-1.4 { + db eval { + PRAGMA foreign_key_check(c2); + } +} {} + +do_test fkey5-2.0 { + db eval { + INSERT INTO c5 SELECT x FROM c1; + DELETE FROM c1; + PRAGMA foreign_key_check; + } +} {c5 1 p1 0 c5 3 p1 0} +do_test fkey5-2.1 { + db eval { + PRAGMA foreign_key_check(c5); + } +} {c5 1 p1 0 c5 3 p1 0} +do_test fkey5-2.2 { + db eval { + PRAGMA foreign_key_check(c1); + } +} {} + +do_test fkey5-3.0 { + db eval { + INSERT INTO c9 SELECT x FROM c5; + DELETE FROM c5; + PRAGMA foreign_key_check; + } +} {c9 1 p1 0 c9 3 p1 0} +do_test fkey5-3.1 { + db eval { + PRAGMA foreign_key_check(c9); + } +} {c9 1 p1 0 c9 3 p1 0} +do_test fkey5-3.2 { + db eval { + PRAGMA foreign_key_check(c5); + } +} {} + +do_test fkey5-4.0 { + db eval { + DELETE FROM c9; + INSERT INTO c2 VALUES(79),(77),(76); + PRAGMA foreign_key_check; + } +} {c2 76 p2 0 c2 79 p2 0} +do_test fkey5-4.1 { + db eval { + PRAGMA foreign_key_check(c2); + } +} {c2 76 p2 0 c2 79 p2 0} +do_test fkey5-4.2 { + db eval { + INSERT INTO c6 SELECT x FROM c2; + DELETE FROM c2; + PRAGMA foreign_key_check; + } +} {c6 1 p2 0 c6 3 p2 0} +do_test fkey5-4.3 { + db eval { + PRAGMA foreign_key_check(c6); + } +} {c6 1 p2 0 c6 3 p2 0} +do_test fkey5-4.4 { + db eval { + INSERT INTO c10 SELECT x FROM c6; + DELETE FROM c6; + PRAGMA foreign_key_check; + } +} {c10 1 p2 0 c10 3 p2 0} +do_test fkey5-4.5 { + db eval { + PRAGMA foreign_key_check(c10); + } +} {c10 1 p2 0 c10 3 p2 0} + +do_test fkey5-5.0 { + db eval { + DELETE FROM c10; + INSERT INTO c3 VALUES(68),(67),(65); + PRAGMA foreign_key_check; + } +} {c3 65 p3 0 c3 68 p3 0} +do_test fkey5-5.1 { + db eval { + PRAGMA foreign_key_check(c3); + } +} {c3 65 p3 0 c3 68 p3 0} +do_test fkey5-5.2 { + db eval { + INSERT INTO c7 SELECT x FROM c3; + INSERT INTO c7 VALUES('Alpha'),('alpha'),('foxtrot'); + DELETE FROM c3; + PRAGMA foreign_key_check; + } +} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0} +do_test fkey5-5.3 { + db eval { + PRAGMA foreign_key_check(c7); + } +} {c7 1 p3 0 c7 3 p3 0 c7 4 p3 0 c7 6 p3 0} +do_test fkey5-5.4 { + db eval { + INSERT INTO c11 SELECT x FROM c7; + DELETE FROM c7; + PRAGMA foreign_key_check; + } +} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0} +do_test fkey5-5.5 { + db eval { + PRAGMA foreign_key_check(c11); + } +} {c11 1 p3 0 c11 3 p3 0 c11 4 p3 0 c11 6 p3 0} + +do_test fkey5-6.0 { + db eval { + DELETE FROM c11; + INSERT INTO c4 VALUES(54),(55),(56); + PRAGMA foreign_key_check; + } +} {c4 54 p4 0 c4 56 p4 0} +do_test fkey5-6.1 { + db eval { + PRAGMA foreign_key_check(c4); + } +} {c4 54 p4 0 c4 56 p4 0} +do_test fkey5-6.2 { + db eval { + INSERT INTO c8 SELECT x FROM c4; + INSERT INTO c8 VALUES('Alpha'),('ALPHA'),('foxtrot'); + DELETE FROM c4; + PRAGMA foreign_key_check; + } +} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0} +do_test fkey5-6.3 { + db eval { + PRAGMA foreign_key_check(c8); + } +} {c8 1 p4 0 c8 3 p4 0 c8 6 p4 0} +do_test fkey5-6.4 { + db eval { + INSERT INTO c12 SELECT x FROM c8; + DELETE FROM c8; + PRAGMA foreign_key_check; + } +} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0} +do_test fkey5-6.5 { + db eval { + PRAGMA foreign_key_check(c12); + } +} {c12 1 p4 0 c12 3 p4 0 c12 6 p4 0} + +do_test fkey5-7.1 { + db eval { + INSERT OR IGNORE INTO c13 SELECT * FROM c12; + INSERT OR IGNORE INTO C14 SELECT * FROM c12; + DELETE FROM c12; + PRAGMA foreign_key_check; + } +} {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0 c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0} +do_test fkey5-7.2 { + db eval { + PRAGMA foreign_key_check(c14); + } +} {c14 1 p4 0 c14 3 p4 0 c14 6 p4 0} +do_test fkey5-7.3 { + db eval { + PRAGMA foreign_key_check(c13); + } +} {c13 1 p3 0 c13 2 p3 0 c13 3 p3 0 c13 4 p3 0 c13 5 p3 0 c13 6 p3 0} + +do_test fkey5-8.0 { + db eval { + DELETE FROM c13; + DELETE FROM c14; + INSERT INTO c19 VALUES('alpha','abc'); + PRAGMA foreign_key_check(c19); + } +} {c19 1 p5 0} +do_test fkey5-8.1 { + db eval { + DELETE FROM c19; + INSERT INTO c19 VALUES('Alpha','abc'); + PRAGMA foreign_key_check(c19); + } +} {} +do_test fkey5-8.2 { + db eval { + INSERT INTO c20 VALUES('Alpha','abc'); + PRAGMA foreign_key_check(c20); + } +} {c20 1 p5 0} +do_test fkey5-8.3 { + db eval { + DELETE FROM c20; + INSERT INTO c20 VALUES('abc','Alpha'); + PRAGMA foreign_key_check(c20); + } +} {} +do_test fkey5-8.4 { + db eval { + INSERT INTO c21 VALUES('alpha','abc '); + PRAGMA foreign_key_check(c21); + } +} {} +do_test fkey5-8.5 { + db eval { + DELETE FROM c21; + INSERT INTO c19 VALUES('Alpha','abc'); + PRAGMA foreign_key_check(c21); + } +} {} +do_test fkey5-8.6 { + db eval { + INSERT INTO c22 VALUES('Alpha','abc'); + PRAGMA foreign_key_check(c22); + } +} {c22 1 p6 0} +do_test fkey5-8.7 { + db eval { + DELETE FROM c22; + INSERT INTO c22 VALUES('abc ','ALPHA'); + PRAGMA foreign_key_check(c22); + } +} {} + + + +finish_test Index: test/fkey_malloc.test ================================================================== --- test/fkey_malloc.test +++ test/fkey_malloc.test @@ -27,10 +27,11 @@ } -sqlbody { INSERT INTO t1 VALUES('aaa', 1); INSERT INTO t2 VALUES('aaa'); UPDATE t1 SET a = 'bbb'; DELETE FROM t1; + PRAGMA foreign_key_check; } do_malloc_test fkey_malloc-2 -sqlprep { PRAGMA foreign_keys = 1; CREATE TABLE t1(a, b, UNIQUE(a, b)); @@ -126,7 +127,5 @@ DROP TABLE y; DROP TABLE x; } finish_test - -