On Tue, Feb 6, 2018 at 2:24 AM, J Decker <[email protected]> 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
