Unable to switch the sqlite3 database foreign keys on

[starling13]

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

//...
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

To copy to clipboard, switch view to plain text mode 

[starling13]

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]

Thanks for posting the solution, I wasn't aware of that restriction!