> It guess it comes down to what one wants from "INTEGER PRIMARY KEY

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
__rowid__.  What application would want to mess with that?

On Mon, Oct 31, 2016 at 12:56 PM, Richard Hipp <d...@sqlite.org> wrote:

> On 10/27/16, Adam Goldman <ad...@pobox.com> 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.
> >
> > INSERT INTO foo (bar) VALUES(1234);
> > UPDATE foo SET bar=5678;
> > DELETE FROM foo;
> > SELECT * FROM foo;
> Yes, this is a discrepancy between the implementation and the
> documentation.  But the implementation of AUTOINCREMENT has *never*
> before modified the content of the sqlite_sequence table on an UPDATE,
> since the AUTOINCREMENT feature was first introduced in 2002 (SQLite
> version 2.5.2). For 14 years, AUTOINCREMENT has always worked as it
> does in 3.15.0.  If we "fix" the implementation now, we run a risk of
> breaking some of the millions of applications that use AUTOINCREMENT.
> For that reason, we have tentatively decided to update the
> documentation rather than the code.
> Note that the implied purpose of AUTOINCREMENT is to generate a unique
> and immutable identifier.  Running an UPDATE on an immutable
> identifier breaks the contract.  Even though this contract was not
> previously stated in the documentation, apparently most people
> understood it because this issue has never come up before in 14 years
> of heavy use.
> Thanks for pointing out the problem.
> --
> D. Richard Hipp
> d...@sqlite.org
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
sqlite-users mailing list

Reply via email to