ROWIDs and the INTEGER PRIMARY KEY

ROWIDs and the INTEGER PRIMARY KEY

Every row of every SQLite table has a 64-bit signed integer key that uniquely identifies the row within its table. This integer is usually called the "rowid". The rowid value can be accessed using one of the special case-independent names "rowid", "oid", or "_rowid_" in place of a column name. If a table contains a user defined column named "rowid", "oid" or "_rowid_", then that name always refers the explicitly declared column and cannot be used to retrieve the integer rowid value.

The data for each table in SQLite is stored as a B-Tree structure containing an entry for each table row, using the rowid value as the key. This means that retrieving or sorting records by rowid is fast. Searching for a record with a specific rowid, or for all records with rowids within a specified range is around twice as fast as a similar search made by specifying any other PRIMARY KEY or indexed value.

With one exception, if a table has a primary key that consists of a single column, and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

The exception mentioned above is that if the declaration of a column with declared type "INTEGER" includes an "PRIMARY KEY DESC" clause, it does not become an alias for the rowid and is not classified as an integer primary key. This quirk is not by design. It is due to a bug in early versions of SQLite. But fixing the bug could result in very serious backwards incompatibilities. The SQLite developers feel that goofy behavior in a corner case is far better than a compatibility break, so the original behavior is retained. This means that the following three table declarations all cause the column "x" to be an alias for the rowid (an integer primary key):
?CREATE TABLE t(x INTEGER PRIMARY KEY ASC, y, z);
?CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x ASC));
?CREATE TABLE t(x INTEGER, y, z, PRIMARY KEY(x DESC));

But the following declaration does not result in "x" being an alias for the rowid:
?CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);

Rowid values may be modified using an UPDATE statement in the same way as any other column value can, either using one of the built-in aliases ("rowid", "oid" or "_rowid_") or by using an alias created by an integer primary key. Similarly, an INSERT statement may provide a value to use as the rowid for each row inserted. Unlike normal SQLite columns, an integer primary key or rowid column must contain integer values. Integer primary key or rowid columns are not able to hold floating point values, strings, BLOBs, or NULLs.

If an UPDATE statement attempts to set an integer primary key or rowid column to a NULL or blob value, or to a string or real value that cannot be losslessly converted to an integer, a "datatype mismatch" error occurs and the statement is aborted. If an INSERT statement attempts to insert a blob value, or a string or real value that cannot be losslessly converted to an integer into an integer primary key or rowid column, a "datatype mismatch" error occurs and the statement is aborted.

If an INSERT statement attempts to insert a NULL value into a rowid or integer primary key column, the system chooses an integer value to use as the rowid automatically. A detailed description of how this is done is provided separately.

The parent key of a foreign key constraint is not allowed to use the rowid. The parent key must used named columns only.

相关文章

2013年平板电脑排行榜Office 2003 SP2 简体中文完整版下载与25位密钥
PHP下载文件代码读书郎学生平板电脑G50,G12,G11详细介绍
PHP:SQLite3数据库操作代码使用PHP的ODBC函数库操作ACCESS数据库
PHP:ODBC处理EXCEL电子表格数据库实例ROWIDs and the INTEGER PRIMARY KEY
用汇编语言修改IE浏览器首页MySQL创建数据库:CREATE DATABASE语法
apache2.4.6 httpd.exe命令我要读书——农村儿童的心声!
MySQL数据库定义语句:ALTER DATABASE语法HTML5本地存储检测、保存、读取
2013年平板电脑品牌排行榜PHP+SQLite数据库操作教程与实例
SQLite的数据类型爱学宝平板学习机A8,A33,A36,A511,AX730,AX750详细介绍
MySQL5存储过程视频教程,PHP操作MySQL存储过程示例如何删除Word页眉横线
版权所有 © 中山市飞娥软件工作室 证书:粤ICP备09170368号