Description
| Q | A |
|---|---|
| Version | 4.2.x |
The meaning of the AUTOINCREMENT attribute in SQLite is different from MySQL. In layman terms,
- A column doesn't ned to be declared with
AUTOINCREMENTin order to have its value auto-generated and auto-incremented. It's sufficient to declare it asINTEGER PRIMARY KEY. - Declaring it as
AUTOINCREMENTonly changes theAUTOINCREMENTbehavior/implementation (see the difference below).
When DBAL declares an SQLite column, it adds the AUTOINCREMENT keyword only if the column has the autoincrement attribute:
dbal/src/Platforms/SQLitePlatform.php
Lines 249 to 250 in bfe8fcf
When DBAL introspects an SQLite column, it marks it as autoincrement if it's effectively auto-incremented in the database, not necessarily was declared with the AUTOINCREMENT keyword:
dbal/src/Schema/SQLiteSchemaManager.php
Lines 187 to 193 in 1a30708
The inconsistency between declaration and introspection can be demonstrated with a test:
public function testPrimaryKeyAutoIncrementComparison(): void
{
$table = new Table('pk_auto_increment');
$table->addColumn('id', Types::INTEGER);
$table->setPrimaryKey(['id']);
$this->dropAndCreateTable($table);
$onlineTable = $this->schemaManager->introspectTable('pk_auto_increment');
self::assertEquals(
$table->getColumn('id')->getAutoincrement(),
$onlineTable->getColumn('id')->getAutoincrement(),
);
}The test will fail because the id column is declared as autoincrement = false in the application but it's introspected as autoincrement = true.
Difference between INTEGER PRIMARY KEY and INTEGER PRIMARY KEY AUTOINCREMENT
From the documentation:
[...] the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.
sqlite> -- without AUTOINCREMENT
sqlite> drop table if exists t;
sqlite> create table t (id integer primary key);
sqlite> insert into t values(null);
sqlite> delete from t;
sqlite> insert into t values(null);
sqlite> select * from t;
1 ← a previous value is reused
sqlite> -- with AUTOINCREMENT
sqlite> drop table if exists t;
sqlite> create table t (id integer primary key autoincrement);
sqlite> insert into t values(null);
sqlite> delete from t;
sqlite> insert into t values(null);
sqlite> select * from t;
2 ← the counter keeps increasing
The behavior of INTEGER PRIMARY KEY AUTOINCREMENT is consistent with the rest of supported database platforms and is enforced by SchemaManagerFunctionalTestCase#testPrimaryKeyAutoIncrement():