c++sqliteforeign-keys

List SQLite Foreign Key Violations C++


I am adding the ability to delete any row in a database for my program, but this database shares keys with 6 other databases. I ensured that foreign keys were enabled to begin with:

void SQLitePatternSet::enableForeignKeys() 
{
    char *msg;

    int result = sqlite3_exec(db1, "PRAGMA foreign_keys=ON;", nullptr,
                          nullptr, &msg);
    if(result != SQLITE_OK)
    {
        std::string errMsg = "Failed to enable foreign key support: ";
        errMsg += msg;
        sqlite3_free(msg);
        throw std::runtime_error(errMsg);
    }
}

And that the appropriate rows in the database have ON DELETE CASCADE attached. Despite this, I still receive an error for SQLITE_CONSTRAINT_FOREIGNKEY. Now I would like to list which keys are in violation during delete:

bool SQLitePatternSet::removeRow(const std::int64_t db1RowID)
{
    bool removed = true;
    std::string sql = "DELETE FROM Database1 WHERE RowID = :id;";
    sqlite3_stmt *removeStmt;
    prepStatement(sql, removeStmt);

    sqlite3_bind_int64(removeStmt, 1, db1RowID);

    int sqlResult = sqlite3_step(removeStmt);
    if(sqlResult != SQLITE_DONE)
    {
        if(sqlite3_extended_errcode(db1) == SQLITE_CONSTRAINT_FOREIGNKEY)
        {
            removed = false;
            result = sqlite3_exec(db1, "PRAGMA foreign_key_check(Database1);", foreignKeyCheckCallback, nullptr, nullptr);
        }
        else
        {
            auto errMsg = formatErrMsg("removeRow failed");
            throw pset_errors::storage_error(errMsg);
        }
    }
    data1.erase(db1RowID);
    sqlite3_finalize(removeStmt);
    return removed;
}

But this is where I am stuck. It seems like the foreign key check should work, where:

int foreignKeyCheckCallback(void *a_param, int argc, char **argv, char **column){
    std::string msg = "Foreign key check:\n";
    for (int i = 0; i < argc; i++) {
        msg += argv[i];
        if (i < argc - 1)
            msg += "\n";
    }
    throw std::runtime_error(msg);
    return 1;
}

And result returns 0, which means there are no errors in the syntax for sqlite3_exec(). But after a few tests the callback is never run, which (afaik) means there are no keys which trigger the foreign key error in question. How can this be? Or does foreign_key_check simply not output to the callback by nature?


Solution

  • So it turns out it wasn't the foreign keys in Database 1 that were causing the error, but keys in another database that referenced the primary key in Database 1. Selecting all rows in Database 2 that reference 1, and then deleting them from 2, resolved the issue:

    bool SQLitePatternSet::removeRow(const std::int64_t db1RowID)
    {
    std::string sql1 = "SELECT DB2RowID FROM Database2 WHERE DB1RowID = :id;";
    sqlite3_stmt *selStmt;
    prepStatement(sql1, selStmt);
    sqlite3_bind_int64(selStmt, 1, db1RowID);
    
    int sqlresult1;
    std::vector<std::int64_t> db2RowIDs;
    do
    {
        sqlresult1 = sqlite3_step(selStmt);
        if(sqlresult1 == SQLITE_ROW)
        {
            std::int64_t db2RowID = sqlite3_column_int64(selStmt, 0);
            db2RowIDs.push_back(db2RowID);
        }
        else if(sqlresult1 != SQLITE_DONE)
        {
            throw pset_errors::storage_error(
                formatSQLErr("removeRow failed"));
        }
    } while(sqlresult1 != SQLITE_DONE);
    sqlite3_finalize(selStmt);
    
    bool removed = true;
    for (std::int64_t db2RowID: db2RowIDs) {
        std::string sql2 = "DELETE FROM Database2 WHERE DB2RowID = :id;";
        sqlite3_stmt *removeStmt;
        prepStatement(sql2, removeStmt);
        sqlite3_bind_int64(removeStmt, 1, db2RowID );
    
        int sqlResult2 = sqlite3_step(removeStmt);
        if(sqlResult2 != SQLITE_DONE)
        {
            if(sqlite3_extended_errcode(db1) == SQLITE_CONSTRAINT_FOREIGNKEY)
            {
                removed = false;
            }
            else
            {
                auto errMsg = formatErrMsg("removeRow failed");
                throw pset_errors::storage_error(errMsg);
            }
        }
        data1.erase(db1RowID);
        sqlite3_finalize(removeStmt);
    }
    return removed;
    

    }