AUTOINCREMENT is not implemented in create statements

[JamesMcIntosh]

The construction of auto incrementing primary keys is confusing and does not expose allow for auto incrementing keys without reuse. https://www.sqlite.org/autoinc.html

Currently if you specify a PrimaryKey with the auto attribute

@PrimaryKey(auto: true)
final int id;

It is generated as

id INTEGER PRIMARY KEY

It should be

id INTEGER PRIMARY KEY AUTOINCREMENT

I would recommend handling the creation of integer columns in create.dart like this:

String composeCreateColumn(final CreateColumn col) {
...
if (col is CreateInt) {
  if (col.isPrimary) {
    if (col.autoIncrement) {
      sb.write(' INTEGER PRIMARY KEY AUTOINCREMENT');
    } else {
      sb.write(' INTEGER PRIMARY KEY');
    }
  } else {
    sb.write(' INT');
  }
}
...
}

[jaumard]

The doc you mention also says The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

And: In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

The only advantage of the keyword is If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

But most people doesn't care about this, they just want an automatic ID.

So the current way of working is the right one, and jaguar doesn't actually have anything to support both AUTOINCREMENT and the ROWID

[JamesMcIntosh]

The SqlLite guys are a very performance driven, I'm pretty sure you wouldn't notice it in a normal app.

The advantage of this behaviour comes when you are missing foreign key constraints or haven't enabled them (foreign key constraints are set to off by the default in SqlLite). If don't clean up related data correctly when you delete rows then you can have data magically appear which is from something you have deleted. You can then be in a situation where you can't cleanup the database without starting from scratch where if you had the incrementing keys not being reused then you would be able to delete the erroneous data and carry on.

[jbrechbuehl]

Hi,

I would also like to be able to use the AUTOINCREMENT keyword.

Thank you

[JamesMcIntosh]

@jbrechbuehl As a hack when creating the tables you can add AUTOINCREMENT to the create statement by replacing: INTEGER PRIMARY KEY NOT NULL with INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL

If you have existing tables then this update statement should make it open with AUTOINCREMENT enabled as the tables structure is reanalysed on startup. tx.rawUpdate( "UPDATE sqlite_master SET sql = replace(sql, 'INTEGER PRIMARY KEY NOT NULL', 'INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL') WHERE type = 'table'");