SQLite
Check-in [8c13a7fd73]
Not logged in
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: 208b259ad7 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: 8c13a7fd73 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: 27001356ed user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/pragma.c.

1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
....
1624
1625
1626
1627
1628
1629
1630

1631
1632
1633
1634
1635
1636
1637
1638

1639
1640

1641
1642
1643
1644
1645
1646
1647
1648
1649
....
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662

1663
1664
1665
1666

1667
1668
1669
1670
1671
1672
1673
      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{
................................................................................
            sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
          }
        }else{
          k = 0;
          break;
        }
      }

      if( pFK ) break;
      if( pParse->nTab<i ) pParse->nTab = 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 && iKey<pTab->nCol );
          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,
................................................................................
          }
          sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow);
          sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk);
          sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
        }else{
          for(j=0; j<pFK->nCol; 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);







|
|







 







>




|
<


>
|
|
>

|







 







|


>
|
|
|
|
>







1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
....
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636

1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
....
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
      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 = sqlite3FindTable(db, pFK->zTo, zDb);
        if( pParent==0 ) continue;
        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{
................................................................................
            sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
          }
        }else{
          k = 0;
          break;
        }
      }
      assert( pParse->nErr>0 || pFK==0 );
      if( pFK ) break;
      if( pParse->nTab<i ) pParse->nTab = i;
      addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
      for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
        pParent = sqlite3FindTable(db, pFK->zTo, zDb);

        pIdx = 0;
        aiCols = 0;
        if( pParent ){
          x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
          assert( x==0 );
        }
        addrOk = sqlite3VdbeMakeLabel(v);
        if( pParent && pIdx==0 ){
          int iKey = pFK->aCol[0].iFrom;
          assert( iKey>=0 && iKey<pTab->nCol );
          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,
................................................................................
          }
          sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow);
          sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk);
          sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
        }else{
          for(j=0; j<pFK->nCol; j++){
            sqlite3ExprCodeGetColumnOfTable(v, pTab, 0,
                            aiCols ? aiCols[j] : pFK->aCol[j].iFrom, regRow+j);
            sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
          }
          if( pParent ){
            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);

Changes to test/fkey5.test.

13
14
15
16
17
18
19

20
21
22
23
24
25
26
...
320
321
322
323
324
325
326
327


328































329
# This file tests the PRAGMA foreign_key_check command.
#
# EVIDENCE-OF: R-05426-18119 PRAGMA foreign_key_check; PRAGMA
# foreign_key_check(table-name);

set testdir [file dirname $argv0]
source $testdir/tester.tcl


ifcapable {!foreignkey} {
  finish_test
  return
}

do_test fkey5-1.1 {
................................................................................
  db eval {
    DELETE FROM c22;
    INSERT INTO c22 VALUES('abc  ','ALPHA');
    PRAGMA foreign_key_check(c22);
  }
} {}




































finish_test







>







 








>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
...
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
# This file tests the PRAGMA foreign_key_check command.
#
# EVIDENCE-OF: R-05426-18119 PRAGMA foreign_key_check; PRAGMA
# foreign_key_check(table-name);

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix fkey5

ifcapable {!foreignkey} {
  finish_test
  return
}

do_test fkey5-1.1 {
................................................................................
  db eval {
    DELETE FROM c22;
    INSERT INTO c22 VALUES('abc  ','ALPHA');
    PRAGMA foreign_key_check(c22);
  }
} {}


#-------------------------------------------------------------------------
# Tests 9.* verify that missing parent tables are handled correctly.
#
do_execsql_test 9.1.1 {
  CREATE TABLE k1(x REFERENCES s1);
  PRAGMA foreign_key_check(k1);
} {}
do_execsql_test 9.1.2 {
  INSERT INTO k1 VALUES(NULL);
  PRAGMA foreign_key_check(k1);
} {}
do_execsql_test 9.1.3 {
  INSERT INTO k1 VALUES(1);
  PRAGMA foreign_key_check(k1);
} {k1 2 s1 0}

do_execsql_test 9.2.1 {
  CREATE TABLE k2(x, y, FOREIGN KEY(x, y) REFERENCES s1(a, b));
  PRAGMA foreign_key_check(k2);
} {}
do_execsql_test 9.2 {
  INSERT INTO k2 VALUES(NULL, 'five');
  PRAGMA foreign_key_check(k2);
} {}
do_execsql_test 9.3 {
  INSERT INTO k2 VALUES('one', NULL);
  PRAGMA foreign_key_check(k2);
} {}
do_execsql_test 9.4 {
  INSERT INTO k2 VALUES('six', 'seven');
  PRAGMA foreign_key_check(k2);
} {k2 3 s1 0}


finish_test