"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] -----------------------------------------------------------------------------
