autoincrement column remembers its last value in SQLite table

In one table I have a column that's an auto-incrementing primary key.

The problem is this scenario:

• 4 rows are inserted
• those 4 are then subsequently deleted
• insert a new row, and the the auto-increment column value is 5

How can I get this auto-increment column to ha开发者_如何学Gove value of 1?

An SQLite Autoincrement field will increment forever.It will not replace the deleted value.It will replace deleted value only if it exists maximum possible value.See this . What you are trying to achieve is not possible using autoincrement field.You have to do it programtically.

Instead of using auto increment you can just use integer primary key it will work fine. Even if your row is deleted one by one, the next increment will be done properly. If you leave the integer primary key field empty, the max value of the row is taken and a 1 is added (it's like max(value)+1). It works fine for me.

if we have a primary which is autoincrement, the value will not be same even if you delete all the existing entries. It will continue from where it stopped on last insert. if you want the primary key to start from 1 again, you need to truncate the table or delete & recreate the table.

In SQLite "truncate" command is not valid. You can use a "Delete from tablename" and "vacuum tablename" to reset the primary key count to start from 1. Please make sure there is no "where" clause in delete command.