AUTOINCREMENT keyword missing from auto increment columns

[Former user]

Originally reported on Google Code with ID 62

What steps will reproduce the problem?
1. Create a new table
2. Select 'auto increment' and 'primary key' for the id field
3.

What is the expected output? What do you see instead?

Expected output is 
CREATE TABLE test(id INTEGER PRIMARY KEY NOT NULL, name TEXT)

We should see 
CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT)

These result in two different things.

In the first, the table won't maintain an auto-index, it will just use the next integer
after the highest EXISTING index, instead of the next integer after the highest EVER
CREATED.  Therefore if someone deletes the last record and inserts another, it will
have the id of the last deleted record- not what would be expected from auto-increment.

See: http://www.sqlite.org/autoinc.html


What version of the product are you using? On what operating system?

v1.8.7
Mac OSX 10.6.8
PHP Version 5.3.6

Please provide any additional information below.

Reported by ykessler on 2011-10-13 17:40:52

[Former user]

``` Correction: What I listed as 'Expected output' is the ACTUAL output.

should read:

Actual output is: CREATE TABLE test(id INTEGER PRIMARY KEY NOT NULL, name TEXT)

Expected output is: CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, name TEXT) ```

Reported by `ykessler` on 2011-10-13 17:44:10

[Christopher Kramer]

Thanks for your bug report, sorry for the late reply.

I can reproduce this issue in the current development version.
The "autoincrement"-form-field is simply ignored...

I'll fix this soon.

Reported by crazy4chrissi on 2012-10-30 22:42:21 - Status changed: Accepted

[Christopher Kramer]

This issue was closed by revision r250.

Reported by crazy4chrissi on 2012-10-31 23:56:41 - Status changed: Fixed

[Christopher Kramer]

Just fixed that in SVN.

Just a note on AUTOINCREMENT: In SQLite, AUTOINCREMENT is only possible for INTEGER
PRIMARY KEYs. Primary keys are always NOT NULL. If you do not specify AUTOINCREMENT
and INSERT into such a table without providing value for the primary key column, SQLite
will generate a primary key anyway, just like if AUTOINCREMENT was specified.
So it does not make much of a difference.

Well, it makes _some_ difference:
The short version is that with AUTOINCREMENT, the primary key will always increase
and rowids will never be reused. Without, SQlite will randomly try to reuse unused
rowids when the maximum rowid limit is reached. With AUTOINCREMENT specified, it will
give an error when reaching the limit.
Read the slightly longer version here: https://www.sqlite.org/autoinc.html

I also adjusted the JS so autoincrement can only be chosen for INTEGER PRIMARY KEY
columns.

Reported by crazy4chrissi on 2012-11-01 00:07:10

[Christopher Kramer]

well, maybe I should have reread what you wrote in your bug report before commenting
;-)

So you already figured out the differences. Sorry for the repetition.

Reported by crazy4chrissi on 2012-11-01 00:10:15