为什么不能使用 SQLite ROWID 作为主键? [英] Why can't you use SQLite ROWID as a Primary key?

问题描述

这不会执行:

create table TestTable (name text, age integer, primary key (ROWID))

错误信息是:

11-23 11:05:05.298: ERROR/Database(31335): 0x2ab378 上的失败 1(表 TestTable 没有名为 ROWID 的列)在准备创建表 TestTable(名称文本、年龄整数、主键 (ROWID))时)'.

11-23 11:05:05.298: ERROR/Database(31335): Failure 1 (table TestTable has no column named ROWID) on 0x2ab378 when preparing 'create table TestTable (name text, age integer, primary key (ROWID))'.

然而，在创建了 TestTable 之后，这准备并执行得很好:

However, after the TestTable is created, this prepares and executes just fine:

create table TestTable (name text, age integer);

insert into TestTable (name, age) values ('Styler', 27);

select * from TestTable where ROWID=1;

我可能会将 ROWID 视为解决需要一个自动递增的主键和外键，它们永远不会被用作应用层上的数据.由于默认情况下 ROWID 对 select 结果集是隐藏的，因此最好将它与主键相关联，同时保持它对应用程序逻辑隐藏.OracleBlog:ROWNUM 和 ROWID 说这是不可能且不可取的，但是除此之外没有提供太多解释.

I could potentially see ROWID as being a solution to needing an auto-increment primary key and foreign key which are never going to be used as populated as data on the application layer. Since ROWID is hidden from select result sets by default, it would have been nice to associate this with the primary key while keeping it hidden from the application logic. OracleBlog: ROWNUM and ROWID say this is impossible and inadvisable, but doesn't provide much explanation other than that.

因此，既然这可能吗"的答案肯定是不可以/不可取，那么问题或多或少是为什么不"?

So, since the answer to 'is this possible' is definitely no/inadvisable, the question is more or less 'why not'?

推荐答案

来自 SQLite.org 的总结:

Summary from SQLite.org:

在 SQLite 中，表行通常有一个 64 位有符号整数 ROWID它在同一个表的所有行中是唯一的.(没有 ROWID表是个例外.)

In SQLite, table rows normally have a 64-bit signed integer ROWID which is unique among all rows in the same table. (WITHOUT ROWID tables are the exception.)

如果一个表包含一个 INTEGER PRIMARY KEY 类型的列，那么column 成为 ROWID 的别名.然后您可以访问 ROWID使用四个不同名称中的任何一个，原始三个名称(ROWID，_ROWID_ 或 OID) 或指定给 INTEGER PRIMARY KEY 的名称柱子.所有这些名称都是彼此的别名并且同样有效在任何情况下都很好.

If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names (ROWID, _ROWID_, or OID) or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.

只需将其用作主键即可.

Just use it as the primary key.

这篇关于为什么不能使用 SQLite ROWID 作为主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！