Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| 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: |
57d47423a65d183cb1efcb95b28cb143 |
| User & Date: | drh 2012-12-17 18:43:02 |
|
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 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) ){