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

[Ulrik Petersen]

Jay 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.
There are calls that will return the value of the field
after it's inserted, you should use them instead.
--- Andrew Shakinovsky <[EMAIL PROTECTED]> 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.
   

Or you could start by inserting a 1.
Ulrik P.
--
Ulrik Petersen, MA, B.Sc.
University of Aalborg, Denmark

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

[Jay]

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.

There are calls that will return the value of the field
after it's inserted, you should use them instead.

--- Andrew Shakinovsky <[EMAIL PROTECTED]> 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.
> 
> 


=

-

"Lord Tarlington gazed upon the crazed Egyptian hieroglyphics on the walls of 
the ancient tomb of the petrified pharaoh, he vowed there would be no curse on 
him like on that other Lord, unless you count his marriage to Lady Tarlington 
who, when the lost treasure was found, will be dumped faster than that basket 
in the bulrushes."
  Melissa Rhodes
-

The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's 
Call
http://www.lulu.com/content/77264



__ 
Do you Yahoo!? 
Take Yahoo! Mail with you! Get it on your mobile phone. 
http://mobile.yahoo.com/maildemo 

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.