just a few explainatory notes from me to myself, hoping that they might
be useful for the community...
again about integer primary key autoincrement, with some details about
last_insert_rowid() and sqlite_sequence.
---------------------------------------------------------------------------
each table has an integer primary key, that you explictly ask for it or
not. this field is called "oid" and possibly also in other ways...
if you explicitly declare a field "integer primary key" (literally!),
the integer primary key (which would be created anyways) becomes also
visible under the name you chose for the field.
the function last_insert_rowid() returns the value of the integer
primary key of the last inserted record. (the oid, which may coincide
with your integer primary key).
if you declare a field "integer primary key autoincrement" (literally,
non case sensitive, in this order!), some sort of sequence is created to
insure that no value for primary key is ever reused. real sequences do
not exist in sqlite3, so the reserved table sqlite_sequence is used to
associate a table name to the highest value already used. the record
relative to the table is created when the first record is inserted into
the table.
when you insert a record into a table and leave the task of choosing the
oid to sqlite, sqlite will look into the table to get the highest oid
currently in use (or into the sqlite_sequence table if you used
"autoincrement"), increment it by one and use this value (and possibly
update the sqlite_sequence table).
just to make a silly example:
sqlite> CREATE TABLE test(
...> f INTEGER PRIMARY KEY AUTOINCREMENT,
...> v int default 0);
sqlite> INSERT INTO "test" VALUES(2, 0);
sqlite> INSERT INTO "test" VALUES(1, 1);
sqlite> select last_insert_rowid();
1
sqlite> SELECT seq FROM sqlite_sequence WHERE name='test';
2
sqlite>
---------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
