SQLite3 Foreign Keys

? Offline

At the beginning of my program, I have this:

#include "mainwindow.h"
#include "ui_mainwindow.h"


MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("myDb");

    QSqlQuery query(db);
    query.exec("PRAGMA foreign_keys = ON;");
}

And I got that from Asimov, here on the Forum. In all the posts on this topic that I've read, they're all fairly consistent in their remedies to the elusive turning on the SQLite3 Foreign Key apparatus. But just a simple delete in the master table fails to delete across the detail tables.

Master table:

query1 = "CREATE TABLE IF NOT EXISTS recipes("
                "recipe_id INTEGER PRIMARY KEY AUTOINCREMENT,"
                "name VARCHAR(50);";

Detail table:

query2 = "CREATE TABLE IF NOT EXISTS recipe_ingredients("
                "ingredient_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "ingredient TEXT, "
                "amount FLOAT, "
                "inventory_id INTEGER, "
                "recipe_id INTEGER, "
                "FOREIGN KEY(recipe_id) REFERENCES recipes(recipe_id) "
                "ON DELETE CASCADE ON UPDATE CASCADE)";

I've been messing around with this for a while, long before even helping Noah drive nails in the Ark! But I can't get it to fire up.
And while it's not as elegant, I can hard code all deletes and updates. In all honesty, I don't think turning on foreign keys (as shown in my code) can be useful inside Qt until Qt has data-aware components. I sure wld like for someone to prove me wrong =)

JKSH

@landslyde said in SQLite3 Foreign Keys:

But just a simple delete in the master table fails to delete across the detail tables.

Do you mean the entry in recipes gets deleted but the entry/entries in recipe_ingredients remain? Sounds like foreign keys have not been turned on.

To check: Try to insert something into recipe_ingredients with an invalid recipe_id. The insertion should fail.

Did you call the pragma before creating the tables?

I don't think turning on foreign keys (as shown in my code) can be useful inside Qt until Qt has data-aware components.

Qt doesn't do anything special -- it just forwards all your queries to the underlying SQLite library.

? Offline

The following code works. And you don't have to have SQLite3 installed with foreign_keys = on. You do, however, need to make sure NO query is performed before this code runs. Do that and this works like a charm.

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    db = QSqlDatabase::addDatabase("QSQLITE");
    db.setDatabaseName("myDb");

    if (!db.open())
        ui->statusBar->showMessage(db.lastError().text());
    else
    {
        // NOT EVERYONE WILL HAVE FOREIGN KEYS ENABLED.
        // SO THE SQLITE3 DEVS GAVE US THIS LITTLE GEM 
        // TO ENSURE FOREIGN KEY ACCESS.
        QSqlQuery query(db);
        query.exec("PRAGMA foreign_keys = ON;");

        ui->statusBar->showMessage("Database connected");
    }
}