Re: [sqlite] Question concerning foreign keys across databases

[Simon Slavin]

On 18 Aug 2010, at 9:54pm, Stephan Wehner wrote:

> So attaching the file1 before creating the table in file2
> is going to fail? (Then sqlite would know about the {texts} table)

The ATTACH command is a bit of a misnomer: it doesn't do anything to the 
database files.  It affects a particular connection that you opened with 
sqlite3_open(), and allows your application to address both files on disk via 
the same connection.  It does not attach the two database files for any later 
connections to them, and the two files still don't know anything about 
one-another.

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

Re: [sqlite] Question concerning foreign keys across databases

[Oliver Schneider]

Hi Pavel,

I think I guess that I understand what your point is.

On 2010-08-18 21:18, Pavel Ivanov wrote:
> This is exactly the reason why it's not logical action: SQLite will
> check constraint only in those places where it knows that something is
> changed and constraint can be violated. And it shouldn't re-check it
> in million other places where nothing seems to be changed...
> To catch up with reason of "why" consider these scenarios:
> 1) You connected to main, attached texts, made foreign constraint and
> inserted some records. Then you created other connection to texts
> directly and deleted all referenced records. How should SQLite know
> that they cannot be deleted?
> 2) You made another connection to main and connected another database
> as "texts". How foreign keys should be enforced in this situation?
I see, so the guarantees made by constraints are somehow just don't fit
with the feature of using more than one database at a time.


Thanks for pointing it out,

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

Re: [sqlite] Question concerning foreign keys across databases

[Pavel Ivanov]

> In either case the insertion would
> fail, since it can only be checked when it happens and it probably will
> not be checked again afterwards.

This is exactly the reason why it's not logical action: SQLite will
check constraint only in those places where it knows that something is
changed and constraint can be violated. And it shouldn't re-check it
in million other places where nothing seems to be changed...
To catch up with reason of "why" consider these scenarios:
1) You connected to main, attached texts, made foreign constraint and
inserted some records. Then you created other connection to texts
directly and deleted all referenced records. How should SQLite know
that they cannot be deleted?
2) You made another connection to main and connected another database
as "texts". How foreign keys should be enforced in this situation?


Pavel

On Wed, Aug 18, 2010 at 4:57 PM, Oliver Schneider  wrote:
> Hello Pavel,
>
> thanks for your reply.
>
> On 2010-08-18 20:39, Pavel Ivanov wrote:
>>> Summary: how can I use foreign keys across database boundaries? Is it at
>>> all possible?
>>
>> No. It's logically incorrect action, so it's impossible. If you want
>> consistency of your tables to be automatically checked by database
>> engine you need to allow that engine to see those tables at all times.
> Sorry to disagree, concerning the logic. But I can't see why an error
> about texts.text instead of main.text would make such a difference. Why
> is one more logical than the other? In either case the insertion would
> fail, since it can only be checked when it happens and it probably will
> not be checked again afterwards.
>
> If SQLite supports attaching multiple databases it would be natural to
> support foreign tables across them.
>
> The only possible catch I see is if the references are two-way (or
> more). Then it could really get messy, although the principle of failing
> if the table does not exist would still apply. I guess I'll have to
> dedicate a night or two to read over the code to get an idea about the
> "why".
>
>> For SQLite it means that you need to keep those tables in one
>> database. If you insist on keeping tables in different databases then
>> your application should check consistency itself because it's the only
>> one knowing how to keep track of different files.
> I guess what I'll simply dump the data from the "static" DB (which will
> only be modified manually) and import it right before creating the new
> tables.
>
>
> // Oliver
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question concerning foreign keys across databases

[Oliver Schneider]

Hello Pavel,

thanks for your reply.

On 2010-08-18 20:39, Pavel Ivanov wrote:
>> Summary: how can I use foreign keys across database boundaries? Is it at
>> all possible?
> 
> No. It's logically incorrect action, so it's impossible. If you want
> consistency of your tables to be automatically checked by database
> engine you need to allow that engine to see those tables at all times.
Sorry to disagree, concerning the logic. But I can't see why an error
about texts.text instead of main.text would make such a difference. Why
is one more logical than the other? In either case the insertion would
fail, since it can only be checked when it happens and it probably will
not be checked again afterwards.

If SQLite supports attaching multiple databases it would be natural to
support foreign tables across them.

The only possible catch I see is if the references are two-way (or
more). Then it could really get messy, although the principle of failing
if the table does not exist would still apply. I guess I'll have to
dedicate a night or two to read over the code to get an idea about the
"why".

> For SQLite it means that you need to keep those tables in one
> database. If you insist on keeping tables in different databases then
> your application should check consistency itself because it's the only
> one knowing how to keep track of different files.
I guess what I'll simply dump the data from the "static" DB (which will
only be modified manually) and import it right before creating the new
tables.


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

Re: [sqlite] Question concerning foreign keys across databases

[Stephan Wehner]

On Wed, Aug 18, 2010 at 1:39 PM, Pavel Ivanov  wrote:
>> Summary: how can I use foreign keys across database boundaries? Is it at
>> all possible?
>
> No. It's logically incorrect action, so it's impossible. If you want
> consistency of your tables to be automatically checked by database
> engine you need to allow that engine to see those tables at all times.
> For SQLite it means that you need to keep those tables in one
> database. If you insist on keeping tables in different databases then
> your application should check consistency itself because it's the only
> one knowing how to keep track of different files.
>

So attaching the file1 before creating the table in file2
is going to fail? (Then sqlite would know about the {texts} table)

Stephan

>
> Pavel
>
> On Wed, Aug 18, 2010 at 4:26 PM, Oliver Schneider  wrote:
>> Hello,
>>
>> when trying to use foreign keys I'm running into a problem. It could be
>> that I hit some general limitation, but then again the error that I'm
>> seeing could also be issued if the database with the table of the
>> referenced foreign key is not attached.
>>
>> Here's what I'm trying to do.
>>
>> I have some meta-data that rarely changes (e.g. text strings with
>> accompanying IDs) but is rather big. Suppose we have:
>>
>> -
>> pragma foreign_keys = on;
>>
>> create table texts ( id integer primary key autoincrement, str text
>> unique not null on conflict fail );
>>
>> /* With the following data */
>> insert into texts(str) values('foo');
>> insert into texts(str) values('bar');
>> insert into texts(str) values('baz');
>> -
>>
>> I'll call this table {texts} from now on. It's contained in file1. Now
>> comes some more variable data where I would prefer to use one database
>> per "data set", so I create another database contained in file2 (which
>> becomes {main}):
>>
>> -
>> pragma foreign_keys = on;
>>
>> attach database file1 as text;
>>
>> /* This fails right away:
>>
>> create table main.result ( id integer primary key autoincrement, strid
>> integer, details text default null, foreign key(strid) references
>> text.texts(id) );
>>
>> ... so I use: */
>>
>> create table main.result ( id integer primary key autoincrement, strid
>> integer, details text default null, foreign key(strid) references
>> texts(id) );
>>
>> /* Which succeeds for the moment, until ... */
>>
>> insert into result(strid,details) values (1,'some foo value');
>> -
>>
>> This last line gives me "Error: no such table: main.texts", which, I
>> suppose, is due to the constraint check for the foreign key "strid".
>>
>> The given error message obviously makes sense. However, since the
>> constraint check is done upon insertion referencing an attached database
>> shouldn't really pose a problem, right? At worst - e.g. if I hadn't
>> attached "file1 as text" - I would get the that error with a slight
>> variation: "Error: no such table: text.texts" ...
>>
>> Summary: how can I use foreign keys across database boundaries? Is it at
>> all possible?
>>
>>
>> Thanks,
>>
>> // Oliver
>>
>> PS: I'm using SQLite version 3.6.22
>> PPS: Tried to send it with PGP/MIME signature, but it didn't get through
>> according to the list archive, so sending without signature.
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Stephan Wehner

-> http://stephan.sugarmotor.org (blog and homepage)
-> http://loggingit.com
-> http://www.thrackle.org
-> http://www.buckmaster.ca
-> http://www.trafficlife.com
-> http://stephansmap.org -- http://blog.stephansmap.org
-> http://twitter.com/stephanwehner / @stephanwehner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question concerning foreign keys across databases

[Pavel Ivanov]

> Summary: how can I use foreign keys across database boundaries? Is it at
> all possible?

No. It's logically incorrect action, so it's impossible. If you want
consistency of your tables to be automatically checked by database
engine you need to allow that engine to see those tables at all times.
For SQLite it means that you need to keep those tables in one
database. If you insist on keeping tables in different databases then
your application should check consistency itself because it's the only
one knowing how to keep track of different files.


Pavel

On Wed, Aug 18, 2010 at 4:26 PM, Oliver Schneider  wrote:
> Hello,
>
> when trying to use foreign keys I'm running into a problem. It could be
> that I hit some general limitation, but then again the error that I'm
> seeing could also be issued if the database with the table of the
> referenced foreign key is not attached.
>
> Here's what I'm trying to do.
>
> I have some meta-data that rarely changes (e.g. text strings with
> accompanying IDs) but is rather big. Suppose we have:
>
> -
> pragma foreign_keys = on;
>
> create table texts ( id integer primary key autoincrement, str text
> unique not null on conflict fail );
>
> /* With the following data */
> insert into texts(str) values('foo');
> insert into texts(str) values('bar');
> insert into texts(str) values('baz');
> -
>
> I'll call this table {texts} from now on. It's contained in file1. Now
> comes some more variable data where I would prefer to use one database
> per "data set", so I create another database contained in file2 (which
> becomes {main}):
>
> -
> pragma foreign_keys = on;
>
> attach database file1 as text;
>
> /* This fails right away:
>
> create table main.result ( id integer primary key autoincrement, strid
> integer, details text default null, foreign key(strid) references
> text.texts(id) );
>
> ... so I use: */
>
> create table main.result ( id integer primary key autoincrement, strid
> integer, details text default null, foreign key(strid) references
> texts(id) );
>
> /* Which succeeds for the moment, until ... */
>
> insert into result(strid,details) values (1,'some foo value');
> -
>
> This last line gives me "Error: no such table: main.texts", which, I
> suppose, is due to the constraint check for the foreign key "strid".
>
> The given error message obviously makes sense. However, since the
> constraint check is done upon insertion referencing an attached database
> shouldn't really pose a problem, right? At worst - e.g. if I hadn't
> attached "file1 as text" - I would get the that error with a slight
> variation: "Error: no such table: text.texts" ...
>
> Summary: how can I use foreign keys across database boundaries? Is it at
> all possible?
>
>
> Thanks,
>
> // Oliver
>
> PS: I'm using SQLite version 3.6.22
> PPS: Tried to send it with PGP/MIME signature, but it didn't get through
> according to the list archive, so sending without signature.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question concerning foreign keys across databases

[Oliver Schneider]

Hello,

when trying to use foreign keys I'm running into a problem. It could be
that I hit some general limitation, but then again the error that I'm
seeing could also be issued if the database with the table of the
referenced foreign key is not attached.

Here's what I'm trying to do.

I have some meta-data that rarely changes (e.g. text strings with
accompanying IDs) but is rather big. Suppose we have:

-
pragma foreign_keys = on;

create table texts ( id integer primary key autoincrement, str text
unique not null on conflict fail );

/* With the following data */
insert into texts(str) values('foo');
insert into texts(str) values('bar');
insert into texts(str) values('baz');
-

I'll call this table {texts} from now on. It's contained in file1. Now
comes some more variable data where I would prefer to use one database
per "data set", so I create another database contained in file2 (which
becomes {main}):

-
pragma foreign_keys = on;

attach database file1 as text;

/* This fails right away:

create table main.result ( id integer primary key autoincrement, strid
integer, details text default null, foreign key(strid) references
text.texts(id) );

... so I use: */

create table main.result ( id integer primary key autoincrement, strid
integer, details text default null, foreign key(strid) references
texts(id) );

/* Which succeeds for the moment, until ... */

insert into result(strid,details) values (1,'some foo value');
-

This last line gives me "Error: no such table: main.texts", which, I
suppose, is due to the constraint check for the foreign key "strid".

The given error message obviously makes sense. However, since the
constraint check is done upon insertion referencing an attached database
shouldn't really pose a problem, right? At worst - e.g. if I hadn't
attached "file1 as text" - I would get the that error with a slight
variation: "Error: no such table: text.texts" ...

Summary: how can I use foreign keys across database boundaries? Is it at
all possible?


Thanks,

// Oliver

PS: I'm using SQLite version 3.6.22
PPS: Tried to send it with PGP/MIME signature, but it didn't get through
according to the list archive, so sending without signature.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users