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. We would like to check whether this is expected behavior or a bug.

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

Basically we have 3 databases and we open the first database (call it A) and attach the other two (call them B, and C). B and C have a bunch of tables that are identically named, perhaps with slightly different schema. What we noticed is that foreign key references are being enforced against database B when we perform a query against C. 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. This could not be verified with a trivial example so it might be more complicated.

Said another way, database B has two tables "T1" and "T2" and database C also has two tables "T1" and "T2". Database B has data in both tables where table T2 has a foreign key reference to T1. 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. Then when we try to delete the data in database C, we get a foreign key constraint violation. If we then DETACH database B and try again, it works.

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. Anyone have any thoughts?

If I understand your report, (which is definitely a hypothetical), it amounts to this: Referential integrity enforcement is enabled against all databases associated with a connection, not just main, when "PRAGMA foreign_keys = true" is run, and this is contrary to expectation that attached databases would not be made to have such enforcement by that pragma.

That behavior is as-documented. I did observe, while ascertaining that "as-documented" assertion, that the method used to distinguish connection-wide pragmas from schema-targeted pragmas is not entirely clear, at least not without reading the right paragraphs at the top of the page.

My reading of Parker's problem (which is also only hypothetically correct) is not that he wants foreign-key enforcement to be different in different attached databases... I believe what he is seeing (or thinks he's seeing) is that a foreign key reference/enforcement is going to the wrong database. If I'm interpreting things correctly, there's an FK between B.T1 to B.T2, and one between C.T1 and C.T2. However, when he deletes something from C.T1, it's complaining because it would invalidate a reference from B.T2. Or something along those lines.

That is correct. There is a foreign key reference that we expect to be enforced on the same database (in each of the databases B and C) but it instead appears to be enforced against a table in a different database that is attached on the same connection. We did not expect foreign key references to cross databases at all, let alone for there to be a non-obvious precedence of the attached databases with tables of the same name. We verified this precedence by swapping the order of attaching the databases B and C and then the foreign key constraint violation did not occur. If this is the intended behavior of SQLite, then that's fine. Just wanted to verify that it was "as designed" so that we can communicate that around and create appropriate designs accordingly.

Sorry about misunderstanding your problem.

As for "as designed", we want to be sure that it works as intended.

Could 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);
   create table if not exists t2(fk integer references t1(id) on delete cascade);

   attach ':memory:' as B;
   attach ':memory:' as C;

   create table if not exists B.t1(id integer primary key);
   create table if not exists B.t2(fk integer references t1(id) on delete cascade);
 
   create table if not exists C.t1(id integer primary key);
   create table if not exists C.t2(fk integer references t1(id) on delete cascade);
 
   insert or replace into t1 values(1);
   insert or replace into t2 values(1);
 
   insert into B.t1 values(1);
   insert into B.t2 values(1);
 
   insert into C.t1 values(1);
   insert into C.t2 values(1);
 
   .print Tables built and loaded.
 
   pragma foreign_keys = true;
 
   .print Deleting from C:
   delete from C.t1 where id==1;
 
   .header on
   .print Content of main.t2:
   select * from t2;
   .print Content of B.t2:
   select * from B.t2;
   .print Content of C.t2:
   select * from C.t2;

I had attempted to make a trivial recreation of the issue as follows:

>sqlite3 a.sqlite
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> CREATE TABLE [Test] (
   ...> [TestID] INTEGER PRIMARY KEY
   ...> );
sqlite> .exit

>sqlite3 b.sqlite
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> CREATE TABLE [A] (
   ...> [AID] INTEGER PRIMARY KEY
   ...> );
sqlite> CREATE TABLE [B] (
   ...> [BID] INTEGER PRIMARY KEY,
   ...> [AID] INTEGER NOT NULL REFERENCES A([AID])
   ...> );
sqlite> insert into A VALUES(1);
sqlite> insert into B VALUES(1,1);
sqlite> .exit

>sqlite3 c.sqlite
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> CREATE TABLE [A] (
   ...> [AID] INTEGER PRIMARY KEY
   ...> );
sqlite> CREATE TABLE [B] (
   ...> [BID] INTEGER PRIMARY KEY,
   ...> [AID] INTEGER NOT NULL REFERENCES A([AID])
   ...> );
sqlite> .exit

>sqlite3 a.sqlite
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> ATTACH DATABASE "b.sqlite" as b;
sqlite> PRAGMA foreign_keys=ON;
sqlite> ATTACH DATABASE "c.sqlite" as c;
sqlite> PRAGMA foreign_keys=ON;
sqlite> insert into c.A VALUES(1);
sqlite> delete from c.A WHERE AID=1;
sqlite> insert into c.A VALUES(1);
sqlite> EXPLAIN QUERY PLAN delete from c.A WHERE AID=1;
QUERY PLAN
|--SEARCH c.A USING INTEGER PRIMARY KEY (rowid=?)
`--SCAN B
sqlite> select * from B;
1|1
sqlite> .database
main: C:\Users\pevans\Downloads\sqlite-tools-win32-x86-3380200\sqlite-tools-win32-x86-3380200\a.sqlite r/w
b: C:\Users\pevans\Downloads\sqlite-tools-win32-x86-3380200\sqlite-tools-win32-x86-3380200\b.sqlite r/w
c: C:\Users\pevans\Downloads\sqlite-tools-win32-x86-3380200\sqlite-tools-win32-x86-3380200\c.sqlite r/w
sqlite> select * from c.A;
1
sqlite> select * from c.B;

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. Wanted to at least get the question out there. But I can try to provide as much detail as I can without providing our proprietary database schema. The actual database tables where this occurs look more like this:

Database B:

CREATE TABLE [A] (
  [AID] INTEGER PRIMARY KEY AUTOINCREMENT,
  [AnotherID] INTEGER NOT NULL REFERENCES [AnotherTable]([AnotherID]) ON DELETE RESTRICT ON UPDATE RESTRICT,
  [Something] INTEGER NOT NULL,
  [SomethingElse] INTEGER NOT NULL,
  UNIQUE ([AnotherID], [Something], [SomethingElse])
);
CREATE TRIGGER BeforeUpdateOnA
BEFORE UPDATE ON A
FOR EACH ROW
BEGIN
   SELECT RAISE(ABORT, "Cannot update table 'A'.");
END;

CREATE TABLE [B] (
    [AID] INTEGER NOT NULL REFERENCES [A]([AID]) ON DELETE CASCADE ON UPDATE RESTRICT,
    [YetAnotherID] INTEGER NOT NULL REFERENCES [YetAnotherTable]([YetAnotherID]) ON DELETE RESTRICT ON UPDATE RESTRICT,
    UNIQUE ([AID])
);
CREATE TRIGGER BeforeUpdateOnB
BEFORE UPDATE ON B
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, "Cannot update table 'B'.");
END;

Database C:

CREATE TABLE [A] (
  [AID] INTEGER PRIMARY KEY AUTOINCREMENT,
  [AString] TEXT NOT NULL UNIQUE CHECK (length([AString]) > 0),
  [AnotherID] INTEGER NOT NULL REFERENCES [AnotherTable]([AnotherID]) ON DELETE RESTRICT ON UPDATE RESTRICT,
  [Something] INTEGER NOT NULL,
  [SomethingElse] INTEGER NOT NULL,
  UNIQUE ([AnotherID], [Something], [SomethingElse])
);

CREATE TABLE [B] (
    [BID] INTEGER PRIMARY KEY AUTOINCREMENT,
    [AID] INTEGER NOT NULL REFERENCES [A]([AID]) ON DELETE RESTRICT ON UPDATE RESTRICT,
    [YetAnotherID] INTEGER NOT NULL REFERENCES [YetAnotherTable]([YetAnotherID]) ON DELETE RESTRICT ON UPDATE RESTRICT,
);

Which version of SQLite are you using?

In other words, what does the query "SELECT sqlite_version()" return in the app?

Thanks,

Dan.

I have reproduced this on both Linux/ARM 3.14.2 and Windows/amd64 3.38.2. The databases were likely created with 3.14.2 originally.

Can you post the schemas for each database (output of ".schema")?

Thanks,

Dan.

I do not see a likely direction of departure from the schema you posted, or from mine, leading to a reproduction of the problem you describe. (There are too many variables to make guessing a productive approach.)

If you can provide a repro case, and it in fact appears to represent a bug, it will surely be fixed. But with only the vague indications we have now, it is a poor use of time for anybody else to be trying to guess how to reproduce the problem.

I hope you understand that there may be something subtly wrong with your failing DML that we would not even think to include in a guessing quest. Your effort to reduce your proprietary schema to one which still has the problem and is not proprietary would go far toward resolving whether this is a SQLite problem or purely a local SQL-oops problem.

What Larry said, and to be clear, if what you describe actually happens, it surely is a bug and not an intended, nor documented "feature".

As of now though, I could not reproduce your described bug and neither could you, so it's a fair guess that, as Larry suggested, their might be some subtle mistake in your actual system code/SQL that actually caused this.

Were I you, I would redouble my efforts to mimic the bug, perhaps starting from your full proprietary schema and chipping away until it no longer happens, because if you do find a bug it will be kudos from us, but if not, the search will surely lead you to the bug in your own code - and that is always worth the effort.

First, in cases like this, it's important to determines whether the problem is code-related, or schema-related. Because of SQLite's extensive testing, we tend to lean towards the former on this list, but bugs do happen of course.

So take your proprietary DBs, and open them (the main one, then various attach calls) in the official CLI Shell, enable FKs, and do at the CLI what you code does. It the problem repros, with the latest release CLI, then it's likely schema-related. If not, then the problem is likely in your code.

If you use transactions, be sure to order the begin / commit and attach / detach calls the same way your application does them, also opening/attaching the DBs in the same order.

If OTOH, you do repro, then I suggest you share your DBs (possibly trimed of fat) privately with the SQLite devs. Richard regularly does this here, and although I don't remember Dan doing it, the fact he chimed in is a good sign you've picked the interest of the Dev Team. What you report would be an important bug to fix, should it prove reproducible. My $0.02.

Sorry it took me a little bit to make some time to make the full reproduction. But here it is starting from scratch using the latest released sqlite3 on Windows:

>sqlite3 --version
3.38.2 2022-03-26 13:51:10 d33c709cc0af66bc5b6dc6216eba9f1f0b40960b9ae83694c986fbf4c1d6f08f

>sqlite3 a.sqlite
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> CREATE TABLE [Test] (
...> [TestID] INTEGER PRIMARY KEY AUTOINCREMENT
...> );
sqlite> .exit

>sqlite3 b.sqlite
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> CREATE TABLE [A] (
...>   [AID] INTEGER PRIMARY KEY AUTOINCREMENT,
...>   [AnotherID] INTEGER NOT NULL REFERENCES [AnotherTable]([AnotherID]) ON DELETE RESTRICT ON UPDATE RESTRICT,
...>   [Something] INTEGER NOT NULL,
...>   [SomethingElse] INTEGER NOT NULL,
...>   UNIQUE ([AnotherID], [Something], [SomethingElse])
...> );
sqlite> CREATE TRIGGER BeforeUpdateOnA
...> BEFORE UPDATE ON A
...> FOR EACH ROW
...> BEGIN
...>    SELECT RAISE(ABORT, "Cannot update table 'A'.");
...> END;
sqlite> CREATE TABLE [B] (
...>     [AID] INTEGER NOT NULL REFERENCES [A]([AID]) ON DELETE CASCADE ON UPDATE RESTRICT,
...>     [YetAnotherID] INTEGER NOT NULL REFERENCES [YetAnotherTable]([YetAnotherID]) ON DELETE RESTRICT ON UPDATE RESTRICT,
...>     UNIQUE ([AID])
...> );
sqlite> CREATE TRIGGER BeforeUpdateOnB
...> BEFORE UPDATE ON B
...> FOR EACH ROW
...> BEGIN
...>     SELECT RAISE(ABORT, "Cannot update table 'B'.");
...> END;
sqlite> CREATE TABLE [AnotherTable] (
...> [AnotherID] INTEGER PRIMARY KEY AUTOINCREMENT
...> );
sqlite> CREATE TABLE [YetAnotherTable] (
...> [YetAnotherID] INTEGER PRIMARY KEY AUTOINCREMENT
...> );
sqlite> .exit

C:\Users\pevans\Downloads\sqlite-tools-win32-x86-3380200\sqlite-tools-win32-x86-3380200>sqlite3 c.sqlite
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> CREATE TABLE [A] (
...>   [AID] INTEGER PRIMARY KEY AUTOINCREMENT,
...>   [AString] TEXT NOT NULL UNIQUE CHECK (length([AString]) > 0),
...>   [AnotherID] INTEGER NOT NULL REFERENCES [AnotherTable]([AnotherID]) ON DELETE RESTRICT ON UPDATE RESTRICT,
...>   [Something] INTEGER NOT NULL,
...>   [SomethingElse] INTEGER NOT NULL,
...>   UNIQUE ([AnotherID], [Something], [SomethingElse])
...> );
sqlite> CREATE TABLE [B] (
...>     [BID] INTEGER PRIMARY KEY AUTOINCREMENT,
...>     [AID] INTEGER NOT NULL REFERENCES [A]([AID]) ON DELETE RESTRICT ON UPDATE RESTRICT,
...>     [YetAnotherID] INTEGER NOT NULL REFERENCES [YetAnotherTable]([YetAnotherID]) ON DELETE RESTRICT ON UPDATE RESTRICT
...> );
sqlite> CREATE TABLE [AnotherTable] (
...> [AnotherID] INTEGER PRIMARY KEY AUTOINCREMENT
...> );
sqlite> CREATE TABLE [YetAnotherTable] (
...> [YetAnotherID] INTEGER PRIMARY KEY AUTOINCREMENT
...> );
sqlite> .exit

>sqlite3 b.sqlite
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> .header on
sqlite> .mode column
sqlite> .table
A                AnotherTable     B                YetAnotherTable
sqlite> INSERT INTO AnotherTable VALUES(1);
sqlite> INSERT INTO YetAnotherTable VALUES(1);
sqlite> INSERT INTO A VALUES(1,1,1,1);
sqlite> INSERT INTO B VALUES(1,1);
sqlite> SELECT * FROM A;
AID  AnotherID  Something  SomethingElse
---  ---------  ---------  -------------
1    1          1          1
sqlite> SELECT * FROM B;
AID  YetAnotherID
---  ------------
1    1
sqlite> .exit

>sqlite3 a.sqlite
SQLite version 3.38.2 2022-03-26 13:51:10
Enter ".help" for usage hints.
sqlite> ATTACH DATABASE "b.sqlite" as b;
sqlite> PRAGMA foreign_keys=ON;
sqlite> ATTACH DATABASE "c.sqlite" as c;
sqlite> PRAGMA foreign_keys=ON;
sqlite> .database
main: [MYPATH]/a.sqlite r/w
b: [MYPATH]/b.sqlite r/w
c: [MYPATH]/c.sqlite r/w
sqlite> INSERT INTO c.AnotherTable VALUES(1);
sqlite> INSERT INTO c.YetAnotherTable VALUES(1);
sqlite> INSERT INTO c.A VALUES(1,"123",1,1,1);
sqlite> DELETE FROM c.A WHERE AString = "123";
Runtime error: FOREIGN KEY constraint failed (19)
sqlite> EXPLAIN QUERY PLAN DELETE FROM c.A WHERE AString = "123";
QUERY PLAN
|--SEARCH c.A USING INDEX sqlite_autoindex_A_1 (AString=?)
`--SCAN B
sqlite> DETACH DATABASE b;
sqlite> ATTACH DATABASE "b.sqlite" as b;
sqlite> .database
main: [MYPATH]\a.sqlite r/w
c: [MYPATH]\c.sqlite r/w
b: [MYPATH]\b.sqlite r/w
sqlite> DELETE FROM c.A WHERE AString = "123";
sqlite>

Thanks for taking the time to analyze this. An equivalent script is:

    PRAGMA foreign_keys = on;

    CREATE TABLE c1(b);
    INSERT INTO c1 VALUES(123);

    ATTACH '' AS aux;
    CREATE TABLE aux.p1(a INTEGER PRIMARY KEY);
    CREATE TABLE aux.c1(b REFERENCES p1(a) ON DELETE RESTRICT);
    INSERT INTO aux.p1 VALUES(123);

    DELETE FROM p1 WHERE a=123;

It's the "ON DELETE RESTRICT" that is causing the problem.

Dan.

FYI: Dan fixed this bug a couple hours ago.

Thanks for your persistence in getting this narrowed down to a test case. A reduced form of it is in the test suite now.

Great! And no problem, thanks for being so responsive and Dan for addressing the issue. One benefit of identifying it will probably be capturing it as a known issue for earlier versions. We saw this come up in a database migration use case, not sure if others might see that as well.