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 >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
