Delete from JDBC Derby Database using the auto-generated key (INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1))

Question

Just wondering what I am doing wrong here and how to work around it. I've got a simple table that is created as so:

String createTableSQL = (
        "CREATE TABLE ITEM_OBJECT_TABLE ("
        + "ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
        + "Item_Object blob)");
         PreparedStatement ps = connection.prepareStatement(createTableSQL);
         ps.execute();

The table stores a blob of an "item" object

Anyway. My delete method is as so:

public void deleteItem(int i)
{
    Debug.write("Attempting to delete item ID: "+i);
    try
      {
        PreparedStatement deleteStatement = databaseConnector.prepareStatement("DELETE FROM Item_Object_Table WHERE ID=?");
        deleteStatement.setInt(1, i);
        deleteStatement.execute();
      } catch (SQLException ex)
      {
        Logger.getLogger(DatabaseControl.class.getName()).log(Level.SEVERE, null, ex);
      }

}

This DOES delete the item but it does not update the integer ID not null always generated etc.

For example if my database looked like this:

ID 1 | Blob Data
ID 2 | Blob Data

And I called the deleteItem(int ID) method and parsed in the int 1 (i.e. delete ID 1) the database would look like this:

ID 2 | Blob Data

The question basically is, how do I 'update' the keys?

Answer 1

I'm a little confused by what you want here.

Are you saying that when you delete 1, you expect ID 2 to be updated to use ID 1 since ID 1 is gone?

If so, that's not how it works, and that would be fantastically inefficient and complex. You could have 200 tables referring to that ID = 2 which would all need cascading updates to rename it to 1, and it would be confusing for DB consumers as well.

You may be able to control whether or not the lowest available ID is reused instead of continuing from the highest ID up, but that's a DB specific question.