Does a SQLite Foreign key automatically have an in

问题:

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?

回答1:

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

回答2:

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.