2

I'm using a SQLite database in android that auto increments the _ID field, but when I remove a row from it, the _ID has a missing number so instead of it going from 1-9 it would go 1,2,4,5,6,7,8,9, but what I want it to do is go from 1 - 8! Is there a way to do this? Sorry about the question being confusing, I couldn't figure out how to phrase it!

William L.
  • 3,846
  • 9
  • 53
  • 72
  • Maybe this is helpful: http://stackoverflow.com/a/5600067/620338 – Matt Handy Mar 13 '13 at 07:45
  • AFAIK, SQLite doesn't support that. –  Mar 13 '13 at 07:53
  • Why do you want to do that? – CL. Mar 13 '13 at 08:36
  • @CL. Because they all get inserted into a listview and I want the row_id and the listview row to be in sync for when I delete it. – William L. Mar 13 '13 at 08:37
  • I don't want to be rude at all my friend but I'm afraid "I want it to be in sync" is not good enough reason... –  Mar 13 '13 at 08:41
  • @Areks Haha, I agree... I believe my mind stopped working about 3 hours ago. Basically I wanted the row_id of the listview to be the same as the row_id for the SQLite Database so that way when I delete the row it can use the listviews id instead of having to figure out what the SQLite's ID was. If that makes any sense at all. Which is probably doesn't. – William L. Mar 13 '13 at 08:43
  • If you are using the ListView, you most likely populated it with some sort of List. So, if you save a reference for it, you can get the ID from the list. Something like: list.get(index).getId() –  Mar 13 '13 at 08:46
  • @Areks & sajmon_d Thanks guys, It was with a listview so I did what you recommended, sorry if I confused ya'll, I'm a slightly over tired :P – William L. Mar 13 '13 at 08:53

1 Answers1

0

I'm using a SQLite database in android that auto increments the _ID field, but when I remove a row from it, the _ID has a missing number so instead of it going from 1-9 it would go 1,2,4,5,6,7,8,9, but what I want it to do is go from 1 - 8! Is there a way to do this?

By one word no. This what you are describing is normal behaviour. Internal iterator of autoincrement hasn't memory, simply said when once is generated id 3, it won't generate id 3 again but next number 4. If you should have generated ids 1,2,3,4,5 and now remove row with id 5, new inserted row will have automatic id 6 not 5.

In the most of DMSs this is normal behaviour. For sure, you can achieve it when you won't use autoincrement but explicitly added ids and when it changes so you can provide update of rows programatically but this approach is not recommended and also very "sick" because in the case you would have milion rows in table, you would update all milion rows and this operation is too much time-consume and especially for devices this is not good because they have limited memory and battery with certain capacity.

And also as @Areks mentioned if you are using relational tables you must also update references and it's just not worth it in most cases.

Note: If you are populating Adapter with List of own defined Objects you can simply save reference for row_id from SQLite into each Object and then when you want to delete row from ListView just get id from row and pass it into parameter of delete method.

Simon Dorociak
  • 33,374
  • 10
  • 68
  • 106