> In either case the insertion would > fail, since it can only be checked when it happens and it probably will > not be checked again afterwards.
This is exactly the reason why it's not logical action: SQLite will check constraint only in those places where it knows that something is changed and constraint can be violated. And it shouldn't re-check it in million other places where nothing seems to be changed... To catch up with reason of "why" consider these scenarios: 1) You connected to main, attached texts, made foreign constraint and inserted some records. Then you created other connection to texts directly and deleted all referenced records. How should SQLite know that they cannot be deleted? 2) You made another connection to main and connected another database as "texts". How foreign keys should be enforced in this situation? Pavel On Wed, Aug 18, 2010 at 4:57 PM, Oliver Schneider <oli...@f-prot.com> wrote: > Hello Pavel, > > thanks for your reply. > > On 2010-08-18 20:39, Pavel Ivanov 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. > Sorry to disagree, concerning the logic. But I can't see why an error > about texts.text instead of main.text would make such a difference. Why > is one more logical than the other? In either case the insertion would > fail, since it can only be checked when it happens and it probably will > not be checked again afterwards. > > If SQLite supports attaching multiple databases it would be natural to > support foreign tables across them. > > The only possible catch I see is if the references are two-way (or > more). Then it could really get messy, although the principle of failing > if the table does not exist would still apply. I guess I'll have to > dedicate a night or two to read over the code to get an idea about the > "why". > >> 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. > I guess what I'll simply dump the data from the "static" DB (which will > only be modified manually) and import it right before creating the new > tables. > > > // Oliver > _______________________________________________ > 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
