[SailfishDevel] SQLite: ON DELETE CASCADE with Foreign Keys not working

Matthias Fehring buschmann at huessenbergnetz.de
Wed Aug 24 11:54:42 UTC 2016


Hello Thomas,

for me foreign key support is working. Directly after establishing the 
connection to the SQLite database (QSqlDatabase::open()) I use QSqlQuery to 
execute "PRAGMA foreign_keys = ON".

I think you have to do it every time you open a closed database, so I 
implemented it in my database connection methods that it is executed if the 
database connection was closed before.

Best greetings
Matthias


Am Mittwoch, 24. August 2016, 12:39:04 CEST schrieb TE:
> Hello fellow devs,
> 
> I have two SQLite tables. The second table references a key from the
> first table as foreign key. Now when I delete a row in the first table I
> want all rows from the second table referencing the deleted key to be
> deleted as well. For this the action ON DELETE CASCADE seems to be
> appropriate. Unfortunately this doesn't work and the corresponding rows
> in the second table are *not* deleted.
> 
> Here are the table definitions:
> 
> First table:
> CREATE TABLE IF NOT EXISTS NodeMetaData (id INTEGER PRIMARY KEY,
> parentId INTEGER);
> 
> Second table:
> CREATE TABLE IF NOT EXISTS NodeConcreteData (refId INTEGER NOT NULL,
> FOREIGN KEY(refId) REFERENCES NodeMetaData(id) ON DELETE CASCADE);
> 
> In the sqlite docs, it is mentioned that foreign key support has to be
> acitvated. So I tried tx.executeSql("PRAGMA foreign_keys = ON;"). But
> the query tx.executeSql("PRAGMA foreign_keys;") returns 0.
> 
> When I recreate the scenario in an interactive SQLite session, the above
> works (PRAGMA foreign_keys = ON has to be executed).
> 
> Any ideas?
> Is the SFOS Qt lib compiled with foreign key support?
> What am I missing?
> 
> Many thanks in advance!
> Thomas
> _______________________________________________
> SailfishOS.org Devel mailing list
> To unsubscribe, please send a mail to devel-unsubscribe at lists.sailfishos.org


-- 
Das Gesetz hat zum Schneckengang verdorben, was Adlerflug geworden wäre.
(Friedrich Schiller - Die Räuber)

Und der Buschfunk spielt gerade "Was machen wir bloss" von "EL*KE".

www.buschmann23.de
GPG-Key: 0x614C3258
GPG Fingerprint: B770 E0D0 69CF BFC1 5FE1 D78E 3A70 A936 614C 3258
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 819 bytes
Desc: This is a digitally signed message part.
URL: <https://lists.sailfishos.org/pipermail/devel/attachments/20160824/5ea45ef6/attachment.pgp>


More information about the Devel mailing list