On Wed, Aug 18, 2010 at 1:39 PM, Pavel Ivanov <paiva...@gmail.com> wrote:
>> 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.
>
So attaching the file1 before creating the table in file2
is going to fail? (Then sqlite would know about the {texts} table)
Stephan
>
> 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
>
--
Stephan Wehner
-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
