Shane. If you're dead set on paying the cost for brute force mid table id insertion, take a look at INSTEAD OF triggers:
https://sqlite.org/lang_createtrigger.html Your example would look like this: CREATE VIEW fruit_ins AS SELECT * FROM fruit; CREATE TRIGGER fruit_ins INSTEAD OF INSERT ON fruit_ins BEGIN UPDATE fruit SET id = -id-1 WHERE id >= NEW.id; UPDATE fruit SET id = -id WHERE id < 0; INSERT INTO fruit VALUES (NEW.id,NEW.fruit); END; INSERT INTO fruit_ins VALUES (2,'Banana'); [Caveat: don't use intentional negative Id's in production without revising this code first!] On Mon, Nov 20, 2017 at 1:31 PM, Shane Dev <devshan...@gmail.com> wrote: > Hi Ryan, > > Nice trick - changing the sign of ID. I agree that changing an existing > record's ID value would cause problems for any other field / table that > referenced this key. > > If I used your idea of adding a SortOrder column incremented in steps of x, > as you suggested, the gaps would start to disappear after many inserts > between existing records. I suppose the gaps could be reset by writing > program to call sqlite3_exec with > > select * from fruit order by SortOrder; > > and increment a RowCount variable each time the callback was triggered, > then update fruit with something like > > update fruit set SortOrder = RowCount*x where id = [id of the row when it's > callback was triggered]; > > I would to prefer to avoid this solution because it involves mutable state > (the RowCount variable) which is the "root of all evil" (bugs). Is there an > SQL statement which could reset the gaps back to x? > > > On 20 November 2017 at 17:12, R Smith <rsm...@rsweb.co.za> wrote: > > > This question pops up from time to time. > > > > I will show a correct query script to achieve this below, but I want to > > emphasize what others have said: Data in an RDBMS has no intrinsic order, > > it's all SETs, and if you artificially bestow order to the data itself > (as > > opposed to the eventual output) then you are doing something that's very > > bad in database design. > > > > To be specific, if the fruit in your DB needs ORDER as a property, best > is > > to add a column called SortOrder or FruitOrder or the like. In this > column > > you can then assign the values automatically in steps of 10 or 100, so > you > > end up with a table like: > > id | fruit | SortOrder > > 1 | Apple | 100 > > 2 | Pear | 200 > > 3 | Kiwi | 300 etc... > > > > > > Then inserting: > > INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150); > > > > is simply trivial. (The 150 can be computed from splitting the difference > > between the precedent and decedent). Non-Integer is best. > > > > Eventually though, you might need to do maintenance and reset the gaps or > > such. > > > > Anyway, enough preaching - this query script will fix your Situation in > > SQLite very fast: > > > > UPDATE fruit SET id = -id-1 WHERE id >= 2; > > UPDATE fruit SET id = -id WHERE id < 0; > > INSERT INTO fruit (2,'Banana'); > > > > > > Another way: > > > > UPDATE fruit SET id = -(id * 100); > > UPDATE fruit SET id = -id WHERE id < 0; > > INSERT INTO fruit (150,'Banana'); > > > > > > The reason why this is bad? Mostly a primary Key serves as a lookup for > > other tables linking to a very specific record. Imagine your query that > > added fruit to recipes where needed has the fruit's primary keys > shuffled, > > the next day will see some really weird recipes when Banana ends up where > > Pear was intended. Next you'll want to insert Watermelon... :) > > > > Cheers, > > Ryan > > > > > > On 2017/11/19 10:37 PM, Shane Dev wrote: > > > >> Let's say I have a table of fruit - > >> > >> sqlite> .sch fruit > >> CREATE TABLE fruit(id integer primary key, name text); > >> > >> with some entries - > >> > >> sqlite> select * from fruit; > >> id|name > >> 1|apple > >> 2|pear > >> 3|kiwi > >> > >> Is there an easy way to insert 'banana' between apple and pear while > still > >> maintaining a consistent order of the ID field? > >> > >> desired result - > >> > >> sqlite> select * from fruit; > >> 1|apple > >> 2|banana > >> 3|pear > >> 4|kiwi > >> _______________________________________________ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
