SQLite

Check-in [97f7f73772]
Login

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
Timelines: family | ancestors | descendants | both | foreign-key-check
Files: files | file ages | folders
SHA1: 97f7f7377259ecf562019f62ebed0897cfae236e
User & Date: drh 2012-12-17 20:57:15.968
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: 25411f83f9 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: 97f7f73772 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: 0f9963526c user: drh tags: foreign-key-check)
Changes
Unified Diff Ignore Whitespace Patch
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

    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{








|



|


|
>








1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150

    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{
︙︙
1207
1208
1209
1210
1211
1212
1213


1214
1215
1216
1217
1218
1219
1220
1221
1222

          }
          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) */








>
>
|
|








1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225

          }
          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

  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, 








>








25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

  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,
︙︙
124
125
126
127
128
129
130
131
132

  CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);
} -sqlbody {
  DROP TABLE y;
  DROP TABLE x;
}

finish_test










<
<

125
126
127
128
129
130
131



  CREATE TABLE z(e, f, FOREIGN KEY(e, f) REFERENCES x);
} -sqlbody {
  DROP TABLE y;
  DROP TABLE x;
}

finish_test