PDA

View Full Version : Unable to switch the sqlite3 database foreign keys on



starling13
6th September 2016, 16:19
I'v faced strange problem. Executing the "PRAGMA foreign_keys = ON" query on the sqlite3 database has no effect.



//...
QSqlQuery query(_database);

if (!query.exec("PRAGMA foreign_keys = ON"))
qCritical() << "QSQLQuery::exec";

if (!query.exec("PRAGMA foreign_keys"))
qCritical() << "QSQLQuery::exec";

if (query.next())
// Always prints "0", while using sqlite3 command line tool
// "PRAGMA foreign_keys" query returns 0 and 1, in correspondence with
// the value, which was set by the last "PRAGMA foreign_keys = ON|OFF" query
qDebug() << query.value(0).toUInt();

if (!query.prepare("DELETE FROM \"MASTER_TABLE\" WHERE ID = :id"))
qCritical() << "QSQLQuery::prepare";

query.bindValue(":id", id);
if (!query.exec())
qCritical() << "QSQLQuery::exec";

// MASTER_TABLE record is deleted, DETAIL_TABLE, containing foreign key
// with "on delete cascade" option not deleted

starling13
7th September 2016, 14:02
Problem was in that query of changing foreign keys control policy was executed inside a transaction.
As sqlite docs say:

It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.

Solved, sorry for disturbance.

jefftee
7th September 2016, 20:57
Thanks for posting the solution, I wasn't aware of that restriction!