[yunqa.de] Re: disadvantage of using ROWID as the primary key of a table?
- From: Edwin Yip <edwin.yip@xxxxxxxxxxxxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Fri, 24 Jul 2009 15:18:50 +0800
Hi Ralf,
Thanks for the detailed explanation. so in other words, if I define a column
of any name of type "INTEGER PRIMARY KEY", referencing that column in SQL is
exactly the same as referencing ROWID, and the values in that column will
not be altered unexpected by the engine, right?
And Is "INTEGER *NOT NULL* PRIMARY KEY" equal to "INTEGER PRIMARY KEY"?
On Fri, Jul 24, 2009 at 1:14 AM, Delphi Inspiration <delphi@xxxxxxxx> wrote:
> 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
>
>
>
>
--
Best Regards,
Edwin Yip
Mind Mapping is as Effortless as Typing
http://www.InnovationGear.com
Other related posts:
