主键需要索引吗？

[bamboo2000]

Sqlite3中

CREATE TABLE tNewsCatalog (
fid integer primary key autoincrement,
fname text,
forderby integer default 0
);

还有没有必要为fid字段创建索引

CREATE INDEX iNewsCatalog_id ON tNewsCatalog (fid ASC);

[ACMAIN_CHM]

当你创建主键的时候，系统会自动添加一个与主主键对应的唯一索引。 不需要再专门添加了。

[ACMAIN_CHM]

sqlite> create table xxx (id int primary key,col int);
sqlite> select * from SQLITE_MASTER;
table|xxx|xxx|10|CREATE TABLE xxx (id int primary key,col int)
index|sqlite_autoindex_xxx_1|xxx|11|
sqlite>

上面例子你可以看到，系统已经自动添加了索引 sqlite_autoindex_xxx_1

[bamboo2000]

我用的是 sqlite.net 在c#中用的

table table1 table1 2 CREATE TABLE table1 ( id integer primary key autoincrement, a text )
table sqlite_sequence sqlite_sequence 3 CREATE TABLE sqlite_sequence(name,seq)
table table2 table2 4 CREATE TABLE table2 ( id integer primary key, a text )

没看到有索引

[ACMAIN_CHM]

贴出下面命令产生的结果。
select * from SQLITE_MASTER;

[bamboo2000]

上面就是结果
我看了下好像
id int primary key
的时候能自己生成索引
id integer primary key autoincrement
id integer primary key
这两种方式不自己生成索引
id int primary key autoincrement 提示错误，int时不能用 autoincrement

[ACMAIN_CHM]

The PRIMARY KEY attribute normally creates a UNIQUE index on the column or columns that are specified as the PRIMARY KEY. The only exception to this behavior is special INTEGER PRIMARY KEY column, described below. According to the SQL standard, PRIMARY KEY should imply NOT NULL. Unfortunately, due to a long-standing coding oversight, this is not the case in SQLite. SQLite allows NULL values in a PRIMARY KEY column. We could change SQLite to conform to the standard (and we might do so in the future), but by the time the oversight was discovered, SQLite was in such wide use that we feared breaking legacy code if we fixed the problem. So for now we have chosen to continue allowing NULLs in PRIMARY KEY columns. Developers should be aware, however, that we may change SQLite to conform to the SQL standard in future and should design new programs accordingly.

SQLite 文档中的说明。　除 INTEGER PRIMARY KEY 以外，其它的都会自动创建UNIQUE 索引。
你可以把上面的英文直接贴到google 语言工具中翻译

[Andy__Huang]

主键已经自动索引了。

[ydlchina]

自动索引