Open JamesMcIntosh opened 4 years ago
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
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.
Hi,
I would also like to be able to use the AUTOINCREMENT keyword.
Thank you
@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'");
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
It is generated as
It should be
I would recommend handling the creation of integer columns in
create.dart
like this: