Does a SQLite Foreign key automatically have an index?

Question

I know that SQLite does not enforce foreign keys natively, but that's not my primary concern. The question is: If I declare

CREATE TABLE invoice ( invoiceID INTEGER PRIMARY KEY, clientID INTEGER REFERENCES client(clientID), ... )

will sqlite at least use the information that clientID is a foreign key to optimize queries and automatically index invoice.clientID, or is this constraint a real no-op?

SQLite does enforce foreign keys, and it can do so without an index — Mooing Duck
– Mooing Duck, Commented Jun 3, 2023 at 13:58

Charles L. · Accepted Answer · 2020-07-15 22:39:48Z

56

In the SQLite Documentation it says:

... "an index should be created on the child key columns of each foreign key constraint"

ie. the index is not automatically created, but you should create one in every instance.

edited Jul 15, 2020 at 22:39

Charles L.

6,40512 gold badges43 silver badges66 bronze badges

answered Jun 8, 2011 at 18:55

Kyle Gadd

5714 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mark Over a year ago

I'm not sure why they say this. Surely it depends how you are using the child table? For example, if you only search the child table (some other column) and then use the child column (of foreign key) to lookup parent table.

jwalker Over a year ago

@Mark They explain it: each time an application deletes a row from the artist table (the parent table), it performs the equivalent of the following SELECT statement to search for referencing rows in the track table (the child table).

Mark Over a year ago

@jwalker that's true, but what if you are not deleting from the parent table?

jwalker Over a year ago

@Mark Then you don't have to create it, but then again, it won't hurt to have it in case you ever start deleting

Mooing Duck Over a year ago

it's updates as well as deletes

Masci · Accepted Answer · 2020-07-27 08:23:52Z

Even if it is not actually a no-op (a data structure describing the constraint is added to the table), foreign key related statement doesn't create any index on involved columns. Indexes are implicitly created only in the case of PRIMARY KEY and UNIQUE statements. For more details, check it out build.c module on the sqlite source tree: ~~http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/build.c~~ https://www.sqlite.org/src/file?name=src/build.c&ci=tip

Collectives™ on Stack Overflow

Does a SQLite Foreign key automatically have an index?

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related