SQLite外键是否自动具有索引? [英] Does a SQLite Foreign key automatically have an index?

问题描述

我知道SQLite不会本机执行外键，但这不是我的主要担心.问题是:如果我声明

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),
  ...
  )

sqlite至少会使用以下信息:clientID是外键来优化查询并自动为invoice.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?

推荐答案

即使实际上不是空操作(将描述约束的数据结构添加到表中)，与外键相关的语句也不会创建任何内容所涉及列的索引. 仅在PRIMARY KEY和UNIQUE语句的情况下才隐式创建索引. 有关更多详细信息，请在sqlite源代码树中检出build.c模块: http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/build.c https://www.sqlite.org/src/file?name=src/build.c&ci=tip

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

这篇关于SQLite外键是否自动具有索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！