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 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

Reply via email to