CODEWITHSUNDEEP
androiddatabasesqlite
Kevik
Kevik

Reputation: 9361

not able to reset primary key numbering in SQLite table

I am trying to use "DELETE FROM SQLITE_SEQUENCE" SQL statement to reset the numbering system for _ID rowId that is the primary key for the table. This makes the first row as 1 again for the table. I was able to do this with no problems in a java desktop app, however in this android app it is not working. any ideas?

EDIT: i found and fixed the runtime error. so I removed the stacktrace from the question. that part is fixed however, now it will not reset the numbering of the rows. when i display the database it shows the same numbering. for example if i delete row 3, then that missing row 3 is still missing after the "DELETE FROM SQLITE_SEQUENCE is used. is there something else that i neeed to do after running that SQL statement? like reaload the database? i did not have to do that with the java app.

from Database class

 public static final String MYDATABASE_NAME = "my_database";
    public static final String MYDATABASE_TABLE = "my_table";
    public static final int MYDATABASE_VERSION = 1;
    public static final String _ID = "_id";
    public static final String TABLE_STRING = "table_string";
    public static final String PAGE = "page";
    public static final String VERSION = "version";

  //create table MY_DATABASE (_ID integer primary key, Content text not null);
    private static final String SCRIPT_CREATE_DATABASE =
        "CREATE TABLE IF NOT EXISTS " + MYDATABASE_TABLE + " (" + _ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +  
        TABLE_STRING + " TEXT, " + PAGE + " TEXT, " + VERSION + " TEXT);";

    SQLiteDatabase sqLiteDatabase;
    SQLiteHelper sqLiteHelper;
    Context context;

from the Database class 

public void resetRowNumbers(){

             sqLiteDatabase.execSQL("DELETE FROM SQLITE_SEQUENCE WHERE NAME =
          '" + MYDATABASE_TABLE + "'");
         }

from the MainActivity class

 public void resetRowNumbering(){
    db = new Database(getApplication());
    db.openToWrite();
    db.resetRowNumbers();
    db.close();
}

Upvotes: 3

Views: 2279

Answers (2)

Kevik
Kevik

Reputation: 9361

here is the code I found that works:

 sqLiteDatabase.execSQL("DELETE FROM '" + MYDATABASE_TABLE + "'");
 sqLiteDatabase.execSQL("DELETE FROM SQLITE_SEQUENCE WHERE NAME =
  '" + MYDATABASE_TABLE + "'");

The answer is for the part of the code that had a * in between DELETE and FROM for the first SQL statement above. For Android using the * does not work. It results in a runtime error. This despite the fact that it is sometimes shown in internet examples for SQL statements. It does not work with Android's version of SQLite.

the correct statement is:

 "DELETE FROM '" + MYDATABASE_TABLE + "'"

this deletes all rows from a table. then after that use:

  "DELETE FROM SQLITE_SEQUENCE WHERE NAME = '" + MYDATABASE_TABLE + "'"

which will reset the primary key numbering to start from 1 again when you enter a new row.

Upvotes: 1

OschtärEi
OschtärEi

Reputation: 2265

Well first of all you should not delete the sequence but reset it to zero:

"UPDATE sqlite_sequence SET seq = 0 WHERE name=  '" + MYDATABASE_TABLE + "'"

But generally said, and you may know this, it's almost never agood idea to reset a sequence.

Upvotes: 1

Related Questions