add constant to INTEGER PRIMARY KEY

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

add constant to INTEGER PRIMARY KEY

Roman Fleysher
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: add constant to INTEGER PRIMARY KEY

Keith Medcalf

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-
>[hidden email]] 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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: add constant to INTEGER PRIMARY KEY

Roman Fleysher
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 [[hidden email]] on behalf of Keith Medcalf [[hidden email]]
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-
>[hidden email]] 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
>[hidden email]
>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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: add constant to INTEGER PRIMARY KEY

Simon Slavin-3
In reply to this post by Roman Fleysher
On 13 Dec 2018, at 10:39pm, Roman Fleysher <[hidden email]> wrote:

> UPDATE table1 SET t =  t + 8000;
>
>
> Error: UNIQUE constraint failed: table1.t

This means there's an actual example where one value for t is 8000 greater than another.  Use a larger constant.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: add constant to INTEGER PRIMARY KEY

James K. Lowden
In reply to this post by Keith Medcalf
On Thu, 13 Dec 2018 16:06:04 -0700
"Keith Medcalf" <[hidden email]> wrote:

> You cannot do that.  The PRIMARY KEY is required to be unique at each
> "step" along the way,

For the OP's benefit, this is  longstanding, er, idiosyncrasy of
SQLite.  It does not conform to the SQL standard.  

The SQL rule is there are no "steps".  Each statement is atomic; it is
either executed entirely or not at all.  The contraints on the table
apply to the *user-visible* view of the data: before the update begins,
and after it finishes. They do not apply during the time the DBMS is
updating the data, however it chooses to do so.  

> Letting alone why anyone would want to do such a thing

The following is legal in SQL for a unique column,

        update T set a = a +1

*regardless* of the values in "a".  SQLite is the only DBMS I'm aware
of that prevents the update if "a" is unique and holds consecutive
values.  

That kind of update is neither absurd nor unusual.  An obvious example
is updating all records by 1 day (perhaps because originally
misrecorded).  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users