On Mon, Dec 15, 2008 at 04:34:29PM +0000, Nathan Catlow scratched on the wall:
> Quoting "D. Richard Hipp" <[email protected]>:
> > Secondly, AUTOINCREMENT in SQLite only works on an INTEGER PRIMARY
> > KEY, not on any other kind of primary key or on any non-primary-key
> > field. BIGINT PRIMARY KEY is not an INTEGER PRIMARY KEY and so
> > AUTOINCREMENT won't work on it.
>
> So you are saying there is an internal difference between INTEGER and
> BIGINT in sqlite? I thought they were just synonyms for INTEGER.
I think this is the key point to this whole discussion.
There is no difference between the types INTEGER and BIGINT in the
SQLite world, at least in terms of size.
There *ARE*, however, some very meaningful and fundamental
differences between columns defined as INTEGER PRIMARY KEY and BIGINT
PRIMARY KEY.
INTEGER PRIMARY KEY columns get turned into ROWID columns. That
transformation alters the way the table is structured and stored.
It is a meaningful and significant change to the behavior of the column
and table.
For a truly cross-platform design, I think it is best to ignore the
whole AUTOINCREMENT feature and just define a generic PRIMARY
KEY NOT NULL** column. If the application demands a sequence feature,
the author/library/user needs to roll their own. AUTOINCREMENT is
not really a sequencing system, it is a specialized behavior for a
highly specialized column type.
(Surely someone somewhere has a sequence plugin for SQLite?)
** Yes, SQLite needs the NOT NULL on PK columns. See docs.
> I see no evidence to support the assertion that BIGINT PRIMARY KEY !=
> INTEGER PRIMARY KEY.
Then you're not reading the docs:
http://sqlite.org/lang_createtable.html
...if primary key is on a single column that has datatype INTEGER,
then that column is used internally as the actual key of the B-Tree
for the table. This means that the column may only hold unique
integer values. (Except for this one case, SQLite ignores the
datatype specification of columns and allows any kind of data to
be put in a column regardless of its declared datatype.) If a table
does not have an INTEGER PRIMARY KEY column, then the B-Tree key
will be a automatically generated integer. The B-Tree key for a
row can always be accessed using one of the special names "ROWID",
"OID", or "_ROWID_". This is true regardless of whether or not
there is an INTEGER PRIMARY KEY.
While these differences are much more significant to the SQLite world
that other databases, they are still significant differences that have
deep and far-reaching meaning in the database engine.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
