SQLite中的外键优化 [英] Foreign key optimization in SQLite

问题描述

我知道SQLite本身并不强制外键，但那不是我主要关心的问题。问题是：如果我声明

pre $ code CREATE TABLE invoice（
invoiceID INTEGER PRIMARY KEY，
clientID INTEGER参考客户端（clientID），
...
）

最少使用clientID是一个外键的信息来优化查询并自动索引invoice.clientID，或者这个约束是一个真正的no-op？

解决方案

即使它实际上不是一个无操作（一个描述约束的数据结构被添加到表中），与外键相关的语句也不会在涉及的列上创建任何索引。
只有在PRIMARY KEY和UNIQUE语句的情况下才会隐含地创建索引。
有关更多详细信息，请查看sqlite源代码树中的build.c模块：
http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/build.c

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?

解决方案

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

这篇关于SQLite中的外键优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！