Re: [sqlite] Resetting a Primary Key

2007-11-22 Thread Asif Lodhi
Hi Vincent, On 11/21/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > I have a primary key that auto increments and has apparently > overlapped back on to itself. > > INSERT into mytable(id,name) values(NULL,'test'); > > .. is giving me "primary key must be unique" errors. > > How can I reset

Re: [sqlite] Resetting a Primary Key

2007-11-21 Thread Dennis Cote
Mitchell Vincent wrote: I found a solution - apparently the DB file was just hosed. There were actually duplicates stored somehow. I dumped the database to a flat file and modified the IDs by hand then imported the data into a new SQLite database. That seemed to do the trick. I'm not sure how

Re: [sqlite] Resetting a Primary Key

2007-11-21 Thread Dennis Cote
Mitchell Vincent wrote: I'm not sure what it means, but here it is : *** in database main *** On page 2580 at right child: 2nd reference to page 2677 On tree page 9 cell 15: 2nd reference to page 2678 On tree page 9 cell 15: Child page depth differs On tree page 9 cell 16: Child page depth

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Mitchell Vincent
I found a solution - apparently the DB file was just hosed. There were actually duplicates stored somehow. I dumped the database to a flat file and modified the IDs by hand then imported the data into a new SQLite database. That seemed to do the trick. I'm not sure how the corruption happened but

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Dennis Cote
Mitchell Vincent wrote: select max(item_id) from invoice_items; Produces "803" - but that is pretty clearly wrong when I look at the dataset a count() returns 15503 records in that table. min(item_id) is 1 (as expected). I can insert records if I specify a unique item_id. On Nov 20, 2007

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Mitchell Vincent
No triggers at all in the database. It's pretty vanilla.. On Nov 20, 2007 6:04 PM, <[EMAIL PROTECTED]> wrote: > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > > Here we go. I am accessing the database through an ODBC connection > > using the most recent version from Christian's site.. > > > >

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Mitchell Vincent
select max(item_id) from invoice_items; Produces "803" - but that is pretty clearly wrong when I look at the dataset a count() returns 15503 records in that table. min(item_id) is 1 (as expected). I can insert records if I specify a unique item_id. On Nov 20, 2007 5:50 PM, Dennis Cote <[EMAIL

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread drh
"Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > Here we go. I am accessing the database through an ODBC connection > using the most recent version from Christian's site.. > > The table schema : > > CREATE TABLE invoice_items ( > item_id INTEGER PRIMARY KEY, > invoice_id int4, > product_id

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Dennis Cote
Mitchell Vincent wrote: Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text ,

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Mitchell Vincent
Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text , description text ,

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread Mitchell Vincent
No doubt, it is obviously something that has been screwed up. Unfortunately I'm in "hot fix" mode right now and the investigation into why it happened will happen after I get this customer back up and running. I have a feeling it has something to do with the ODBC driver being used. I'll gather and

Re: [sqlite] Resetting a Primary Key

2007-11-20 Thread drh
"Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > I have a primary key that auto increments and has apparently > overlapped back on to itself. > > INSERT into mytable(id,name) values(NULL,'test'); > > ... is giving me "primary key must be unique" errors. > > How can I reset the sequence for a

RE: [sqlite] Resetting a Primary Key

2007-11-20 Thread Griggs, Donald
condition? -Original Message- From: Mitchell Vincent [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 4:01 PM To: sqlite-users@sqlite.org Subject: [sqlite] Resetting a Primary Key I have a primary key that auto increments and has apparently overlapped back on to itself. INSERT