That is because you do not have an index on the tableB child key of the 
relation (fk).  This is required.  see the lint command in a command line shell 
near you.

You do realize that a LEFT JOIN b is syntactic sugar for a LEFT OUTER JOIN b 
which means, in English, include all the rows of a whether or not there are (1 
or more) matches in table b?  Therefore you require indexes on the lookup 
columns (unless your tables are very paltry == as in contain no rows). 

Multiple on clauses can be specified as in:

create table tableB fk, datab, foreign key (fk) references tableA (pk) on 
delete cascade on update cascade);

You can create an index on a determinitic or slow-change function.

The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>] On Behalf Of J Decker
>Sent: Monday, 5 February, 2018 18:24
>To: General Discussion of SQLite Database
>Subject: [sqlite] Auto Index Warnings; key on deterministic functions
>I have a couple tables like...
>create table tableA ( pk PRIMARY KEY, dataA )
>create table tableB ( fk, dataB, FOREIGN KEY (fk) REFERENCES
>tableA(pk) ON
>if the table was also ON UPDATE CASCADE could it slave to the same
>index as
>primary key?
>doing a LEFT JOIN on the tables  I get a warning message about
>index is being generated...
>Separately, can a key be added to a table that is a determinstic
>alter tableB add column dFuncB char default dFunc(dataB)
>create INDEX dFuncIndex on tableB ( dFuncB )
>where dFunc is a deterministic function... oh wait, it can just be a
>function... but it can't reference a column value as the source of
>data... the expression part needs to be constant.
>I suppose I can add triggers to the table to update the default
>but then that function is non-constant... so how does that index
>Kind of a X-Y problem, that is what I thought I might like to do is
>have a key into a table that's partial, because not all entries in
>table will be referenced by that key... (although since it is
>could just be filled anyway).   But then if a could be made, the
>wouldn't actually have to exist in the table, since I really never
>that value, but just need to lookup by the value...
>create INDEX dFuncIndex on tableB ( (dFunc(dataB)) )
>I CAN do
>select * from tableB where dFunc(dataB) === 'some value'
>but that's not indexed at all.
>sqlite-users mailing list

sqlite-users mailing list

Reply via email to