Should SQLite foreign-key columns have their own indexes?

Treat the constraint and the index as two separate schema decisions: declaring the foreign key does not itself create the child-column index. SQLite’s guidance says child-key indexes are not required, but are almost always beneficial; it recommends creating one in most real systems. [S1]

The reason is that when a parent row is deleted or its key is changed, SQLite may need to find the rows in the child table that refer to it. Without a usable index on the child key, that lookup requires scanning the child table. [S1]

For your example, the usual choice is an index on orders(customer_id). It need not be unique: several orders may refer to the same customer. [S1]

Here is an illustrative schema; the table and column names are placeholders. Create the tables in a scratch database or other disposable test setup. Then compare the setup without the index against the same setup after adding it. Avoid changing important data just to compare schemas.

-- Shared table definitions: the foreign key is present in both comparisons.
CREATE TABLE customers (
  id INTEGER PRIMARY KEY
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id)
);

-- Comparison B: add this index; omit it for comparison A.
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

The comparison is about the extra index, not about replacing or weakening the constraint. SQLite’s documentation describes declaring a foreign key and creating an index as separate choices. [S1]

Do not make the child-key index UNIQUE unless your data model independently requires each customer to appear in at most one order. For the usual one-customer-to-many-orders relationship, a regular index allows multiple orders to refer to the same customer. SQLite says the child-key index need not be UNIQUE. [S1]

The index is not a substitute for the foreign-key constraint. Also, having a foreign-key declaration in the schema does not by itself settle whether enforcement is active at runtime: SQLite’s documentation says enforcement must be enabled on each connection, assuming the library was built with support. [S1] Check your application’s connection setup; this guide does not establish how a particular application configures it.

A useful default is: for each child foreign key, consider a non-unique index on the child-key column, especially when parent rows might be deleted or their keys changed. SQLite recommends child-key indexes in most real systems because they can avoid a linear scan when looking for referencing rows. [S1]

Before adding one, confirm the exact child table and key columns, and check whether the schema already has an index that can serve those child-key lookups. Avoid adding an index solely because the foreign key exists if your design has a specific reason not to; the documentation says the index is beneficial in most cases, not mandatory in every case. [S1] The source does not establish the performance benefit for your particular workload, so treat the recommendation as a design rule of thumb, not a measured result.