Re: [sqlite] Foreign key child index question

[David Raymond]

For the specific case below I was just banging something out to test if it 
worked, so didn't adhere to the best design practices. All your comments are 
good ones for actual designs though.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Dominique Devienne
Sent: Tuesday, December 13, 2016 2:50 PM
To: SQLite mailing list
Subject: Re: [sqlite] Foreign key child index question

On Tue, Dec 13, 2016 at 8:07 PM, David Raymond <david.raym...@tomtom.com>
wrote:

> sqlite> create table parentTable
>...> (
>...>   id integer primary key,
>...>   a int not null,
>...>   b int not null,
>...>   otherStuff,
>...>   unique (a, b)
>...> );
>
> sqlite> create table childTable
>...> (
>...>   id integer primary key,
>...>   a int, --nullable
>...>   b int, --nullable
>...>   otherStuff,
>...>   foreign key (a, b) references parentTable (a, b)
>...> );
>

But why would you do that?

If you have unique(a, b), that implies it's your natural key (NK),
and id is "just" the surrogate key (SK). And the whole point of
a surrogate key is to have lighter-weight (to store and index)
and faster to match against foreigns keys (FKs). Otherwise you
might as well set (a, b) as your PK, and not have an SK at all. --DD
___
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] Foreign key child index question

[Dominique Devienne]

On Tue, Dec 13, 2016 at 8:07 PM, David Raymond 
wrote:

> sqlite> create table parentTable
>...> (
>...>   id integer primary key,
>...>   a int not null,
>...>   b int not null,
>...>   otherStuff,
>...>   unique (a, b)
>...> );
>
> sqlite> create table childTable
>...> (
>...>   id integer primary key,
>...>   a int, --nullable
>...>   b int, --nullable
>...>   otherStuff,
>...>   foreign key (a, b) references parentTable (a, b)
>...> );
>

But why would you do that?

If you have unique(a, b), that implies it's your natural key (NK),
and id is "just" the surrogate key (SK). And the whole point of
a surrogate key is to have lighter-weight (to store and index)
and faster to match against foreigns keys (FKs). Otherwise you
might as well set (a, b) as your PK, and not have an SK at all. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[David Raymond]

Hurray for intelligent logic! Also seems to work for composite foreign keys. 
Thank you all for checking more into this. And thank you to the developers for 
already having this in there. I think this would be worthy of having a couple 
sentences written up for the "Foreign Key Support" page.
http://www.sqlite.org/foreignkeys.html#fk_indexes


SQLite version 3.15.2 2016-11-28 19:13:37
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> pragma foreign_keys = on;

sqlite> create table parentTable
   ...> (
   ...>   id integer primary key,
   ...>   a int not null,
   ...>   b int not null,
   ...>   otherStuff,
   ...>   unique (a, b)
   ...> );

sqlite> create table childTable
   ...> (
   ...>   id integer primary key,
   ...>   a int, --nullable
   ...>   b int, --nullable
   ...>   otherStuff,
   ...>   foreign key (a, b) references parentTable (a, b)
   ...> );

sqlite> explain query plan delete from parentTable;
selectid|order|from|detail
0|0|0|SCAN TABLE parentTable
0|0|0|SCAN TABLE childTable

sqlite> create index fullIndex on childTable (a, b);

sqlite> explain query plan delete from parentTable;
selectid|order|from|detail
0|0|0|SCAN TABLE parentTable
0|0|0|SEARCH TABLE childTable USING COVERING INDEX fullIndex (a=? AND b=?)

sqlite> create index halfIndex on childTable (a, b) where b is not null;

sqlite> explain query plan delete from parentTable;
selectid|order|from|detail
0|0|0|SCAN TABLE parentTable
0|0|0|SEARCH TABLE childTable USING COVERING INDEX halfIndex (a=? AND b=?)

sqlite> create index partialIndex on childTable (a, b) where a is not null and 
b is not null;

sqlite> explain query plan delete from parentTable;
selectid|order|from|detail
0|0|0|SCAN TABLE parentTable
0|0|0|SEARCH TABLE childTable USING COVERING INDEX partialIndex (a=? AND b=?)


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Dan Kennedy
Sent: Tuesday, December 13, 2016 12:05 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Foreign key child index question

On 12/13/2016 11:02 PM, Paul Egli wrote:
> On Tue, Dec 13, 2016 at 9:56 AM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>> On 13 Dec 2016, at 3:53pm, Paul Egli <e...@sourcegear.com> wrote:
>>
>>> Well if i am missing something, i still cannot see it.
>>>
>>> Based on these quotes in the docs, i assume that a NULL in the child
>> table
>>> means that it does not have a parent.
>> You are correct.  I missed that.
>>
>> So yes, the original poster was correct, and using an index which left out
>> the NULL key values would lead to the right results.  Should be possible to
>> use that for an optimization case.  I suppose it might lead to quite a
>> saving in filespace for some tables.
>>
>
> Great. Just to clarify, SQLite will already use "alternateChildIndex1" from
> the example? Or just that it would be possible as an enhancement request?

Indexes qualified with "WHERE col IS NOT NULL" on child tables can be 
used for FK processing. Simon is right that you can use EXPLAIN QUERY 
PLAN to see this. Without the index, EQP says "SCAN TABLE childTable". 
After it is created, "SEARCH TABLE childTable USING ... INDEX ...".

   sqlite> create table parentTable( id integer primary key, restOfFields);
   sqlite> create table childTable (
  ...>   id integer primary key,
  ...>   fkField int references parentTable, --usually null
  ...>   restOfFields --includes many more foreign keys
  ...> );
   sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
   0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
   sqlite> PRAGMA foreign_keys = 1;
   sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
   0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
   0|0|0|SCAN TABLE childTable
   sqlite> create index alternateChildIndex1 on childTable (fkField) 
where fkField is not null;
   sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
   0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
   0|0|0|SEARCH TABLE childTable USING COVERING INDEX 
alternateChildIndex1 (fkField=?)

Dan.

___
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] Foreign key child index question

[Dan Kennedy]

On 12/13/2016 11:02 PM, Paul Egli wrote:

On Tue, Dec 13, 2016 at 9:56 AM, Simon Slavin  wrote:


On 13 Dec 2016, at 3:53pm, Paul Egli  wrote:


Well if i am missing something, i still cannot see it.

Based on these quotes in the docs, i assume that a NULL in the child

table

means that it does not have a parent.

You are correct.  I missed that.

So yes, the original poster was correct, and using an index which left out
the NULL key values would lead to the right results.  Should be possible to
use that for an optimization case.  I suppose it might lead to quite a
saving in filespace for some tables.



Great. Just to clarify, SQLite will already use "alternateChildIndex1" from
the example? Or just that it would be possible as an enhancement request?


Indexes qualified with "WHERE col IS NOT NULL" on child tables can be 
used for FK processing. Simon is right that you can use EXPLAIN QUERY 
PLAN to see this. Without the index, EQP says "SCAN TABLE childTable". 
After it is created, "SEARCH TABLE childTable USING ... INDEX ...".


  sqlite> create table parentTable( id integer primary key, restOfFields);
  sqlite> create table childTable (
 ...>   id integer primary key,
 ...>   fkField int references parentTable, --usually null
 ...>   restOfFields --includes many more foreign keys
 ...> );
  sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
  0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
  sqlite> PRAGMA foreign_keys = 1;
  sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
  0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
  0|0|0|SCAN TABLE childTable
  sqlite> create index alternateChildIndex1 on childTable (fkField) 
where fkField is not null;

  sqlite> EXPLAIN QUERY PLAN DELETE FROM parentTable WHERE id=?;
  0|0|0|SEARCH TABLE parentTable USING INTEGER PRIMARY KEY (rowid=?)
  0|0|0|SEARCH TABLE childTable USING COVERING INDEX 
alternateChildIndex1 (fkField=?)


Dan.

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

Re: [sqlite] Foreign key child index question

[Simon Slavin]

On 13 Dec 2016, at 4:02pm, Paul Egli  wrote:

> Great. Just to clarify, SQLite will already use "alternateChildIndex1" from
> the example? Or just that it would be possible as an enhancement request?

I don’t know the answer, but you can find out exactly what SQLite has chosen to 
do for your case.  Prefix your SELECT command with EXPLAIN QUERY PLAN:



It’s probably easiest to do this using the SQLite command-line tool.  The plan 
you get back will describe which indexes SQLite has decided to use, or whether 
it couldn’t find a useful index and had to scan the table instead.

Note that using ANALYZE after you have created your indexes can cause a 
dramatic improvement in the options SQLite picks when constructing query plans. 
 If you are in a position of worrying about optimization it’s useful to 
understand ANALYZE and EXPLAIN QUERY PLAN.

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

Re: [sqlite] Foreign key child index question

[Paul Egli]

On Tue, Dec 13, 2016 at 9:56 AM, Simon Slavin  wrote:

>
> On 13 Dec 2016, at 3:53pm, Paul Egli  wrote:
>
> > Well if i am missing something, i still cannot see it.
> >
> > Based on these quotes in the docs, i assume that a NULL in the child
> table
> > means that it does not have a parent.
>
> You are correct.  I missed that.
>
> So yes, the original poster was correct, and using an index which left out
> the NULL key values would lead to the right results.  Should be possible to
> use that for an optimization case.  I suppose it might lead to quite a
> saving in filespace for some tables.
>


Great. Just to clarify, SQLite will already use "alternateChildIndex1" from
the example? Or just that it would be possible as an enhancement request?

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

Re: [sqlite] Foreign key child index question

[Simon Slavin]

On 13 Dec 2016, at 3:53pm, Paul Egli  wrote:

> Well if i am missing something, i still cannot see it.
> 
> Based on these quotes in the docs, i assume that a NULL in the child table
> means that it does not have a parent.

You are correct.  I missed that.

So yes, the original poster was correct, and using an index which left out the 
NULL key values would lead to the right results.  Should be possible to use 
that for an optimization case.  I suppose it might lead to quite a saving in 
filespace for some tables.

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

Re: [sqlite] Foreign key child index question

[Paul Egli]

On Tue, Dec 13, 2016 at 9:41 AM, Simon Slavin  wrote:

>
> On 13 Dec 2016, at 3:20pm, Paul Egli  wrote:
>
> > Why must SQLite find rows where the value is NULL?
>
> Because the related fields in the offspring row might have NULL in them,
> and SQLite needs to know how to find the parent row for that row.
>
>
Well if i am missing something, i still cannot see it.

Based on these quotes in the docs, i assume that a NULL in the child table
means that it does not have a parent.

"All foreign key constraints in SQLite are handled as if MATCH SIMPLE were
specified."

"If "MATCH SIMPLE" is specified, then a child key is not required to
correspond to any row of the parent table if one or more of the child key
values are NULL."

Therefore SQLite would *not *need to find a parent for that row.

So i guess i am still hoping that, if possible, SQLite could be enhanced as
David's question was getting at. :-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[Simon Slavin]

On 13 Dec 2016, at 3:20pm, Paul Egli  wrote:

> Why must SQLite find rows where the value is NULL?

Because the related fields in the offspring row might have NULL in them, and 
SQLite needs to know how to find the parent row for that row.

Mind you, if the relating key field(s) are defined as NOT NULL, you have a good 
suggestion.

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

Re: [sqlite] Foreign key child index question

[Paul Egli]

On Wed, Dec 7, 2016 at 3:11 PM, Simon Slavin  wrote:

>
> On 7 Dec 2016, at 8:40pm, David Raymond  wrote:
>
> > Question on making indexes for the child fields of foreign keys. I have
> a child table with a number of foreign keys on fields which the majority of
> the time are null. I've currently got indexes on the child fields for the
> purposes of speeding up the foreign key checks, but what I'm wondering is
> if I used a conditional index which has "where fkField is not null", will
> that index be usable by the internal foreign key checker?
>
> Sorry, but the answer is no.  SQLite needs to be able to find the rows
> where the key-value is NULL.  It can’t do that from an index which doesn’t
> include those rows.
>
>
Why must SQLite find rows where the value is NULL? To me, this sounded like
a good enhancement request if it's not already the case. Am i missing
something?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key child index question

[Simon Slavin]

On 7 Dec 2016, at 8:40pm, David Raymond  wrote:

> Question on making indexes for the child fields of foreign keys. I have a 
> child table with a number of foreign keys on fields which the majority of the 
> time are null. I've currently got indexes on the child fields for the 
> purposes of speeding up the foreign key checks, but what I'm wondering is if 
> I used a conditional index which has "where fkField is not null", will that 
> index be usable by the internal foreign key checker?

Sorry, but the answer is no.  SQLite needs to be able to find the rows where 
the key-value is NULL.  It can’t do that from an index which doesn’t include 
those rows.

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

[sqlite] Foreign key child index question

[David Raymond]

http://www.sqlite.org/foreignkeys.html#fk_indexes

Question on making indexes for the child fields of foreign keys. I have a child 
table with a number of foreign keys on fields which the majority of the time 
are null. I've currently got indexes on the child fields for the purposes of 
speeding up the foreign key checks, but what I'm wondering is if I used a 
conditional index which has "where fkField is not null", will that index be 
usable by the internal foreign key checker? I'd like to avoid having 10 indexes 
each of which is 90% full of nulls for example.

create table parentTable
(
  id integer primary key,
  restOfFields
);

create table childTable
(
  id integer primary key,
  fkField int references parentTable, --usually null
  restOfFields --includes many more foreign keys
);

create index childIndex1 on childTable (fkField);--normal fk child index

create index alternateChildIndex1 on childTable (fkField) where fkField is not 
null;
--will this be used, and help cut down on wasted space?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users