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