-2

SQL Lite Database: Android Programming From the previous youtube video, I have learned about the basic SQL lite database. I want to ask that after we clear the data, then you add new data into the database but the id is not reset to the beginning. The data is just added to the next row according to the previous data which is not cleared. Could I just add to the beginning row after clear the data?Thanks!

Dexter Wong
  • 113
  • 1
  • 2
  • 9
  • 2
    if IDs are autoincremented then deleting records will not reset the ids, either maintain your own Ids or you need to drop the schema altogether to re-start the count. i am not sure of your problem(which is based on a tutorial) so not answering instead putting it in comments. – ppuskar Dec 28 '14 at 11:44
  • 1
    If you care about this, you should create and maintain your own ID column. – Simon Dec 28 '14 at 11:50
  • You will learn that an ID is **not** a positional indicator (i.e.: it **won't** correspond to a ListView item), but it's rather an... identifier (like your identity card: it's a **unique code**). When you'll deal with **related tables** (that is, you'll **normalize** your dbs), you'll understand that you better **don't mess with IDs** - in order not to leave orphaned children in another table. – Phantômaxx Dec 28 '14 at 11:55
  • It's unclear what the IDs in your database represent or what you're trying to achieve. Please edit your question to provide more details. – Squonk Dec 28 '14 at 12:10

2 Answers2

0

Yes, you can do this. And NO, you shouldn't)

It is common practice to use Database just let it autoincrement and don't care in future about this. If you really need to do this you can do next:

  1. Don't put autoincrement modificator on IDs row;
  2. Next time you want to insert data in your table you should find which IDs are empty. Put your records into these ids. Than if you still have records to insert you should put it at the end of your table.

I really recommend you to leave it as is. I can't see any benefits of manually finding gaps in your table and put here values. But it will be diffucult for you to support your code in future.

UPDATE:

When you quering to show some data. You just will take all avaialable records(or some part of them it depends of your app logic) and show it to the user. Like delete the last row and update it Don't understand this. If you delete it -> it will be deleted. And next time creating row will get to the last position.

The basic idea is here: you show your data as is. You take all records and show them in list(table or some other UI View). So when you creating row, just add it to the end of list(of course you can insert it and then reload your view).

Anatol
  • 941
  • 1
  • 7
  • 13
  • The reason I want to do so is that I would like to make it like a excel file to easily store data and access them to some places. Once I don't need them, I need to erase them and start all over again. Also, what if I want to update some rows' data? Like delete the last row and update it. Will the updated data be added the next row? – Dexter Wong Dec 28 '14 at 12:39
0

Looks to be related to an older post. SQL Lite maintains a separate metadata table sqlite_sequence for sequence ids. The solution is to delete the entry for your table name from that table.

SQLite Reset Primary Key Field

Community
  • 1
  • 1
Vin
  • 124
  • 3