SQLite Migration Error: AUTOINCREMENT on BIGINT Primary Key

Issue Overview: AUTOINCREMENT Constraint on BIGINT Primary Key in SQLite Migrations

The core issue revolves around the inability to migrate tables in SQLite when the primary key column is defined as BIGINT with the AUTOINCREMENT constraint. This problem arises specifically during database migrations where Entity Framework Core (EF Core) generates a temporary table, copies data from the original table, drops the original table, and renames the temporary table to the original table’s name. The generated SQL for the temporary table includes a BIGINT primary key column with the AUTOINCREMENT constraint, which SQLite does not support. The error message returned by SQLite is: "SQLite Error 1: ‘AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY’."

The confusion stems from the fact that SQLite initially allows the creation of a table with a BIGINT primary key and AUTOINCREMENT constraint without any errors. However, during migrations, SQLite enforces stricter rules, leading to the aforementioned error. This behavior is particularly problematic for developers using EF Core for database migrations, as the migration scripts are auto-generated and cannot be easily modified.

Possible Causes: Misalignment Between SQLite and EF Core’s Type Handling

The root cause of this issue lies in the misalignment between SQLite’s type affinity system and EF Core’s type handling during migrations. SQLite employs a dynamic type system where column types are more of a suggestion rather than a strict enforcement. For example, declaring a column as BIGINT does not enforce a 64-bit integer type but instead assigns it an integer affinity. However, the AUTOINCREMENT constraint in SQLite has specific requirements: it can only be applied to columns explicitly declared as INTEGER PRIMARY KEY. This is because AUTOINCREMENT modifies the behavior of the rowid selection algorithm, which is intrinsically tied to the INTEGER PRIMARY KEY column type.

EF Core, on the other hand, generates migration scripts based on the model definitions provided by the developer. When the model defines a primary key as Int64 (equivalent to BIGINT), EF Core generates a BIGINT column with the AUTOINCREMENT constraint. This mismatch between EF Core’s generated SQL and SQLite’s constraints results in the migration error.

Another contributing factor is the lack of awareness or documentation regarding SQLite’s type affinity system and the specific requirements for using the AUTOINCREMENT constraint. Developers accustomed to other database systems may assume that BIGINT and INTEGER are interchangeable or that AUTOINCREMENT can be applied to any integer type. This assumption leads to the generation of invalid SQL during migrations.

Troubleshooting Steps, Solutions & Fixes: Aligning EF Core with SQLite’s Constraints

To resolve this issue, developers must align EF Core’s migration scripts with SQLite’s constraints. This can be achieved through several approaches, each with its own trade-offs.

1. Modify the Model to Use INTEGER Instead of BIGINT

The most straightforward solution is to modify the EF Core model to use INTEGER instead of BIGINT for primary key columns. In SQLite, INTEGER PRIMARY KEY columns are automatically assigned 64-bit integer values, which is equivalent to BIGINT in other database systems. This change ensures that the generated migration scripts comply with SQLite’s requirement for the AUTOINCREMENT constraint.

To implement this change, update the model definition to specify the primary key as INTEGER:

public class Thing
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long Id { get; set; } // Change from 'Int64' to 'long' (equivalent to INTEGER in SQLite)

    public string Name { get; set; }
    public string Notes { get; set; }
}

By making this change, EF Core will generate the correct SQL for the primary key column, avoiding the AUTOINCREMENT error during migrations.

2. Remove the AUTOINCREMENT Constraint

If the AUTOINCREMENT constraint is not strictly necessary, it can be removed from the primary key column. In SQLite, primary key columns declared as INTEGER PRIMARY KEY automatically generate unique values without the need for the AUTOINCREMENT constraint. The AUTOINCREMENT constraint is only required in specific cases where strictly ascending key values are needed or when key values must be permanently retired.

To remove the AUTOINCREMENT constraint, modify the model to exclude the DatabaseGeneratedOption.Identity attribute:

public class Thing
{
    [Key]
    public long Id { get; set; } // Remove 'DatabaseGenerated(DatabaseGeneratedOption.Identity)'

    public string Name { get; set; }
    public string Notes { get; set; }
}

This change ensures that EF Core generates a primary key column without the AUTOINCREMENT constraint, allowing the migration to proceed without errors.

3. Customize EF Core Migrations for SQLite

For scenarios where modifying the model is not feasible, developers can customize EF Core’s migration generation process to handle SQLite-specific constraints. This approach involves creating a custom migration generator that overrides the default behavior for primary key columns.

To implement a custom migration generator, create a new class that inherits from MigrationsSqlGenerator and override the Generate method to modify the generated SQL for primary key columns:

public class SqliteMigrationsSqlGenerator : MigrationsSqlGenerator
{
    public SqliteMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies)
        : base(dependencies)
    {
    }

    protected override void Generate(CreateTableOperation operation, IModel model, MigrationCommandListBuilder builder)
    {
        // Modify the primary key column to use 'INTEGER' instead of 'BIGINT'
        foreach (var column in operation.Columns)
        {
            if (column.IsPrimaryKey() && column.ClrType == typeof(long))
            {
                column.ColumnType = "INTEGER";
            }
        }

        base.Generate(operation, model, builder);
    }
}

Next, register the custom migration generator in the DbContext configuration:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlite("Data Source=mydatabase.db")
                  .ReplaceService<IMigrationsSqlGenerator, SqliteMigrationsSqlGenerator>();
}

This approach allows developers to retain the use of BIGINT in the model while ensuring that the generated migration scripts comply with SQLite’s constraints.

4. Use Separate Migrations for SQLite and Production Databases

In environments where SQLite is used for testing and another database system (e.g., SQL Server) is used for production, developers can create separate sets of migrations for each database system. This approach involves generating and maintaining two distinct sets of migration scripts: one for SQLite and one for the production database.

To implement this approach, create separate migration folders for each database system:

Migrations/
    SqliteMigrations/
        20210101000000_InitialCreate.cs
        20210102000000_AddThingTable.cs
    ProductionMigrations/
        20210101000000_InitialCreate.cs
        20210102000000_AddThingTable.cs

When running migrations, specify the appropriate migration folder based on the target database:

if (isSqlite)
{
    context.Database.Migrate("SqliteMigrations");
}
else
{
    context.Database.Migrate("ProductionMigrations");
}

This approach ensures that the migration scripts are tailored to the specific constraints of each database system, avoiding the AUTOINCREMENT error in SQLite.

5. File a Bug Report with EF Core

If none of the above solutions are feasible, developers can file a bug report with the EF Core team to address the issue at the framework level. The bug report should include a detailed description of the problem, the generated SQL, and the error message. The EF Core team may choose to modify the migration generation process to better handle SQLite’s constraints or provide additional configuration options for primary key columns.

To file a bug report, visit the EF Core GitHub repository and create a new issue: EF Core GitHub Issues.

Conclusion

The issue of migrating BIGINT primary key columns with the AUTOINCREMENT constraint in SQLite is a result of the misalignment between SQLite’s type affinity system and EF Core’s migration generation process. By understanding SQLite’s constraints and modifying the EF Core model or migration generation process, developers can resolve this issue and ensure smooth database migrations. Whether through model adjustments, custom migration generators, or separate migration sets, the solutions outlined above provide a comprehensive approach to addressing this common challenge in SQLite and EF Core development.