Thank you, Keith, for suggestion. The "t" is time index, with respect to the start of a clock. Since clock can only move forward, it is unique. I need to shift the start of the clock ....
My clock is on raster of 10 milliseconds, so are the shifts. Thus I can use your method to shift twice: by (constant - 5) then by 5. Thank you, Roman ________________________________________ From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of Keith Medcalf [kmedc...@dessus.com] Sent: Thursday, December 13, 2018 6:06 PM To: SQLite mailing list Subject: Re: [sqlite] add constant to INTEGER PRIMARY KEY You cannot do that. The PRIMARY KEY is required to be unique at each "step" along the way, especially as this is the parent in a foreign key relationship. Letting alone why anyone would want to do such a thing (which is beyond my ken), you simply have to make sure that your values are unique. constant = select (max(t)-min(t))*47 from table1 update table1 set t = t + constant; update table1 set t = t - constant + (my modification); which will work provided that (max(t)-min(t))*47 + max(t) is not greater than the value that can be stored in a 64-bit signed integer and (max(t)-min(t))*47 + min(t) is not less that the value that can be stored in a 64-bit unsigned integer and that your new t's will all fit in a 64-bit unsigned integer. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Roman Fleysher >Sent: Thursday, 13 December, 2018 15:39 >To: General Discussion of SQLite Database >Subject: [sqlite] add constant to INTEGER PRIMARY KEY > >Dear SQLiters, > >I would like to update a column declared as INTEGER PRIMARY KEY. This >column is parent to a column of another table: > >table1 ( t INTEGER PRIMARY KEY NOT NULL); > >table2 (t INTEGER PRIMARY KEY NOT NULL REFERENCES table1(t) ON DELETE >CASCADE ON UPDATE CASCADE); > >I keep PRAGMA foreign_keys = 'yes'; so that when I update table1, >table2 also gets updated. Values in column t are positive and >negative integers. When I try to add a constant I get constraint >violation: > >UPDATE table1 SET t = t + 8000; > > >Error: UNIQUE constraint failed: table1.t > > >But, if I subtract a positive constant, the update succeeds. > > >I understand that as I add or subtract a constant, numbers become >those that already exist. However, by the end of the UPDATE, all >numbers will still be unique. > > >It looks like the UPDATE happens in some order and if I can force the >order of update depending on the sign of the constant, it will always >succeed as I need. But UPDATE has no ordering mechanism because it >makes no sense to have one. > > >Does it mean I am doing it incorrectly? How to do I update? >Bracketing with BEGIN .. COMMIT did not help. > > > >Thank you, > > >Roman > >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C6c83fdc6d5434b2192d008d6614f91cd%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803391712215292&sdata=pl14VeTl%2FTVBtEOD1azIWzSspGJDv25VndQwblaiQjM%3D&reserved=0 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org https://na01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fmailinglists.sqlite.org%2Fcgi-bin%2Fmailman%2Flistinfo%2Fsqlite-users&data=02%7C01%7Croman.fleysher%40einstein.yu.edu%7C6c83fdc6d5434b2192d008d6614f91cd%7C04c70eb48f2648079934e02e89266ad0%7C1%7C0%7C636803391712225300&sdata=k%2BFBzAh8eaHmNC66MKK1BDyTPzJGBunVXhji5CJKy0M%3D&reserved=0 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
