Delete entry in SQLite database automatically (android)

Question

How can I tell my database to automatically delete the entry with the lowest ID when a new row is created? I want this to be done when there are more than ten rows in the table.

Here is my createEntry method:

public long createEntry(String latitude, String longitude) {
    // TODO Auto-generated method stub
    ContentValues cv = new ContentValues();

    cv.put(KEY_LATITUDE, latitude);     
    cv.put(KEY_LONGITUDE, longitude);



    return myDatabase.insert(DATABASE_TABLE, null, cv);
}

Community · Accepted Answer · 2017-05-23 10:34:29Z

2

If you want to delete the "oldest" row with the smallest id use this answer from Rolling rows in SQL table.

However a SQLite table has a maximum number of row ids. Once this limit has been reached, a table that uses integer primary key autoincrement will search for old, unused row ids for new insertions. This could result in the latest row having the lowest row id (and being deleted first).

Understand that this limit is typically 9223372036854775807. If you expect to exceed this many insertions in your smartphone app:

You're nuts.
You can use timestamps in your trigger instead.

edited May 23, 2017 at 10:34

CommunityBot

11 silver badge

answered Jul 15, 2012 at 23:02

Sam

87k21 gold badges185 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Over a year ago

Thanks for your answer! Well I might not use that many entries, but I would like to limit the amount of entries to make the database easier to handle. I have a bunch of different databases, each database updates frequently and I only need like the last ten rows of information, thus making the other ones uninteresting.

Sam Over a year ago

"I would like to limit the amount of entries to make the database easier to handle" The link I posted uses a trigger which prevents you from having to access the database multiple times to check the row count and delete the unnecessary rows, the trigger does this in one access.

Collectives™ on Stack Overflow

Delete entry in SQLite database automatically (android)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related