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
