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