Deleting a row in SQLite by its ordinal number

Question

There is a certain table in which the id autoincrement column got off, how can you delete a row not by its id, but by its ordinal number in the table?

@MaksimHawkins so you need to solve problems with the adapter, id entries should be left alone they have nothing to do with
Consider splitting the numbering in the data table and the numbering displayed, i.e.
Better deal with your adapter, that would go did not interfere with his work.
Ask a new question about your adapter and your problem with it.
Just for asking what you are asking for in this question desperately, "they beat their hands!"

Community spirit ♦ one · Accepted Answer · 2016-12-22T04:22:05

When working with a DBMS, there is no such thing as a record sequence number, and there are no ways to get it. With proper organization of work with the database, this information will not be required under any possible actions with the base.

ID is a unique identifier, not a means of numbering or ordering in the database. Its purpose is only to uniquely identify a record (it is guaranteed to distinguish one record from another), and if sequential addition of records leads to an increase in ID, then if you delete the record from the middle, the order will be broken. Moreover, if you delete the last entry with ID = 5, for example, the next time you add the ID of the new entry will be equal to 6 - it should be so that the structure of the database and the logic of working with it does not "break". Under no circumstances, the ID column can not get lost, each new entry automatically receives a number following the one used earlier - this is how uniqueness is achieved.

Under no circumstances may the client of the database need to manipulate IDs (change their values for a specific record, rearrange between records, etc.) - the need for such actions is a sure sign of not understanding the principles of working with the database. Attempts to do this can only lead to problems and loss (distortion) of stored data.

If you need sequence numbers when working with the database, it is very likely that you are doing something wrong, and if you persist in wanting to get these numbers, you do not understand what you are doing and how you should work with the database.

That is, you need to first learn how to work with databases, and then apply them in practice. A DBMS is a very complex and voluminous discipline, with many unobvious moments, but if you know how it works, then there will be no problems or "impossible" desires.

Also, you need not to try to delete the record in order of number, but to start learning the correct ways to work with the database.

I recommend to start learning knowledge from the book Lynn Bailey "Learning SQL" -2012. The book is written very accessible and interesting, at the same time gives a complete picture of the subject, in contrast to other literature on this subject - dry, scientific and inaccessible for understanding by the newcomer.

PS: Another answer to a similar question.

"each new entry automatically gets the next number after it was used previously" - in fact, not necessarily.
@PavelMayorov I can’t speak for all the databases, Android uses SQLite and the INTEGER PRIMARY KEY AUTOINCREMENT parameter is specified for the ID column - exactly what I wrote happens.
There are also legends that AUTOINCREMENT for a column marked with the PRIMARY KEY key can be omitted, since this key already includes such functionality, but not everyone believes in it :)
But if AUTOINCREMENT not specified, then the behavior will change.
The new record will continue to receive the next number — but not including deleted records.
And you can also not create an ID column - then it will be called rowid.
All this is in the documentation on SQLite, but nobody reads it ...

Deleting a row in SQLite by its ordinal number

1 answer 1

More articles: