Hi Igor, Homework exercise? No, this is purely a hobby project in my free time. My goal is see how much logic can moved from application code to the database.
Why do I want store ID numbers whose values may change? Why not. 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 sqlite> .sch fruit 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. I still have to consider how to manage changes to values in the sort column. Apparently there is no single SQL statement which can insert a record in to any arbitrary sort position. Even if I use the stepped approach (fruit.sort = 100, 200, 300 ...) or define sort as real unique, I will still need to determine if it is necessary to reset the gaps between sort column values. Peter Nichvolodov's trigger solution ( https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html) is elegant, but might be slow if the table had many entries. On 22 November 2017 at 00:11, Igor Korot <ikoro...@gmail.com> wrote: > Simon, > > On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin <slav...@bigfraud.org> > wrote: > > > > > > On 21 Nov 2017, at 10:09pm, Jens Alfke <j...@mooseyard.com> wrote: > > > >>> On Nov 21, 2017, at 1:56 AM, R Smith <rsm...@rsweb.co.za> wrote: > >>> > >>> That assumes you are not starting from an integer part (like 4000) and > hitting the exact same relative insert spot every time, which /can/ happen, > but is hugely unlikely. > >> > >> Not to beat this into the ground, but: it’s not that unlikely. Let’s > say you sort rows by date. You’ve already got some entries from 2015 in > your database, and some from 2017. Someone now inserts 60 entries from > 2016, and to be ‘helpful’, they insert them in chronological order. Wham, > this immediately hits that case. > > > > Yes, if you use this method, you do need to renumber them every so > often. You assess this when you’re working out (before + after) / 2, and > you do it using something like the double-UPDATE command someone came up > with earlier. > > > > But that just brings us back to the question of why OP wants to store ID > numbers which might change. > > Homework exercise? > Stupid requirements? > > Thank you. > > > > > Simon. > > _______________________________________________ > > 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
