![]() I had attempted to make a trivial recreation of the issue as follows: >sqlite3 a.sqlite Sorry about misunderstanding your problem.Īs for "as designed", we want to be sure that it works as intended.Ĭould you please explain, or by edits show, how the following differs materially from what you have described? create table if not exists t1(id integer primary key) Ĭreate table if not exists t2(fk integer references t1(id) on delete cascade) Ĭreate table if not exists B.t1(id integer primary key) Ĭreate table if not exists B.t2(fk integer references t1(id) on delete cascade) Ĭreate table if not exists C.t1(id integer primary key) Ĭreate table if not exists C.t2(fk integer references t1(id) on delete cascade) I realize it might be a little confusing without the actual databases but that's the gist and I can try to answer questions on specifics if necessary. If we then DETACH database B and try again, it works. Then when we try to delete the data in database C, we get a foreign key constraint violation. ![]() We then insert something into table T1 in database C with the same ID as the data in database B which is the primary key of table T1 and is referenced by T2. Database B has data in both tables where table T2 has a foreign key reference to T1. Said another way, database B has two tables "T1" and "T2" and database C also has two tables "T1" and "T2". This could not be verified with a trivial example so it might be more complicated. We looked at the query plan and saw a "SCAN tablename" without a prefix which we assumed to indicate that it might be going to the "Least Recently Attached" database similar to the behavior when you do a query without a database prefix. What we noticed is that foreign key references are being enforced against database B when we perform a query against C. B and C have a bunch of tables that are identically named, perhaps with slightly different schema. We would like to check whether this is expected behavior or a bug.Īttempting to reproduce this with a trivial example has proven difficult so I will explain what is happening and perhaps open a support contract if it turns out its something specific to our schema and/or unexpected.īasically we have 3 databases and we open the first database (call it A) and attach the other two (call them B, and C). 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.We recently encountered unexpected behavior in sqlite with respect to how foreign key references are enforced when we have multiple databases attached, where the databases have identical table names. 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.Īnd while it's not as elegant, I can hard code all deletes and updates. "FOREIGN KEY(recipe_id) REFERENCES recipes(recipe_id) " "ingredient_id INTEGER PRIMARY KEY AUTOINCREMENT, " "recipe_id INTEGER PRIMARY KEY AUTOINCREMENT,"ĭetail table: query2 = "CREATE TABLE IF NOT EXISTS recipe_ingredients(" Master table: query1 = "CREATE TABLE IF NOT EXISTS recipes(" But just a simple delete in the master table fails to delete across the detail tables. ![]() 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. MainWindow::MainWindow(QWidget *parent) :ĭb = QSqlDatabase::addDatabase("QSQLITE") Īnd I got that from Asimov, here on the Forum. ![]() ![]() At the beginning of my program, I have this: #include "mainwindow.h" ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |