![]() ![]() SELECT RAISE(ABORT, "Cannot update table 'B'.") INTEGER NOT NULL REFERENCES () ON DELETE CASCADE ON UPDATE RESTRICT, SELECT RAISE(ABORT, "Cannot update table 'A'.") INTEGER NOT NULL REFERENCES () ON DELETE RESTRICT ON UPDATE RESTRICT, The actual database tables where this occurs look more like this: But I can try to provide as much detail as I can without providing our proprietary database schema. Wanted to at least get the question out there. However, as you can see from the output and as mentioned in the initial post, the issue did not reproduce so its likely something a bit more obscure. Main: C:\Users\pevans\Downloads\sqlite-tools-win3200\sqlite-tools-win3200\a.sqlite r/wī: C:\Users\pevans\Downloads\sqlite-tools-win3200\sqlite-tools-win3200\b.sqlite r/wĬ: C:\Users\pevans\Downloads\sqlite-tools-win3200\sqlite-tools-win3200\c.sqlite r/w |-SEARCH c.A USING INTEGER PRIMARY KEY (rowid=?) Sqlite> EXPLAIN QUERY PLAN delete from c.A WHERE AID=1 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). 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |