row ID error after deleting a row in Android SQLite database

Question

I have created a program based on this Deleting entry from SQLite database tutorial.

Everything works fine except one thing. While I have added new rows to the SQLite Database it worked perfectly, but as soon as I deleted one of these rows, the rest of the ROWID haven't updated itself, just continued counting as usually.

For example I have added five rows, than I deleted the third one so the others should rewrite themselves to 1,2,3,4. But the ROWIDs remained: 1,2,4,5

I was looking for this problem on the Internet, and I found out that I should somehow put the remained rows to a new database and then back and it should be all right. But I have never worked with SQL before, so I don't know the code how to fix the problem. Could you please tell me, how to solve my problem?

Here is my database code:

public class HotOrNot {

public static final String KEY_ROWID = "_id";
public static final String KEY_NAME = "persons_name";
public static final String KEY_HOTNESS = "persons_hotness";

private static final String DATABASE_NAME = "HotOrNotdb";
private static final String DATABASE_TABLE = "peopleTable";
private static final int DATABASE_VERSION = 1;

private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;

private static class DbHelper extends SQLiteOpenHelper {

    public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" + 
                KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + 
                KEY_NAME + " TEXT NOT NULL, " + 
                KEY_HOTNESS + " TEXT NOT NULL);"



                );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
        onCreate(db);
    }

}
public HotOrNot(Context c){
    ourContext = c;
}

public HotOrNot open() throws SQLException{
    ourHelper = new DbHelper(ourContext);
    ourDatabase = ourHelper.getWritableDatabase();
    return this;
}
public void close(){
    ourHelper.close();
}

public long createEntry(String name, String hotness) {
    // TODO Auto-generated method stub
    ContentValues cv = new ContentValues();
    cv.put(KEY_NAME, name);
    cv.put(KEY_HOTNESS, hotness);
    return ourDatabase.insert(DATABASE_TABLE, null, cv);
}

public String getData() {
    // TODO Auto-generated method stub
    String[] columns = new String[]{ KEY_ROWID, KEY_NAME, KEY_HOTNESS};
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null, null, null);
    String result = "";

    int iRow = c.getColumnIndex(KEY_ROWID);
    int iName = c.getColumnIndex(KEY_NAME);
    int iHotness = c.getColumnIndex(KEY_HOTNESS);

    for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
        result = result + c.getString(iRow) + " " + c.getString(iName) + " " + c.getString(iHotness) + "\n";
    }
    return result;
}

public String getName(long l) throws SQLException{
    // TODO Auto-generated method stub
    String[] columns = new String[]{ KEY_ROWID, KEY_NAME, KEY_HOTNESS};
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l, null, null, null, null);
    if(c != null){
        c.moveToFirst();
        String name = c.getString(1);
        return name;
    }
    return null;
}

public String getHotness(long l) throws SQLException{
    // TODO Auto-generated method stub
    String[] columns = new String[]{ KEY_ROWID, KEY_NAME, KEY_HOTNESS};
    Cursor c = ourDatabase.query(DATABASE_TABLE, columns, KEY_ROWID + "=" + l, null, null, null, null);
    if(c != null){
        c.moveToFirst();
        String hotness = c.getString(2);
        return hotness;
    }
    return null;
}

public void updateEntry(long lRow, String mName, String mHotness) throws SQLException{
    // TODO Auto-generated method stub
    ContentValues cvUpdate = new ContentValues();
    cvUpdate.put(KEY_NAME, mName);
    cvUpdate.put(KEY_HOTNESS, mHotness);
    ourDatabase.update(DATABASE_TABLE, cvUpdate, KEY_ROWID + "=" + lRow, null);
}

public void deleteEntry(long lRow1) throws SQLException{
    // TODO Auto-generated method stub
    ourDatabase.delete(DATABASE_TABLE, KEY_ROWID + "=" + lRow1, null);
}
}

Thanks in advance!

Answer 1

The ID (primary key)in database is unique for each record and it is assigned once.

They can not be changed later for any record.

So if you delete any record then other rowId will not get affected.

The result you are getting is Expected Result. Don't Worry.