Re: [sqlite] quick question regarding INTEGER PRIMARY KEY default value

[Randall Fox]

On Wed, 2 Feb 2005 09:22:09 -0800 (PST), you wrote:

>
>You really should not rely on implicit assumptions in your code,
>such as 'the first value of an autoincrement field is 1'. If you
>use a different database, or different version of this one, that
>may be wrong and a nightmare to fix.

True, but if you use the integer primary key (IPK) as a foreign key in
another table, you don't want a zero value as the IPK.  This is
because when you get the value of the foreign key in the second table,
if the foreign key has NULL in it, sqlite will return 0 from the
function sqlite3_column_int to represent NULL.  The only safe way is
to either not allow zero as a key entry, or get the fkey as a text
value.

Randall Fox

Re: [sqlite] quick question regarding INTEGER PRIMARY KEY default value

[Stephen C. Gilardi]

It is documented to start at 1:

If a table contains a column of type INTEGER PRIMARY KEY, then that 
column becomes an alias for the ROWID.

[...]
If no ROWID is specified on the insert, an appropriate ROWID is created 
automatically. The usual algorithm is to give the newly created row a 
ROWID that is one larger than the largest ROWID in the table prior to 
the insert. If the table is initially empty, then a ROWID of 1 is used. 
If the largest ROWID is equal to the largest possible integer 
(9223372036854775807 in SQLite version 3.0 and later) then the database 
engine starts picking candidate ROWIDs at random until it finds one 
that is not previously used.

--Steve
On Feb 2, 2005, at 11:25 AM, Andrew Shakinovsky wrote:
Will the default value of an INTEGER PRIMARY KEY column always start at
1?
Example:
CREATE TABLE xyz (id INTEGER PRIMARY KEY);
INSERT INTO xyz VALUES (NULL);
Will the value of the id column always be 1 in this example? I tried
numerous tests, and it seems to always start at 1, I wanted to just 
make
sure this would always be the case before I start relying on it in my
code.

[sqlite] quick question regarding INTEGER PRIMARY KEY default value

[Andrew Shakinovsky]

Will the default value of an INTEGER PRIMARY KEY column always start at
1?
Example:
CREATE TABLE xyz (id INTEGER PRIMARY KEY);
INSERT INTO xyz VALUES (NULL);

Will the value of the id column always be 1 in this example? I tried
numerous tests, and it seems to always start at 1, I wanted to just make
sure this would always be the case before I start relying on it in my
code.