RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question
Brickl Roland [mailto:[EMAIL PROTECTED] wrote: Integer PrimaryKeys are always autoincrementing. When you don't specify it it uses after (2^63)-1 a random free positiv value. Odekirk, Shawn [EMAIL PROTECTED] wrote: I will compile this for Windows and see what my results are using a compiler that supports 64 bit integers. Odekirk, Shawn [EMAIL PROTECTED] wrote: Indeed, compiled using Microsoft Visual Studio 2005 it works as described. So, now to dive into the source and figure out how to make it work using my old SCO tools. Well, that was easy! A little poking around and I found the SQLITE_32BIT_ROWID preprocessor symbol. Simply defining that symbol and recompiling fixed my problem. I have to say that I am very impressed with how easy it has been to compile SQLite on different platforms and with how well it seems to work. I'm looking around trying to find other projects where I can use it. Shawn - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question
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] -
Re: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question
Odekirk, Shawn [EMAIL PROTECTED] wrote: 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. Very likely. -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] INTEGER PRIMARY KEY Auto Increment Rollover Question
Brickl Roland [mailto:[EMAIL PROTECTED] wrote: Integer PrimaryKeys are always autoincrementing. When you don't specify it it uses after (2^63)-1 a random free positiv value. Odekirk, Shawn [EMAIL PROTECTED] wrote: I will compile this for Windows and see what my results are using a compiler that supports 64 bit integers. Indeed, compiled using Microsoft Visual Studio 2005 it works as described. So, now to dive into the source and figure out how to make it work using my old SCO tools. Thanks, Shawn - To unsubscribe, send email to [EMAIL PROTECTED] -
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] -
