What is the best way to change keys in a SQLite table? [closed]

It's difficult to tell what is being asked here. This que开发者_StackOverflow中文版stion is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

I have hierarchical data stored in a treeview which is populated from an SQLite DB. Depending on what the user does, the tree nodes' ids can change (if they add a new node before node 001, all nodes from 001 to the end will have their key increased by 1).

This proves troubling when updating the database - because updating the value for 001 may update the wrong node - since one was added to the tree.

I have found a way to get around this, but it seems pretty dumb to me. I just purge the table before I update, deleting all the records, and then I re-add them. But this seems bad because if there is a bug somewhere along adding them, all the remaining items will not be added.

The only other workaround I can think of is when adding nodes to the tree, I could just give the new node a key 00x (where x is the number of nodes in the tree). but then that means that the keys are out of order.

What is the best way to do this? Probably not the way I have it now.

---

you could use a separate ordering field. as far as i can see, the only reason you are changing IDs is to change ordering - so put the ordering elsewhere.

(you might even go wild and only require order to be unique amongst the children of a particular node, but think about exactly how you're building this with SQL queries before doing so)

---

Is it a big deal to just tack on to the end? Why does it have to be in front or in the middle? It seems your numbers mean nothing except ordering.

Why not create a Primary key that you can just add on to the end, These numbers will never change so if you have 1-10 and you add to it, the new one is 11. Then you can do your increasing piece for the columns you have, i'm assuming you use that specifically to order. This way you can order using what you have, but when updating you use the primary key where everything is forced to be unique and the number never changes once assigned.