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
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.
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] 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
>DELETE CASCADE )
>if the table was also ON UPDATE CASCADE could it slave to the same
>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