Re: [sqlite] Foreign keys & TEMPORARY tables.

[Richard Hipp]

On 11/7/16, Smith, Randall  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

[sqlite] Foreign keys & TEMPORARY tables.

[Smith, Randall]

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?

Randall Smith
Senior Staff Engineer
Qualcomm, Inc.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users