/ 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 Show Whitespace Changes 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;
         1134  +    regResult = pParse->nMem+1;
         1135  +    pParse->nMem += 3;
         1136  +    regKey = ++pParse->nMem;
         1137  +    regRow = ++pParse->nMem;
  1133   1138         v = sqlite3GetVdbe(pParse);
  1134         -      sqlite3VdbeSetNumCols(v, 2);
  1135         -      sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "fkid", SQLITE_STATIC);
         1139  +    sqlite3VdbeSetNumCols(v, 3);
         1140  +    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "table", SQLITE_STATIC);
  1136   1141         sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "rowid", SQLITE_STATIC);
         1142  +    sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "fkid", SQLITE_STATIC);
  1137   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;
         1176  +      if( pFK ) break;
         1177  +      if( pParse->nTab<i ) pParse->nTab = i;
  1162   1178           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   1179           for(i=1, pFK=pTab->pFKey; pFK; i++, pFK=pFK->pNextFrom){
  1169   1180             pParent = sqlite3LocateTable(pParse, 0, pFK->zTo, zDb);
  1170   1181             assert( pParent!=0 );
  1171   1182             pIdx = 0;
  1172         -          aiCols = 0;
  1173         -          x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, &aiCols);
         1183  +        x = sqlite3FkLocateIndex(pParse, pParent, pFK, &pIdx, 0);
  1174   1184             assert( x==0 );
  1175   1185             addrOk = sqlite3VdbeMakeLabel(v);
  1176   1186             if( pIdx==0 ){
  1177   1187               int iKey = pFK->aCol[0].iFrom;
  1178   1188               if( iKey>=0 && iKey!=pTab->iPKey ){
  1179   1189                 sqlite3VdbeAddOp3(v, OP_Column, 0, iKey, regRow);
  1180   1190                 sqlite3ColumnDefault(v, pTab, iKey, regRow);
................................................................................
  1194   1204                 sqlite3VdbeAddOp2(v, OP_IsNull, regRow+j, addrOk);
  1195   1205               }
  1196   1206               sqlite3VdbeAddOp3(v, OP_MakeRecord, regRow, pFK->nCol, regKey);
  1197   1207               sqlite3VdbeChangeP4(v, -1,
  1198   1208                        sqlite3IndexAffinityStr(v,pIdx), P4_TRANSIENT);
  1199   1209               sqlite3VdbeAddOp4Int(v, OP_Found, i, addrOk, regKey, 0);
  1200   1210             }
  1201         -          sqlite3DbFree(db, aiCols);
  1202         -          sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult);
  1203   1211             sqlite3VdbeAddOp2(v, OP_Rowid, 0, regResult+1);
  1204         -          sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 2);
         1212  +        sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+2);
         1213  +        sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 3);
  1205   1214             sqlite3VdbeResolveLabel(v, addrOk);
  1206   1215           }
  1207   1216           sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop+1);
  1208   1217           sqlite3VdbeJumpHere(v, addrTop);
  1209   1218         }
  1210         -    }
  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 ){
  1217   1225         if( sqlite3GetBoolean(zRight, 0) ){