How to reset auto-increment int when deleting all data from database?
SQLite Reset Primary Key Field
Reseting SQLite autoincrement column
One method to set zero
UPDATE SQLITE_SEQUENCE SET SEQ=0 WHERE NAME='table_name';Second method to set maximum of id in current table
There are two simple steps in resetting an auto increment field:
Find the highest number in the auto increment field
To find the highest number, run the following SQL command:
SELECT MAX( `column` ) FROM `table` ;
Replace 'column' with the name of the auto incrementing column. Replace table with the name of the table.
Example : SELECT max(id) as id FROM customerentries
Reset the auto increment field
The next step is to take the number that you get from the first command and auto increment from there. So add one to that number and run the following command:
UPDATE SQLITE_SEQUENCE SET SEQ=new_value WHERE NAME='table_name';
Replacing new_value with the result of the previous command plus one and replacing table with the table_name.
Example:
/*autoincrement id reset max of id*/
public void resetIdCustomerentries(){
try {
String selectQuery = "SELECT max(id) as id FROM customerentries";
SQLiteDatabase database = this.getWritableDatabase();
Cursor cursor = database.rawQuery(selectQuery, null);
cursor.moveToFirst();
int idc = cursor.getInt(cursor.getColumnIndex("id"))+1;
//int idc = cursor.getInt(0)+1;
String updateQuery = "UPDATE SQLITE_SEQUENCE SET SEQ= "+ idc + " WHERE NAME='customerentries'";
database.execSQL(updateQuery);
database.close();
}
catch (android.database.sqlite.SQLiteConstraintException e) {
Log.e("TAG:", "SQLiteConstraintException:" + e.getMessage());
}
catch (android.database.sqlite.SQLiteException e) {
Log.e("TAG:", "SQLiteException:" + e.getMessage());
}
catch (Exception e) {
Log.e("TAG:", "Exception:" + e.getMessage());
}
}
This resetIdCustomerentries function reset next value of autoincrement field id.
 |
| Click Here |
Related Links
No comments:
Post a Comment