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, Jun 03 '23 at 13:58

score 49 · Answer 1 · edited Jul 15 '20 at 22:39

49

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 '20 at 22:39

Charles L.

5,795
10
40
60

answered Jun 08 '11 at 18:55

Kyle Gadd

501
4
5

1

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. – Mark Dec 27 '18 at 02:49
2

@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)._ – jwalker Sep 24 '20 at 19:46
@jwalker that's true, but what if you are not deleting from the parent table? – Mark Feb 23 '21 at 15:02
@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 – jwalker Feb 28 '21 at 04:29
it's updates as well as deletes – Mooing Duck Jun 03 '23 at 13:59

Masci · Accepted Answer · 2020-07-27T08:23:52.767

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

Does a SQLite Foreign key automatically have an index?

2 Answers2

Linked