Mixing main and temp databases in foreign keys is not supported

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Mixing main and temp databases in foreign keys is not supported

Manuel Rigger
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?

Best,
Manuel
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixing main and temp databases in foreign keys is not supported

Simon Slavin-3
On 12 May 2019, at 11:56pm, Manuel Rigger <[hidden email]> wrote:

> Is this intended?

Yes.  Because the temp database disappears when you close your connection. So you would open a database and find that either a parent or a child table had disappeared.  Which would mean the database was corrupt.

Actually it's not limited to temp.  All FOREIGN KEY relationships must be between two tables in the same database.  Because if they were in different databases you might load one database and not the other.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixing main and temp databases in foreign keys is not supported

Manuel Rigger
Thanks for your quick response!

On Mon, May 13, 2019 at 1:17 AM Simon Slavin <[hidden email]> wrote:

> On 12 May 2019, at 11:56pm, Manuel Rigger <[hidden email]> wrote:
>
> > Is this intended?
>
> Yes.  Because the temp database disappears when you close your connection.
> So you would open a database and find that either a parent or a child table
> had disappeared.  Which would mean the database was corrupt.
>

I would not consider the database to be corrupt though, since the behavior
for a missing parent table is documented. For example, it is supported to
drop a parent table that is still referenced.


>
> Actually it's not limited to temp.  All FOREIGN KEY relationships must be
> between two tables in the same database.  Because if they were in different
> databases you might load one database and not the other.
>

As far as I understood, the main and temp databases are always loaded and
cannot be detached. But, as you pointed out, I can understand that this
makes sense in the general case.

Best,
Manuel
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixing main and temp databases in foreign keys is not supported

J. King-3
On May 12, 2019 7:33:20 p.m. EDT, Manuel Rigger <[hidden email]> wrote:

>As far as I understood, the main and temp databases are always loaded
>and
>cannot be detached. But, as you pointed out, I can understand that this
>makes sense in the general case.

Note that the temp database is specific to a given connection while the main one is not. Creating foreign relations to the temp database would corrupt another connection's view of the same database
--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixing main and temp databases in foreign keys is not supported

Manuel Rigger
Okay, thanks for the clarification!

Best,
Manuel

On Mon, May 13, 2019 at 1:38 AM J. King <[hidden email]> wrote:

> On May 12, 2019 7:33:20 p.m. EDT, Manuel Rigger <[hidden email]>
> wrote:
>
> >As far as I understood, the main and temp databases are always loaded
> >and
> >cannot be detached. But, as you pointed out, I can understand that this
> >makes sense in the general case.
>
> Note that the temp database is specific to a given connection while the
> main one is not. Creating foreign relations to the temp database would
> corrupt another connection's view of the same database
> --
> J. King
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixing main and temp databases in foreign keys is not supported

R Smith-2
In reply to this post by Manuel Rigger
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixing main and temp databases in foreign keys is not supported

Manuel Rigger
Hi Ryan,

I hope my question did not offend you. I didn't expect that the answer to
this question would be considered to be that obvious.

Best,
Manuel

On Mon, May 13, 2019 at 9:57 AM R Smith <[hidden email]> wrote:

> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixing main and temp databases in foreign keys is not supported

R Smith-2
On 2019/05/13 11:42 AM, Manuel Rigger wrote:
> Hi Ryan,
>
> I hope my question did not offend you. I didn't expect that the answer to
> this question would be considered to be that obvious.

Goodness, I was not offended and apologies if my reply read in that way
- it was just an honest account - and - if I were to stoop so low as to
"take offense" at a question, I would never answer it publicly.

Please know that it is obvious to most Database people (er... what is a
good word...? shall we say "Afficionados"?), but that in no way means
that you *should* have known or that I (or most others here) would scoff
at anyone not knowing this. I once did not know this, and now to me it
was nothing but an opportunity to teach/relay that which I have been taught.

As to the question, let me see if I could entice your mind to see the
obviousness with us: Try to imagine how you would program a database
engine upon which constraints could be placed, the underlying methods or
values of which may persist in separate files/schemata/tables which may
all be transacted upon from alternate connections while they are not in
view, or not accessible to the engine itself, but the engine is still
expected to uphold the constraints.

I am hoping that after some thinking on the matter, either the
obviousness would materialize for you, or possibly you will come up with
a method that could change the face of RDBMS capabilities forever!

I'm hoping for the latter, but will take the former as a second prize. :)


Cheers!

Ryan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Mixing main and temp databases in foreign keys is not supported

Manuel Rigger
Hi Ryan,

Okay, thanks for the clarification! Your explanation makes sense!

Best,
Manuel

On Mon, May 13, 2019 at 8:25 PM R Smith <[hidden email]> wrote:

> On 2019/05/13 11:42 AM, Manuel Rigger wrote:
> > Hi Ryan,
> >
> > I hope my question did not offend you. I didn't expect that the answer to
> > this question would be considered to be that obvious.
>
> Goodness, I was not offended and apologies if my reply read in that way
> - it was just an honest account - and - if I were to stoop so low as to
> "take offense" at a question, I would never answer it publicly.
>
> Please know that it is obvious to most Database people (er... what is a
> good word...? shall we say "Afficionados"?), but that in no way means
> that you *should* have known or that I (or most others here) would scoff
> at anyone not knowing this. I once did not know this, and now to me it
> was nothing but an opportunity to teach/relay that which I have been
> taught.
>
> As to the question, let me see if I could entice your mind to see the
> obviousness with us: Try to imagine how you would program a database
> engine upon which constraints could be placed, the underlying methods or
> values of which may persist in separate files/schemata/tables which may
> all be transacted upon from alternate connections while they are not in
> view, or not accessible to the engine itself, but the engine is still
> expected to uphold the constraints.
>
> I am hoping that after some thinking on the matter, either the
> obviousness would materialize for you, or possibly you will come up with
> a method that could change the face of RDBMS capabilities forever!
>
> I'm hoping for the latter, but will take the former as a second prize. :)
>
>
> Cheers!
>
> Ryan
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users