> Summary: how can I use foreign keys across database boundaries? Is it at
> all possible?
No. It's logically incorrect action, so it's impossible. If you want
consistency of your tables to be automatically checked by database
engine you need to allow that engine to see those tables at all times.
For SQLite it means that you need to keep those tables in one
database. If you insist on keeping tables in different databases then
your application should check consistency itself because it's the only
one knowing how to keep track of different files.
Pavel
On Wed, Aug 18, 2010 at 4:26 PM, Oliver Schneider <oli...@f-prot.com> wrote:
> Hello,
>
> when trying to use foreign keys I'm running into a problem. It could be
> that I hit some general limitation, but then again the error that I'm
> seeing could also be issued if the database with the table of the
> referenced foreign key is not attached.
>
> Here's what I'm trying to do.
>
> I have some meta-data that rarely changes (e.g. text strings with
> accompanying IDs) but is rather big. Suppose we have:
>
> -------------------------
> pragma foreign_keys = on;
>
> create table texts ( id integer primary key autoincrement, str text
> unique not null on conflict fail );
>
> /* With the following data */
> insert into texts(str) values('foo');
> insert into texts(str) values('bar');
> insert into texts(str) values('baz');
> -------------------------
>
> I'll call this table {texts} from now on. It's contained in file1. Now
> comes some more variable data where I would prefer to use one database
> per "data set", so I create another database contained in file2 (which
> becomes {main}):
>
> -------------------------
> pragma foreign_keys = on;
>
> attach database file1 as text;
>
> /* This fails right away:
>
> create table main.result ( id integer primary key autoincrement, strid
> integer, details text default null, foreign key(strid) references
> text.texts(id) );
>
> ... so I use: */
>
> create table main.result ( id integer primary key autoincrement, strid
> integer, details text default null, foreign key(strid) references
> texts(id) );
>
> /* Which succeeds for the moment, until ... */
>
> insert into result(strid,details) values (1,'some foo value');
> -------------------------
>
> This last line gives me "Error: no such table: main.texts", which, I
> suppose, is due to the constraint check for the foreign key "strid".
>
> The given error message obviously makes sense. However, since the
> constraint check is done upon insertion referencing an attached database
> shouldn't really pose a problem, right? At worst - e.g. if I hadn't
> attached "file1 as text" - I would get the that error with a slight
> variation: "Error: no such table: text.texts" ...
>
> Summary: how can I use foreign keys across database boundaries? Is it at
> all possible?
>
>
> Thanks,
>
> // Oliver
>
> PS: I'm using SQLite version 3.6.22
> PPS: Tried to send it with PGP/MIME signature, but it didn't get through
> according to the list archive, so sending without signature.
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
