Thank you for your reply. I tried adding NOT NULL to my primary key column, but the results are the same. My compiler is old and I don't think it supports a 64 bit data type. Maybe this is the root cause of my problem.
If I create a table like this: CREATE TABLE rollover (id INTEGER PRIMARY KEY, name TEXT) or: CREATE TABLE rollover (id INTEGER PRIMARY KEY NOT NULL, name TEXT) and I insert a row like this: INSERT INTO rollover VALUES (2147483647, 'One'); the row has the values: 2147483647, 'One' If I then insert a row like this: INSERT INTO rollover VALUES (NULL, 'Two'); the newly inserted row has the following values: -2147483648, 'Two' If I then try to insert another row like this: INSERT INTO rollover VALUES (NULL, 'Three'); I get: "SQL error: PRIMARY KEY must be unique" I would like the primary key to rollover to 1, instead of to -2147483648. Does anyone have any ideas where I should look? I will compile this for Windows and see what my results are using a compiler that supports 64 bit integers. Thanks, Shawn -----Original Message----- From: Brickl Roland [mailto:[EMAIL PROTECTED] Sent: Saturday, October 13, 2007 5:29 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question Hallo Odekirk Shawn, SQLite use up to an 64Bit signed Integer for Primary Keys, even on non 64Bit-Systems! Integer PrimaryKeys are always autoincrementing. When you don't specify it it uses after (2^63)-1 a random free positiv value. When you write autoincrement for your create table it never reuse deleted positiv values and you get an SQLITE_FULL error, thats all. And please don't forget the not null for your primary key. Without this you get a little bit different behavior. Greats, Brickl --- "Odekirk, Shawn" <[EMAIL PROTECTED]> schrieb: > I am evaluating SQLite for a project I am working on. > > I have a question about the behavior of the INTEGER PRIMARY KEY auto > increment feature. > > My platform uses 32 bit integers, so the valid values for an unsigned > integer are 0 - 4294967296 and the valid values for a signed integer are > -2147483648 - 2147483647. > > Since the INTEGER PRIMARY KEY data type is a signed integer, the maximum > positive value is 2147483648. If my table already has a row with the > maximum positive value in the primary key field, and I insert a row > using NULL as the value of the primary key field, the row is inserted > and the primary key is assigned the value of -2147483648. That makes > sense to me and I have no problem with that. The problem is that the > next row I insert generates the error "SQL error: PRIMARY KEY must be > unique". I suspect that this is because SQLite tries to use the next > largest positive value when it increments the primary key field. > > Is there an easy way to cause the INTEGER PRIMARY KEY column to use an > unsigned integer instead, or to roll over to 0 instead of the most > negative value for the data type? > > I suspect that in practice I will not run into this issue. However, I > would feel better knowing that there is no chance that I will encounter > this problem. > > > > Thanks, > > Shawn > > > > __________________________________ Yahoo! Clever: Sie haben Fragen? Yahoo! Nutzer antworten Ihnen. www.yahoo.de/clever ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
