Re: [sqlite] Autoincrement sequence not updated by UPDATE

> To: SQLite mailing list > Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE > > On Tue, 1 Nov 2016 11:01:24 + > Simon Slavin <slav...@bigfraud.org> wrote: > > > attempts to change a value in that column using UPDATE always > > generat

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On Tue, 1 Nov 2016 11:01:24 + Simon Slavin wrote: > attempts to change a value in that column using UPDATE always > generate an error. I didn't know that. I looked it up. Apparently > Microsoft's SQLSERVER blocks it Blocks but does not prevent.

Re: [sqlite] Autoincrement sequence not updated by UPDATE

sqlite> create table tbl1 ...> ( ...> id integer primary key autoincrement, ...> someOtherfield, ...> yetAnotherField ...> ); sqlite> create trigger trg_imInChargeAndSayNoAutoincrementUpdates ...> before update of id on tbl1 ...> begin ...> select raise(abort, 'Bad

Re: [sqlite] Autoincrement sequence not updated by UPDATE

| ++--+ ++---+ | id | v | ++---+ | 3 | three | | 10 | one | | 30 | two | ++---+ -Original Message- From: Simon Slavin Sent: Tuesday, November 01, 2016 1:50 PM To: SQLite mailing list Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE On 1 Nov 2016

Re: [sqlite] Autoincrement sequence not updated by UPDATE

| ++---+ | 3 | three | | 10 | one | | 30 | two | ++---+ -Original Message- From: Simon Slavin Sent: Tuesday, November 01, 2016 1:50 PM To: SQLite mailing list Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE On 1 Nov 2016, at 11:44am, Andy Ling

Re: [sqlite] Autoincrement sequence not updated by UPDATE

>> It remembers.. >Ah, neat. Thanks for the testing. As a slight aside. It also never resets the value. We had a problem where the number of inserts had incremented the AUTO INCREMENT value to MAXINT (it took a few years). It then stops. We fixed it by changing id to a BIGINT, but you can

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 1 Nov 2016, at 11:44am, Andy Ling wrote: > It remembers.. Ah, neat. Thanks for the testing. And the "show create table" command you used makes it clear that the engine keeps a record for the table. Apparently a single value for the table's primary key rather

Re: [sqlite] Autoincrement sequence not updated by UPDATE

[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Tue 01 November 2016 11:42 To: SQLite mailing list Subject: Re: [sqlite] Autoincrement sequence not updated by UPDATE On 1 Nov 2016, at 11:14am, Andy Ling <andy.l...@s-a-m.com> wrote: > MySQL lets y

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 2016/11/01 1:01 PM, Simon Slavin wrote: On 1 Nov 2016, at 10:45am, R Smith wrote: D - Horrible if you up some key value significantly and then update it back down, because there is no way the Autoinc value should *EVER* be able/allowed to come back down. It's a

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 1 Nov 2016, at 11:14am, Andy Ling wrote: > MySQL lets you fiddle. I don't have MySQL. To satisfy my curiosity, could you try this: create table tt (id int NOT NULL AUTO_INCREMENT, v TEXT, PRIMARY KEY(id)) ; insert into tt (v) VALUES("one"); update tt set id=10

Re: [sqlite] Autoincrement sequence not updated by UPDATE

> Which, according to GB, is what some other SQL engines do: attempts to change > a value > in that column using UPDATE always generate an error. I didn't know that. > I looked it up. > Apparently Microsoft's SQLSERVER blocks it, but I was unable to find > anything mentioning > how any of

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 11/1/16 7:01 AM, Simon Slavin wrote: On 1 Nov 2016, at 10:45am, R Smith wrote: D - Horrible if you up some key value significantly and then update it back down, because there is no way the Autoinc value should *EVER* be able/allowed to come back down. It's a one-way

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 1 Nov 2016, at 10:45am, R Smith wrote: > D - Horrible if you up some key value significantly and then update it back > down, because there is no way the Autoinc value should *EVER* be able/allowed > to come back down. It's a one-way street. This is an additional

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 2016/11/01 11:52 AM, Simon Slavin wrote: Unfortunately the response is going to be along the lines of "We can't do this for compatibility reasons because there might be a program out there that does it.". And rightly so... There is no reason to use Autoincrement other to be sure newly

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 1 Nov 2016, at 6:56am, GB wrote: > And that is why several SQL Engines prevent AUTOINCREMENT columns from being > UPDATEd. I think that would be a good fix in SQLite's case. If you're relying on SQLite to generate these unique numbers for you then you shouldn't then change

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 28 Oct 2016, at 12:47, Simon Slavin wrote: It guess it comes down to what one wants from "INTEGER PRIMARY KEY AUTOINCREMENT". If the requirement is only-ever-increasing then this is a bug. The behaviour described at https://sqlite.org/autoinc.html seems to match this requirement:

Re: [sqlite] Autoincrement sequence not updated by UPDATE

And that is why several SQL Engines prevent AUTOINCREMENT columns from being UPDATEd. I think it should be fixed in the Docs, not in Code. Richard Hipp schrieb am 31.10.2016 um 18:56: Note that the implied purpose of AUTOINCREMENT is to generate a unique and immutable identifier. Running

Re: [sqlite] Autoincrement sequence not updated by UPDATE

> It guess it comes down to what one wants from "INTEGER PRIMARY KEY AUTOINCREMENT" What I would want, ...expect, is that a primary key autoincrement column would be left completely alone. And if was altered, it was altered on accident. I always thought "integer primary key" was synonymous with

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On 10/27/16, Adam Goldman wrote: > I expected the test case below to print 5679, but it prints 1235 > instead. I tested under a few versions including 3.15.0. It's a bit of a > corner case and I worked around it in my application, but I guess it's a > bug. > > CREATE TABLE foo

Re: [sqlite] Autoincrement sequence not updated by UPDATE

SQLite version 3.14.0 2016-07-26 15:17:14 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT); sqlite> INSERT INTO foo (bar) VALUES(1234); sqlite>

Re: [sqlite] Autoincrement sequence not updated by UPDATE

Works here; SQLite version 3.13.0 2016-05-18 10:57:30 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT); sqlite> INSERT INTO foo (bar) VALUES(1234);

Re: [sqlite] Autoincrement sequence not updated by UPDATE

After line: UPDATE foo SET bar=5678; put this sql command: COMMIT; If you execute all statements in one sql (except last), they are executed in one transaction. Regards Radovan Adam Goldman je 27.10.2016 ob 11:52 napisal: Hi, I expected the test case below to print 5679, but it prints 1235

[sqlite] Autoincrement sequence not updated by UPDATE

Hi, I expected the test case below to print 5679, but it prints 1235 instead. I tested under a few versions including 3.15.0. It's a bit of a corner case and I worked around it in my application, but I guess it's a bug. CREATE TABLE foo (bar INTEGER PRIMARY KEY AUTOINCREMENT); INSERT INTO foo