When deleting a record, the entire row is deleted, respectively, the order in the _id column _id broken. How can I update the auto-increment column? Or how it can be especially deleted so that this column is updated itself? Maybe there is some tricky way? :)

  • I understand correctly that you, for example, have entries with id: 1, 2, 3, 4; then you delete the record with id 3, you remain in the database 1,2,4, and you want to remain 1,2,3? - Vladyslav Matviienko
  • 3
    Perhaps a duplicate question: How to assign auto_increment value to a variable? - cheops
  • Specifically, the solution to your problem . Kostyulnyy - write to the model more ID records from the database, then retrieve it when required. The right thing is to completely revise the whole architecture and work either with classes designed for SQL ( Cursor , ContentProvider , CursorAdapter ), or, if you want so much with the model, then with ORM. The second option is preferable. From the ORM I recommend first to consider Realm - pavlofff

1 answer 1

Manually change the ID values ​​and generally interfere with the autogeneration of this column in the database tables does not follow at all , otherwise your entire database will go straight to AD.

There is no tricky way either, each new record is assigned an ID sequentially, the ID of deleted records is no longer used.

ID is the primary key in the table and so the uniqueness of the identifiers of records in the tables is ensured - one of the basic principles of building the DBMS. All interactions with records in the database are built on this primary key and if it is changed, chaos will begin.

Interference in the values ​​of identifiers threatens in particular with the fact that all possible connections of the database will be violated - one to one, one to many, and many to many. Perhaps you specifically for some reason (most likely due to a distant acquaintance with the principles of the DBMS) and do not use these connections, but because of this no one will allow such blasphemy to be in the database.

I recommend that you familiarize yourself with the principles and device of the database and such a discipline as a DBMS, so that such a "heresy" no longer occurs in your head :). For example, a very accessible book to get you started: L. Bailey - "Learning SQL" - 2012 , Series "O'Reilly Bestsellers" - finding it is quite easy, even for free.

  • and if I change the architecture to SimpleCursorAdapter, then deleting the row will not cause chaos in the _id column? in other words, is it really possible to delete from 1,2,3,4,5 row with id 3 in this type of adapter, the column itself is generated as 1,2,3,4 and not 1,2,4,5? - Flippy
  • @ SergeyGrushin Classes designed to work with SQLLite , in particular, the CursorAdapter return, in addition to the position, the record ID, you do not have to change anything in the ID, you just get the correct ID value from the listener in the fourth parameter onItemClick(AdapterView<?> parent, View view, int position, long id) and you can use it in your logic. ArrayAdapter for obvious reasons (he knows nothing about the database and ID) does not return anything with this parameter. - pavlofff
  • ie, having received an id from the listener, I can insert it into the query to the database for deletion and everything will be deleted normally? - Flippy
  • @ SergeyGrushin Yes, exactly. For this ID and returns to the listener - pavlofff
  • excellent, and one more question - I use my own adapter and model ... Can I keep everything, but adjust the cursor correctly? - Flippy