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- >[email protected]] 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 >DELETE CASCADE ) > >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 >automatic >index is being generated... > >------------------- >Separately, can a key be added to a table that is a determinstic >function? > >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 >its >data... the expression part needs to be constant. >I suppose I can add triggers to the table to update the default >value. > >but then that function is non-constant... so how does that index >work? > >------------ >Kind of a X-Y problem, that is what I thought I might like to do is >just >have a key into a table that's partial, because not all entries in >the >table will be referenced by that key... (although since it is >algorithmic, >could just be filled anyway). But then if a could be made, the >value >wouldn't actually have to exist in the table, since I really never >need >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 >[email protected] >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
