Foreign key is ignored when deleting

Asimov

I have two tables referentially linked by a foreign key. The tables are in a sqlite database.

query.exec("PRAGMA foreign_keys = ON;");
        query.exec("create table boxes "
                  "(boxID integer primary key, "
                  "boxName varchar(20), "
                  "boxLocation varchar(30))");

query.exec("create table boxContents "
                  "(contentsID integer primary key,"
                   "boxID integer,"
                  "itemName varchar(20),"
                  "itemDescription varchar(30),"
                   "FOREIGN KEY(boxID) REFERENCES boxes(boxID) ON DELETE CASCADE)");

I am used to doing this kind of thing in php and mysql. Normally when you add a foreign key on delete cascade and I delete the item from the first table, it also removes the items from the second table.
In my first table I am displaying it using a model, and I delete with the following code.

 int deleteMe = getRecordID(ui,modelBox,"box");// Gets the id to delete
    modelBox->removeRow(deleteMe);

The delete is successful, but it is not deleting the referentially linked table.
This does not seem right as delete on cascade should mean that as soon as I delete from the main table it should deleted the linked files in the other table.

Why is referential integrity being ignored?

Asimov

I have found the solution.
I thought it was enough to turn on foreign keys when creating the tables eg

query.exec("PRAGMA foreign_keys = ON;");

That is not the case you have to turn on foreign keys when you first connect to the database, as in the following code.
This has solved my problem, and now when I delete in the one table, it deletes the data in the other table.

The only problem I have to work out now is how to remove the blank line after deleting. So I have to research refreshing a model after deleting.

bool DbManager::connOpen()
{
    mydb = QSqlDatabase::addDatabase("QSQLITE");
    mydb.setDatabaseName(myConnection);

    if (!mydb.open())
    {
        qDebug() << "Error: connection with database fail";
    }
    else
    {
        qDebug() << "Database: connection ok";
        QSqlQuery query;
        query.exec("PRAGMA foreign_keys = ON;");
    }
}

SGaist

Hi,

What blank line ?