CODEWITHSUNDEEP
androidandroid-sqlite
Pingu
Pingu

Reputation: 646

Android SQLite database removing a record

I am now building an android phone book app and employing the method of SQLite database to store records. In my database, there is _id column which is a primary key and will increase automatically. A strange thing is that when I delete records, the removed _id number is not re-used.

E.g. Originally the database is 

_id name
1   Peter
2   Mary

After deleting 2 (Mary), when I add a new record (John), I found that John is placed with an id = 3.

So my question is: is Mary still there? Is it just flagged to be "deleted" but not really deleted? If so, would this be a wastage of memory and should I do something to remove it completely?

Upvotes: 1

Views: 103

Answers (3)

Manish Mulimani
Manish Mulimani

Reputation: 17615

The row is deleted. Generating the auto increment ID is implementation specific. It is upto to the autoincrement algorithm to assign the new id or reuse the deleted ID. Please refer sqlite tutorial : http://sqlite.org/autoinc.html

This is a quote in the mentioned in above link: With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing. Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.

Upvotes: 1

WindsurferOak
WindsurferOak

Reputation: 4907

No, after deleting row id 2, the record is completely deleted. The incrementing counter will continue from where it left off, so it is correct to see John with an id of 3.

Upvotes: 1

Frank Tudor
Frank Tudor

Reputation: 4504

You need to make your ID column an INT primary key and add auto increment to it.

Upvotes: 0

Related Questions