Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
Hi Ryan, Okay, thanks for the clarification! Your explanation makes sense! Best, Manuel On Mon, May 13, 2019 at 8:25 PM R Smith 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
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 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
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
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
Okay, thanks for the clarification! Best, Manuel On Mon, May 13, 2019 at 1:38 AM J. King wrote: > On May 12, 2019 7:33:20 p.m. EDT, Manuel Rigger > 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
On May 12, 2019 7:33:20 p.m. EDT, Manuel Rigger 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
Thanks for your quick response! On Mon, May 13, 2019 at 1:17 AM Simon Slavin wrote: > On 12 May 2019, at 11:56pm, Manuel Rigger 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mixing main and temp databases in foreign keys is not supported
On 12 May 2019, at 11:56pm, Manuel Rigger 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Mixing main and temp databases in foreign keys is not supported
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
