On 11/7/16, Smith, Randall <rsm...@qti.qualcomm.com> wrote: > It's quite common to create some kind of large database table or > constellation of tables that will be used for intermediate work product, and > which will be deleted when an operation, or the database session, is > complete. This kind of thing is an obvious candidate for TEMPORARY tables > in SQLite, not only because they will be reliably cleaned up, but because > they can be located in RAM for faster operation. > > However, my understanding is that TEMPORARY tables are effectively in their > own database and (thus?) cannot have foreign key relationships to other > databases (in this case, the database holding the persistent data). This > seems unfortunate since it prevents using TEMPORARY tables if you need or > want the consistency checking provided by foreign keys, and also seems > unnecessary since foreign key relationships pointing from a TEMP table to a > persistent table will never outlive the session, so the argument against > allowing against foreign keys between databases doesn't (I claim) really > apply. > > Am I getting the situation right? Is there any way to allow foreign keys > from TEMP tables to persistent tables, or can this be considered a feature > request?
Suppose there are two different processes talking to the database, process A and process B. The database has some table "xyz" with primary key "id". Process A creates a temporary table that REFERENCES the "xyz" table and inserts some content. But then process B (who has no way of seeing the temp table in A or even of knowing that the temp table exists) does "DELETE FROM xyz". This breaks the FK constraints in process A. But process B has no way of knowing that. And so there is is no way to enforce an FK constraint between a TEMP table and ordinary table. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
