Re: [sqlite] Does a foreign key field need a separate index?

[Dominique Devienne]

On Thu, Feb 6, 2020 at 11:08 PM Simon Slavin  wrote:
> On 6 Feb 2020, at 9:23pm, Rael Bauer  wrote:
> > Is a foreign key field automatically indexed, or will it benefit from a 
> > separately created index?
> No indexes for either lookup are automatically created.

The shell's .expert command might recommend them. Not sure, haven't tried.
But in general, yes, FKs should be indexed, if are are CASCADE'ing them. --DD

https://sqlite.org/cli.html#expert
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Does a foreign key field need a separate index?

[Simon Slavin]

On 6 Feb 2020, at 9:23pm, Rael Bauer  wrote:

> Is a foreign key field automatically indexed, or will it benefit from a 
> separately created index?

No indexes for either lookup are automatically created.  You should create 
indexes which will help lookup in both directions, unless one of the tables is 
small or a useful index exists for some other reason, e.g. because the key is 
declared as UNIQUE.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Does a foreign key field need a separate index?

[Rael Bauer]

Hi,

Is a foreign key field automatically indexed, or will it benefit from a 
separately created index?


E.g.

CREATE TABLE [content](
  [note_id] INTEGER REFERENCES [notes]([id]) ON DELETE CASCADE,
  [data] BLOB);

Will note_id benefit from a separately created index?

Thanks
Rael

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users