/ Check-in [8c13a7fd]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:In "PRAGMA foreign_key_check", treat missing parent tables as empty (instead of as errors).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fkc-missing-parent-tables
Files: files | file ages | folders
SHA1: 8c13a7fd738e5441af370537649b0bfa97679cda
User & Date: dan 2013-10-12 19:06:48
Context
2013-10-14
14:30
Update the foreign_key_check pragma so that when a parent table is undefined it is treated as an empty table. check-in: 208b259a user: drh tags: trunk
2013-10-12
19:06
In "PRAGMA foreign_key_check", treat missing parent tables as empty (instead of as errors). Closed-Leaf check-in: 8c13a7fd user: dan tags: fkc-missing-parent-tables
15:12
Fix handling of "DROP TABLE" commands when "PRAGMA defer_foreign_keys=1" is set. check-in: 27001356 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/pragma.c.

  1606   1606         if( pTab==0 || pTab->pFKey==0 ) continue;
  1607   1607         sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
  1608   1608         if( pTab->nCol+regRow>pParse->nMem ) pParse->nMem = pTab->nCol + regRow;
  1609   1609         sqlite3OpenTable(pParse, 0, iDb, pTab, OP_OpenRead);
  1610   1610         sqlite3VdbeAddOp4(v, OP_String8, 0, regResult, 0, pTab->zName,
  1611   1611                           P4_TRANSIENT);
  1612   1612         for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
  1613         -        pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
  1614         -        if( pParent==0 ) break;
         1613  +        pParent = sqlite3FindTable(db, pFK->zTo, zDb);
         1614  +        if( pParent==0 ) continue;
  1615   1615           pIdx = 0;
  1616   1616           sqlite3TableLock(pParse, iDb, pParent->tnum, 0, pParent->zName);
  1617   1617           x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
  1618   1618           if( x==0 ){
  1619   1619             if( pIdx==0 ){
  1620   1620               sqlite3OpenTable(pParse, i, iDb, pParent, OP_OpenRead);
  1621   1621             }else{
................................................................................
  1624   1624               sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
  1625   1625             }
  1626   1626           }else{
  1627   1627             k = 0;
  1628   1628             break;
  1629   1629           }
  1630   1630         }
         1631  +      assert( pParse->nErr>0 || pFK==0 );
  1631   1632         if( pFK ) break;
  1632   1633         if( pParse->nTab<i ) pParse->nTab = i;
  1633   1634         addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
  1634   1635         for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
  1635         -        pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
  1636         -        assert( pParent!=0 );
         1636  +        pParent = sqlite3FindTable(db, pFK->zTo, zDb);
  1637   1637           pIdx = 0;
  1638   1638           aiCols = 0;
  1639         -        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
  1640         -        assert( x==0 );
         1639  +        if( pParent ){
         1640  +          x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
         1641  +          assert( x==0 );
         1642  +        }
  1641   1643           addrOk = sqlite3VdbeMakeLabel(v);
  1642         -        if( pIdx==0 ){
         1644  +        if( pParent && pIdx==0 ){
  1643   1645             int iKey = pFK->aCol[0].iFrom;
  1644   1646             assert( iKey>=0 && iKey<pTab->nCol );
  1645   1647             if( iKey!=pTab->iPKey ){
  1646   1648               sqlite3VdbeAddOp3(v, OP_Column, 0, iKey, regRow);
  1647   1649               sqlite3ColumnDefault(v, pTab, iKey, regRow);
  1648   1650               sqlite3VdbeAddOp2(v, OP_IsNull, regRow, addrOk);
  1649   1651               sqlite3VdbeAddOp2(v, OP_MustBeInt, regRow,
................................................................................
  1653   1655             }
  1654   1656             sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow);
  1655   1657             sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk);
  1656   1658             sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
  1657   1659           }else{
  1658   1660             for(j=0; j<pFK->nCol; j++){
  1659   1661               sqlite3ExprCodeGetColumnOfTable(v, pTab, 0,
  1660         -                            aiCols ? aiCols[j] : pFK->aCol[0].iFrom, regRow+j);
         1662  +                            aiCols ? aiCols[j] : pFK->aCol[j].iFrom, regRow+j);
  1661   1663               sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
  1662   1664             }
  1663         -          sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
  1664         -          sqlite3VdbeChangeP4(v, -1,
  1665         -                   sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
  1666         -          sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
         1665  +          if( pParent ){
         1666  +            sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
         1667  +            sqlite3VdbeChangeP4(v, -1,
         1668  +                     sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
         1669  +            sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
         1670  +          }
  1667   1671           }
  1668   1672           sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1);
  1669   1673           sqlite3VdbeAddOp4(v, OP_String8, 0, regResult+2, 0, 
  1670   1674                             pFK->zTo, P4_TRANSIENT);
  1671   1675           sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+3);
  1672   1676           sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 4);
  1673   1677           sqlite3VdbeResolveLabel(v, addrOk);

Changes to test/fkey5.test.

    13     13   # This file tests the PRAGMA foreign_key_check command.
    14     14   #
    15     15   # EVIDENCE-OF: R-05426-18119 PRAGMA foreign_key_check; PRAGMA
    16     16   # foreign_key_check(table-name);
    17     17   
    18     18   set testdir [file dirname $argv0]
    19     19   source $testdir/tester.tcl
           20  +set testprefix fkey5
    20     21   
    21     22   ifcapable {!foreignkey} {
    22     23     finish_test
    23     24     return
    24     25   }
    25     26   
    26     27   do_test fkey5-1.1 {
................................................................................
   320    321     db eval {
   321    322       DELETE FROM c22;
   322    323       INSERT INTO c22 VALUES('abc  ','ALPHA');
   323    324       PRAGMA foreign_key_check(c22);
   324    325     }
   325    326   } {}
   326    327   
          328  +
          329  +#-------------------------------------------------------------------------
          330  +# Tests 9.* verify that missing parent tables are handled correctly.
          331  +#
          332  +do_execsql_test 9.1.1 {
          333  +  CREATE TABLE k1(x REFERENCES s1);
          334  +  PRAGMA foreign_key_check(k1);
          335  +} {}
          336  +do_execsql_test 9.1.2 {
          337  +  INSERT INTO k1 VALUES(NULL);
          338  +  PRAGMA foreign_key_check(k1);
          339  +} {}
          340  +do_execsql_test 9.1.3 {
          341  +  INSERT INTO k1 VALUES(1);
          342  +  PRAGMA foreign_key_check(k1);
          343  +} {k1 2 s1 0}
          344  +
          345  +do_execsql_test 9.2.1 {
          346  +  CREATE TABLE k2(x, y, FOREIGN KEY(x, y) REFERENCES s1(a, b));
          347  +  PRAGMA foreign_key_check(k2);
          348  +} {}
          349  +do_execsql_test 9.2 {
          350  +  INSERT INTO k2 VALUES(NULL, 'five');
          351  +  PRAGMA foreign_key_check(k2);
          352  +} {}
          353  +do_execsql_test 9.3 {
          354  +  INSERT INTO k2 VALUES('one', NULL);
          355  +  PRAGMA foreign_key_check(k2);
          356  +} {}
          357  +do_execsql_test 9.4 {
          358  +  INSERT INTO k2 VALUES('six', 'seven');
          359  +  PRAGMA foreign_key_check(k2);
          360  +} {k2 3 s1 0}
   327    361   
   328    362   
   329    363   finish_test