On Tue, Feb 6, 2018 at 2:24 AM, J Decker <d3c...@gmail.com> wrote:
> 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?
An index relates a value to a rowid in the associated table (or a set of
rowids if non-unique index).
So the index behind the tableA.pk column maps pk-values to tableA rowids.
While the automatic index on tableB.fk relates to tableB rowids (and is
Sure the fk values are necessarily a subset of the pk values (*) for both
but obviously the rowids are completely different, since from different
So both indexes serve different purposes, and neither can "slave" for each
But perhaps I didn't understand your question correctly? Hopefully that's
PS: You want an "explicit" index on tableB.fk in any case, since all FKs
should be indexed in general.
Otherwise all your ON DELETE, ON UPDATE clauses would yields full scans
in the parent table (tableA here) need to be propagated to child tables.
(it needs to find the child rows
to delete or update, based on the affected tableA.pk values)
(*) if FKs are on, which they are not by default...
sqlite-users mailing list