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
non-unique too).
Sure the fk values are necessarily a subset of the pk values (*) for both
indexes,
but obviously the rowids are completely different, since from different
tables.
So both indexes serve different purposes, and neither can "slave" for each
other.

But perhaps I didn't understand your question correctly? Hopefully that's
useful. --DD

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
when changes
  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to