How can i delete every value row in my SQLite Table?

Question

I want to delete all my values in my sqlite Table.
When I click my button it deletes everything besides the id it keeps counting

so for example:

ID, FIRSTNAME, LASTNAME
1, Jack Sparrow
2, Johhny Dep

if I press delete and add new values, it shows this

 ID, FIRSTNAME, LASTNAME
    2, Obama Barack
    3, Mike Tyson

this is my method

 private void DeleteEverything()
    {
        SQLiteDatabase db = mDbHelper.getWritableDatabase();
        db.execSQL("delete from "+NamesContract.NameEntry.TABLE_NAME);
    }

Why are you concerned with the row id? You should never worry about it. Even if it starts from 1962406424 (yes, it may happen). — Phantômaxx, Sep 30 '17 at 18:29
No, it really does not need to. Unless you are doing **weird things** with it. — Phantômaxx, Sep 30 '17 at 18:33

score 0 · Answer 1 · edited Sep 30 '17 at 18:26

0

You should consider to drop and recreate the table.
You can find an example here

edited Sep 30 '17 at 18:26

Phantômaxx

37,901
21
84
115

answered Sep 30 '17 at 17:56

MihaiBC

472
5
15

Did you tried db.execSQL("TRUNCATE table" + TABLE_NAME); – MihaiBC Sep 30 '17 at 18:36

MikeT · Answer 2 · 2017-10-01T06:52:12.277

If you specify ?? INTEGER PRIMARY KEY or ?? INTEGER PRIMARY KEY AUTOINCREMENT then SQlite treats this in a specific way. That is ?? (by the looks of it ID in your case) is an alias for SQLite's rowid.

rowid if not specified when inserting a row will automatically be generated and is in general guaranteed to be unique and is normally incremented by one. However, it is not guaranteed to be incremented nor incremented by one.

If you specify AUTOINCREMENT (i.e. the latter of the two above) then the guarantee, is that a new rowid will be greater but not necessarily by 1.

There is a limit of 9223372036854775807 on the value of rowid. If AUTOINCREMENT is not used and this limit has been reached then an attempt will made to utilise free numbers (e.g. the rowid of rows that have been deleted). If AUTOINCREMENT is specified and the limit has been reached then an insert will fail with an SQLITE_FULL error.

As such, in your case the freed ID's from deleting rows will not be reused.

In short you should never rely upon the rowid (or an alias of it) column being a specific value, rather you should rely upon it just being a unique value purely for the purpose of uniquely identifying a row (and perhaps the fastest way of accessing a row).

You can, albeit it inadvisable, set rowid either by say INSERT INTO mytable (rowid, myothercolumn) VALUES(1, 'other data') or if ID has been used as an alias then INSERT INTO mytable (ID, myothercolumn)VALUES(1, 'other data').

If you really need the first row to be 1 and then 2 and so on then you could DROP the table and then recreate it rather then deleting all rows. e.g. DROP TABLE mytable, followed by CREATE TABLE mytable (ID INTEGER PRIMARY KEY, FIRSTNAME TEXT, LASTNAME TEXT). However, I'd suggest you will just end up with issues which will subsequently be far more complicated to determine and correct.

If you think about it, really what does Johnny Depp being 2 signify rather than Barack Obama being 2? If it is, for example, a popularity rating the surely you'd have some means of determining the popularity and input that directly rather than input it somewhere else to then be used to determine the insertion order and then do the inserts.

Much of the above is basically a summary of SQLite Autoincrement

As an example of unpredictability, a table was created with:-

CREATE TABLE mytable (ID INTEGER PRIMARY KEY, FIRSTNAME TEXT, LASTNAME TEXT)

A row was inserted using INSERT INTO mytable (ID, FIRSTNAME, LASTNAME) VALUES(9223372036854776000,'FRED','BLOGGS'). Note the use of 9223372036854776000 forces above the limit processing.

This was then followed by a number of INSERT INTO mytable(FIRSTNAME, LASTNAME) VALUES('????','????') inserts. Note! without ID, so using SQLITE's unique identifer determination (above the limit processinmg). The resultant table :-

MARY QUITECONTRARY was the 2nd row inserted,TOM SMITH the 3rd. The two RICHARD BURTON rows are an example of where the unique id could be essential for determining a specific row, both were also inserted without specifying the insertion order.

Note! if the above were tried but with AUTOINCREMENT specified, then the second insert, and any subsequent inserts, would fail with an SQLITE_FULL error.

How can i delete every value row in my SQLite Table?

2 Answers2