Re: [sqlite] reseting primary key
DRH SAID: [EMAIL PROTECTED] wrote:The details of the UPDATE or DELETE are left as an exercise for the reader. Excellent, it reminds me of High School integral proofs. Q.E.D.
RE: [sqlite] reseting primary key
Thank you both for your responses; Very informative. It is much appreciated. Regards, - Jon -Original Message- From: C.Peachment [mailto:[EMAIL PROTECTED] Sent: Monday, May 07, 2007 12:07 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] reseting primary key On Mon, 7 May 2007 11:28:57 -0400, Jonathan Kahn wrote: >Hey all, > I have tried different things such as reindex and vacuum on my primary key >auto inc field but I cannot reset it so that things start from one. I >deleted everything from my table but it still keeps the amount that was >there beforehand on the auto inc so anything new is appened to that number >so I used to have 7 records when I insert new it starts at 8, how can I >start it back from 1, its beginning to drive me a little crazy. You could try altering values in the sqlite_sequence table in your database. It works for me. Remember to delete all records in the data table to avoid problems with the auto increment column(s). Chris Peachment - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] reseting primary key
On Mon, 7 May 2007 11:28:57 -0400, Jonathan Kahn wrote: >Hey all, > I have tried different things such as reindex and vacuum on my primary key >auto inc field but I cannot reset it so that things start from one. I >deleted everything from my table but it still keeps the amount that was >there beforehand on the auto inc so anything new is appened to that number >so I used to have 7 records when I insert new it starts at 8, how can I >start it back from 1, its beginning to drive me a little crazy. You could try altering values in the sqlite_sequence table in your database. It works for me. Remember to delete all records in the data table to avoid problems with the auto increment column(s). Chris Peachment - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] reseting primary key
"Jonathan Kahn" <[EMAIL PROTECTED]> wrote: > > I have tried different things such as reindex and vacuum on my primary key > auto inc field but I cannot reset it so that things start from one. I > deleted everything from my table but it still keeps the amount that was > there beforehand on the auto inc so anything new is appened to that number > so I used to have 7 records when I insert new it starts at 8, how can I > start it back from 1, its beginning to drive me a little crazy. > This is a deliberate feature of SQLite, not a bug. When you add the AUTOINCREMENT keyword to an INTEGER PRIMARY KEY, then when SQLite selects new primary keys, it is careful to select keys that have never been used before, even by rows that where subsequently deleted. If you do not mind reusing the primary key from a row that has been deleted (or if this is what you want to do) then just omit the AUTOINCREMENT keyword. Without AUTOINCREMENT, if you try to insert a NULL into an INTEGER PRIMARY KEY, SQLite will automatically convert the NULL into an integer value that does not currently exist in the table. It might reuse a value from that was found on a row that was previously deleted, however. If you still want to use the AUTOINCREMENT keyword but you want to erase the history from the database so that rowids from deleted rows can be reused, then you will need to make an UPDATE or a DELETE against the "sqlite_sequence" table. The details of the UPDATE or DELETE are left as an exercise for the reader. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
