Shane Dev wrote: > Why do I want store ID numbers whose values may change? Why not.
Because the name "ID" implies that its value _identifies_ the row. If it changes, it is not an ID. > Obviously, this would be bad idea if the ID column was referenced by > other column / table. In that case, I would have created a different > table such as > > CREATE TABLE fruit(id integer primary key, sort integer unique, name text); > > However, this just moves the problem from the id to the sort column. Now updates to the sort column no longer change the rowid, so no longer require moving the entire row around. > I still have to consider how to manage changes to values in the sort > column. You could make updates much easier by dropping the UNIQUE constraint on the sort column. > Apparently there is no single SQL statement which can insert a record > in to any arbitrary sort position. If you have a short list (short enough that the user can rearrange them randomly with the mouse), then just updating all values is no problem. If you have a large list, then you should use a data structure that is more suitable for random insertions. The table above is the equivalent of an array; the equivalent of a linked list would be this: CREATE TABLE fruit ( id INTEGER PRIMARY KEY, next INTEGER REFERENCES fruit, name TEXT ); id next name 42 23 apple 23 5 banana 5 69 pear 69 NULL kiwi Now insertion requires only updating one other pointer. (But querying must be done with a CTE.) Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
