/ Check-in [57d47423]
Login

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

Overview
Comment:"PRAGMA foreign_key_check" with no argument checks the foreign keys on all tables.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | foreign-key-check
Files: files | file ages | folders
SHA1: 57d47423a65d183cb1efcb95b28cb143e788b953
User & Date: drh 2012-12-17 18:43:02
Context
2012-12-17
20:40
Enhance the error message for "foreign key mismatch" to include the names of the child and parent tables. Begin adding test cases for PRAGMA foreign_key_check. Make sure PRAGMA foreign_key_check gets all necessary table locks. check-in: 0f996352 user: drh tags: foreign-key-check
18:43
"PRAGMA foreign_key_check" with no argument checks the foreign keys on all tables. check-in: 57d47423 user: drh tags: foreign-key-check
18:05
Fix an issue with child keys that are not INTEGER PRIMARY KEY referencing INTEGER PRIMARY KEY in the parent. check-in: 603b695b user: drh tags: foreign-key-check
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/pragma.c.

  1111   1111           }
  1112   1112         }
  1113   1113       }
  1114   1114     }else
  1115   1115   #endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */
  1116   1116   
  1117   1117   #ifndef SQLITE_OMIT_FOREIGN_KEY
  1118         -  if( sqlite3StrICmp(zLeft, "foreign_key_check")==0 && zRight ){
  1119         -    FKey *pFK;
  1120         -    Table *pTab;
  1121         -    Table *pParent;
  1122         -    Index *pIdx;
  1123         -    int i, j;
  1124         -    int x;
  1125         -    int *aiCols;
         1118  +  if( sqlite3StrICmp(zLeft, "foreign_key_check")==0 ){
         1119  +    FKey *pFK;             /* A foreign key constraint */
         1120  +    Table *pTab;           /* Child table contain "REFERENCES" keyword */
         1121  +    Table *pParent;        /* Parent table that child points to */
         1122  +    Index *pIdx;           /* Index in the parent table */
         1123  +    int i;                 /* Loop counter:  Foreign key number for pTab */
         1124  +    int j;                 /* Loop counter:  Field of the foreign key */
         1125  +    HashElem *k;           /* Loop counter:  Next table in schema */
         1126  +    int x;                 /* result variable */
         1127  +    int regResult;         /* 3 registers to hold a result row */
         1128  +    int regKey;            /* Register to hold key for checking the FK */
         1129  +    int regRow;            /* Registers to hold a row from pTab */
         1130  +    int addrTop;           /* Top of a loop checking foreign keys */
         1131  +    int addrOk;            /* Jump here if the key is OK */
  1126   1132   
  1127   1133       if( sqlite3ReadSchema(pParse) ) goto pragma_out;
  1128         -    pTab = sqlite3LocateTable(pParse, 0, zRight, zDb);
  1129         -    if( pTab && pTab->pFKey ){
  1130         -      int regResult;
  1131         -      int regRow;
  1132         -      int regKey;
  1133         -      v = sqlite3GetVdbe(pParse);
  1134         -      sqlite3VdbeSetNumCols(v, 2);
  1135         -      sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "fkid", SQLITE_STATIC);
  1136         -      sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "rowid", SQLITE_STATIC);
  1137         -      sqlite3CodeVerifySchema(pParse, iDb);
         1134  +    regResult = pParse->nMem+1;
         1135  +    pParse->nMem += 3;
         1136  +    regKey = ++pParse->nMem;
         1137  +    regRow = ++pParse->nMem;
         1138  +    v = sqlite3GetVdbe(pParse);
         1139  +    sqlite3VdbeSetNumCols(v, 3);
         1140  +    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "table", SQLITE_STATIC);
         1141  +    sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "rowid", SQLITE_STATIC);
         1142  +    sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "fkid", SQLITE_STATIC);
         1143  +    sqlite3CodeVerifySchema(pParse, iDb);
         1144  +    k = sqliteHashFirst(&db->aDb[iDb].pSchema->tblHash);
         1145  +    while( k ){
         1146  +      if( zRight ){
         1147  +        pTab = sqlite3LocateTable(pParse, 0, zRight, zDb);
         1148  +        k = 0;
         1149  +      }else{
         1150  +        pTab = (Table*)sqliteHashData(k);
         1151  +        k = sqliteHashNext(k);
         1152  +      }
         1153  +      if( pTab->pFKey==0 ) continue;
         1154  +      if( pTab->nCol+regRow>pParse->nMem ) pParse->nMem = pTab->nCol + regRow;
  1138   1155         sqlite3OpenTable(pParse, 0, iDb, pTab, OP_OpenRead);
         1156  +      sqlite3VdbeAddOp4(v, OP_String8, 0, regResult, 0, pTab->zName,
         1157  +                        P4_TRANSIENT);
  1139   1158         for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
  1140   1159           pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
  1141   1160           if( pParent==0 ) break;
  1142   1161           pIdx = 0;
  1143         -        aiCols = 0;
  1144         -        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
         1162  +        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
  1145   1163           if( x==0 ){
  1146   1164             if( pIdx==0 ){
  1147   1165               sqlite3OpenTable(pParse, i, iDb, pParent, OP_OpenRead);
  1148   1166             }else{
  1149   1167               KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
  1150   1168               sqlite3VdbeAddOp3(v, OP_OpenRead, i, pIdx->tnum, iDb);
  1151   1169               sqlite3VdbeChangeP4(v, -1, (char*)pKey, P4_KEYINFO_HANDOFF);
  1152   1170             }
  1153   1171           }else{
         1172  +          k = 0;
  1154   1173             break;
  1155   1174           }
  1156         -        sqlite3DbFree(db, aiCols);
  1157   1175         }
  1158         -      pParse->nTab = i;
  1159         -      if( pFK==0 ){
  1160         -        int addrTop;
  1161         -        int addrOk;
  1162         -        addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
  1163         -        regResult = pParse->nMem+1;
  1164         -        pParse->nMem += 2;
  1165         -        regRow = pParse->nMem+1;
  1166         -        pParse->nMem += pTab->nCol;
  1167         -        regKey = ++pParse->nMem;
  1168         -        for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
  1169         -          pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
  1170         -          assert( pParent!=0 );
  1171         -          pIdx = 0;
  1172         -          aiCols = 0;
  1173         -          x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
  1174         -          assert( x==0 );
  1175         -          addrOk = sqlite3VdbeMakeLabel(v);
  1176         -          if( pIdx==0 ){
  1177         -            int iKey = pFK->aCol[0].iFrom;
  1178         -            if( iKey>=0 && iKey!=pTab->iPKey ){
  1179         -              sqlite3VdbeAddOp3(v, OP_Column, 0, iKey, regRow);
  1180         -              sqlite3ColumnDefault(v, pTab, iKey, regRow);
  1181         -              sqlite3VdbeAddOp2(v, OP_IsNull, regRow, addrOk);
  1182         -              sqlite3VdbeAddOp2(v, OP_MustBeInt, regRow,
  1183         -                 sqlite3VdbeCurrentAddr(v)+3);
  1184         -            }else{
  1185         -              sqlite3VdbeAddOp2(v, OP_Rowid, 0, regRow);
  1186         -            }
  1187         -            sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow);
  1188         -            sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk);
  1189         -            sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
         1176  +      if( pFK ) break;
         1177  +      if( pParse->nTab<i ) pParse->nTab = i;
         1178  +      addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, 0);
         1179  +      for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
         1180  +        pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
         1181  +        assert( pParent!=0 );
         1182  +        pIdx = 0;
         1183  +        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
         1184  +        assert( x==0 );
         1185  +        addrOk = sqlite3VdbeMakeLabel(v);
         1186  +        if( pIdx==0 ){
         1187  +          int iKey = pFK->aCol[0].iFrom;
         1188  +          if( iKey>=0 && iKey!=pTab->iPKey ){
         1189  +            sqlite3VdbeAddOp3(v, OP_Column, 0, iKey, regRow);
         1190  +            sqlite3ColumnDefault(v, pTab, iKey, regRow);
         1191  +            sqlite3VdbeAddOp2(v, OP_IsNull, regRow, addrOk);
         1192  +            sqlite3VdbeAddOp2(v, OP_MustBeInt, regRow,
         1193  +               sqlite3VdbeCurrentAddr(v)+3);
  1190   1194             }else{
  1191         -            for(j=0; j<pFK->nCol; j++){
  1192         -              sqlite3ExprCodeGetColumnOfTable(v, pTab, 0, pFK->aCol[j].iFrom,
  1193         -                                              regRow+j);
  1194         -              sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
  1195         -            }
  1196         -            sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
  1197         -            sqlite3VdbeChangeP4(v, -1,
  1198         -                     sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
  1199         -            sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
         1195  +            sqlite3VdbeAddOp2(v, OP_Rowid, 0, regRow);
         1196  +          }
         1197  +          sqlite3VdbeAddOp3(v, OP_NotExists, i, 0, regRow);
         1198  +          sqlite3VdbeAddOp2(v, OP_Goto, 0, addrOk);
         1199  +          sqlite3VdbeJumpHere(v, sqlite3VdbeCurrentAddr(v)-2);
         1200  +        }else{
         1201  +          for(j=0; j<pFK->nCol; j++){
         1202  +            sqlite3ExprCodeGetColumnOfTable(v, pTab, 0, pFK->aCol[j].iFrom,
         1203  +                                            regRow+j);
         1204  +            sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
  1200   1205             }
  1201         -          sqlite3DbFree(db, aiCols);
  1202         -          sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult);
  1203         -          sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1);
  1204         -          sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 2);
  1205         -          sqlite3VdbeResolveLabel(v, addrOk);
         1206  +          sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
         1207  +          sqlite3VdbeChangeP4(v, -1,
         1208  +                   sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
         1209  +          sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
  1206   1210           }
  1207         -        sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop+1);
  1208         -        sqlite3VdbeJumpHere(v, addrTop);
         1211  +        sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1);
         1212  +        sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+2);
         1213  +        sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 3);
         1214  +        sqlite3VdbeResolveLabel(v, addrOk);
  1209   1215         }
         1216  +      sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop+1);
         1217  +      sqlite3VdbeJumpHere(v, addrTop);
  1210   1218       }
  1211   1219     }else
  1212   1220   #endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */
  1213   1221   
  1214   1222   #ifndef NDEBUG
  1215   1223     if( sqlite3StrICmp(zLeft, "parser_trace")==0 ){
  1216   1224       if( zRight ){