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
