On 2019/05/13 12:56 AM, Manuel Rigger wrote:
Hi everyone,
It seems that a table created in the temp database cannot have a parent
table that is created in the main database and vice versa:
PRAGMA foreign_keys=true;
CREATE TABLE t0 (c0 PRIMARY KEY);
CREATE TEMP TABLE t1 (c0, FOREIGN KEY(c0) REFERENCES t0(c0));
INSERT INTO t1(c0) VALUES (1); -- no such table: temp.t0
Is this intended? It somehow seems so, since the parent table can also not
be prefixed by "temp." when declaring the foreign key in the above example.
However, I did not find a note in the documentation that this is not
supported. Would it make sense to describe this in the limitations at
https://www.sqlite.org/foreignkeys.html?
While the "why" of it has been handled by others, the question of "need
it be documented" still remains, to which I can comment: Documenting the
fact that FK relationships cannot be maintained across different
database entities, trespasses on two documentation conventions:
A - Do not document the obvious (i.e. no point documenting the fact that
your car needs all 4 wheels for correct operation)[1], and
B - Do not document the negative (i.e. Say what you CAN do [short list]
rather than what you CAN'T [infinite list]) - unless it is an expected
yet omitted behaviour (such as things that can normally be done in other
databases, but not in this one)[2].
Cheers,
Ryan
[1] - Rant: There seems to be a senseless move to document exactly such
silliness for fear of litigation these days, making modern documentation
more and more a self-indemnification checklist by the manufacturer
rather than a helpful description of the operation and functionality of
the item. It used to be that the number 1 source of information about
the vehicle/device you purchased was its manual (written by Engineers),
now it's more TLDR; (edited by PR/Legal people) and for real information
you simply pray there is a youtube video on the issue by another enthusiast.
[2] - I know MSSQL "allows" temp tables to have foreign keys specified,
but it doesn't enforce the constraint, so it's nothing more than no-op
syntactic sugar, perhaps in an effort to not have it choke on
copy-pasted schemata.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
