Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add to the foreign_key_check pragma an extra output column "parent" that contains the name of the parent table for the constraint that failed. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | foreign-key-check |
| Files: | files | file ages | folders |
| SHA1: |
97f7f7377259ecf562019f62ebed0897 |
| User & Date: | drh 2012-12-17 20:57:15 |
Context
|
2012-12-17
| ||
| 22:32 | Added test cases for PRAGMA foreign_key_check. Fixed a bug that appears when the column order of the child and parent differ. (check-in: 25411f83 user: drh tags: foreign-key-check) | |
| 20:57 | Add to the foreign_key_check pragma an extra output column "parent" that contains the name of the parent table for the constraint that failed. (check-in: 97f7f737 user: drh tags: foreign-key-check) | |
| 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) | |
Changes
Changes to src/pragma.c.
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
....
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
|
int regKey; /* Register to hold key for checking the FK */
int regRow; /* Registers to hold a row from pTab */
int addrTop; /* Top of a loop checking foreign keys */
int addrOk; /* Jump here if the key is OK */
if( sqlite3ReadSchema(pParse) ) goto pragma_out;
regResult = pParse->nMem+1;
pParse->nMem += 3;
regKey = ++pParse->nMem;
regRow = ++pParse->nMem;
v = sqlite3GetVdbe(pParse);
sqlite3VdbeSetNumCols(v, 3);
sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "table", SQLITE_STATIC);
sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "rowid", SQLITE_STATIC);
sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "fkid", SQLITE_STATIC);
sqlite3CodeVerifySchema(pParse, iDb);
k = sqliteHashFirst(&db->aDb[iDb].pSchema->tblHash);
while( k ){
if( zRight ){
pTab = sqlite3LocateTable(pParse, 0, zRight, zDb);
k = 0;
}else{
................................................................................
}
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);
sqlite3VdbeAddOp2(v, OP_Integer, i-1, regResult+2);
sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, 3);
sqlite3VdbeResolveLabel(v, addrOk);
}
sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop+1);
sqlite3VdbeJumpHere(v, addrTop);
}
}else
#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */
|
|
|
|
>
>
>
|
|
|
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
....
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
|
int regKey; /* Register to hold key for checking the FK */
int regRow; /* Registers to hold a row from pTab */
int addrTop; /* Top of a loop checking foreign keys */
int addrOk; /* Jump here if the key is OK */
if( sqlite3ReadSchema(pParse) ) goto pragma_out;
regResult = pParse->nMem+1;
pParse->nMem += 4;
regKey = ++pParse->nMem;
regRow = ++pParse->nMem;
v = sqlite3GetVdbe(pParse);
sqlite3VdbeSetNumCols(v, 4);
sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "table", SQLITE_STATIC);
sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "rowid", SQLITE_STATIC);
sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "parent", SQLITE_STATIC);
sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "fkid", SQLITE_STATIC);
sqlite3CodeVerifySchema(pParse, iDb);
k = sqliteHashFirst(&db->aDb[iDb].pSchema->tblHash);
while( k ){
if( zRight ){
pTab = sqlite3LocateTable(pParse, 0, zRight, zDb);
k = 0;
}else{
................................................................................
}
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);
}
sqlite3VdbeAddOp2(v, OP_Next, 0, addrTop+1);
sqlite3VdbeJumpHere(v, addrTop);
}
}else
#endif /* !defined(SQLITE_OMIT_FOREIGN_KEY) */
|
Changes to test/fkey_malloc.test.
25
26
27
28
29
30
31
32
33
34
35
36
37
38
...
124
125
126
127
128
129
130
131
132
|
CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
CREATE TABLE t2(x REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE);
} -sqlbody {
INSERT INTO t1 VALUES('aaa', 1);
INSERT INTO t2 VALUES('aaa');
UPDATE t1 SET a = 'bbb';
DELETE FROM t1;
}
do_malloc_test fkey_malloc-2 -sqlprep {
PRAGMA foreign_keys = 1;
CREATE TABLE t1(a, b, UNIQUE(a, b));
} -sqlbody {
CREATE TABLE t2(x, y,
................................................................................
CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);
} -sqlbody {
DROP TABLE y;
DROP TABLE x;
}
finish_test
|
>
<
<
|
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
...
125
126
127
128
129
130
131
|
CREATE TABLE t1(a PRIMARY KEY, b UNIQUE);
CREATE TABLE t2(x REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE);
} -sqlbody {
INSERT INTO t1 VALUES('aaa', 1);
INSERT INTO t2 VALUES('aaa');
UPDATE t1 SET a = 'bbb';
DELETE FROM t1;
PRAGMA foreign_key_check;
}
do_malloc_test fkey_malloc-2 -sqlprep {
PRAGMA foreign_keys = 1;
CREATE TABLE t1(a, b, UNIQUE(a, b));
} -sqlbody {
CREATE TABLE t2(x, y,
................................................................................
CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);
} -sqlbody {
DROP TABLE y;
DROP TABLE x;
}
finish_test
|