Foreign key in main database referencing attached database

Question

Is there any way in SQLite3 to have a foreign key in the main database that references columns in an attached database (or vice-versa?)

I hope to share the attached (read-only) database between multiple processes, each of which has its own (read/write) main database.

I create the parent table like this (in database 'ParentDB'):

create table Parent (id integer primary key);

Now I try this in the main database:

attach 'parent.sqlite3' as ParentDB;
create table Child (id integer not null references Parent (id),
                    constraint PK_Child primary key (id));
insert into ParentDB.Parent (id) values (42);

When I try it it creates the foreign key with no errors. Now I try to insert a row into the child table:

insert into Child (id) values (42);

And I get this error:

Error: no such table: main.Parent

So it seems it always assumes the parent and child tables belong to the same database.

Also the foreign key syntax does not allow you to specify which database the parent table belongs to.

Is there a workaround?

---

This question is related, but here both the parent and child tables are in the same attached database, whereas I have them in separate databases.

Answer 1

SQLite's built-in foreign key constraints do not work across databases.

The only workaround would be to manually write check constraints and triggers that do the same checking.