Does a foreign key field need a separate index?

[Rael Bauer-3]

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin-3]

On 6 Feb 2020, at 9:23pm, Rael Bauer <[hidden email]> 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.

<https://sqlite.org/foreignkeys.html#fk_indexes>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Dominique Devienne]

On Thu, Feb 6, 2020 at 11:08 PM Simon Slavin <[hidden email]> wrote:
> On 6 Feb 2020, at 9:23pm, Rael Bauer <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users