[yunqa.de] Re: disadvantage of using ROWID as the primary key of a table?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Thu, 23 Jul 2009 19:14:08 +0200
At 14:54 23.07.2009, Edwin Yip wrote:
>In the past I use standalone integer fields as primary keys, lately I noticed
>that using ROWID is simpler, tough I'm not sure if there is disadvantages, ie.
>Will I face any kind of problem by using OID as the PK? Thanks.
Short answer:
There are no disadvantages in using ROWID, or OID as the primary key. In fact,
their use is recommended and will save disk space and speed up performance.
However, ROWIDs are not guaranteed to be static. Therefore, always make sure to
explicitly declare your PK as an INTEGER PRIMARY KEY column to prevent SQLite
from changing its values during a VACUUM or database dump and reimport.
Detailed answer: (taken from the DISQLite3 Help -> SQL Syntax -> CREATE TABLE):
ROWIDs and the INTEGER PRIMARY KEY
Every row of every SQLite table has a 64-bit signed integer key that is unique
within the same table. This integer is usually called the "rowid". The rowid is
the actual key used in the B-Tree that implements an SQLite table. Rows are
stored in rowid order. The rowid value can be accessed using one of the special
names "ROWID", "OID", or "_ROWID_".
If a column is declared to be an INTEGER PRIMARY KEY, then that column is not a
"real" database column but instead becomes an alias for the rowid. Unlike
normal SQLite columns, the rowid must be a non-NULL integer value. The rowid is
not able to hold floating point values, strings, BLOBs, or NULLs.
An INTEGER PRIMARY KEY column is an alias for the 64-bit signed integer
rowid.
An INTEGER PRIMARY KEY column can also include the keyword AUTOINCREMENT. The
AUTOINCREMENT keyword modified the way that B-Tree keys are automatically
generated. Additional detail on automatic B-Tree key generation is available
separately.
The special behavior of INTEGER PRIMARY KEY is only available if the type name
is exactly "INTEGER" (in any mixture of upper and lower case.) 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 special
behavior of INTEGER PRIMARY KEY is only available if the primary key is a
single column. Multi-column primary keys do not become aliases for the rowid.
The AUTOINCREMENT keyword only works on a column that is an alias for the rowid.
Note that searches against a rowid are generally about twice as fast as
searches against any other PRIMARY KEY or indexed value.
Goofy behavior alert: The following three declarations all cause the column "x"
be an alias for the rowid:
* 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, in contrast, the following declaration does not result in "x" being an
alias for the rowid:
* CREATE TABLE t(x INTEGER PRIMARY KEY DESC, y, z);
This asymmetrical behavior is unfortunate and is really due to a bug in the
parser in early versions of SQLite. But fixing the bug would result in very
serious backwards incompatibilities. The SQLite developers feel that goofy
behavior in an corner case is far better than a compatibility break, so the
original behavior is retained.
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
