sqlite migrations: replacing primary key with an autoincrement identity fails

[jhgbrt]

Migration to replace key (e.g. of type string) by an Identity key fails with unique constraint violation. The migration script adds the Id column with default value 0.

ALTER TABLE "People" ADD "Id" INTEGER NOT NULL DEFAULT 0;

Then it creates a temp table:

CREATE TABLE "ef_temp_People" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_People" PRIMARY KEY AUTOINCREMENT,
    "Ssn" TEXT NOT NULL
);

When inserting data into the corresponding temp table, the unique constraint is violated:

INSERT INTO "ef_temp_People" ("Id", "Ssn")
SELECT "Id", "Ssn"
FROM "People";

In fact, there is no need to add the Id column to the original table, and INSERT statement should omit the Id column. The code below works fine:

ALTER TABLE "People" ADD "Id" INTEGER NOT NULL DEFAULT 0;
CREATE TABLE "ef_temp_People" (
    "Id" INTEGER NOT NULL CONSTRAINT "PK_People" PRIMARY KEY AUTOINCREMENT,
    "Ssn" TEXT NOT NULL
);
INSERT INTO "ef_temp_People" ("Ssn")
SELECT "Ssn"
FROM "People";

Reproduce the problem:

ConsoleApp2.csproj:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net9.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="9.0.0">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="9.0.0" />
  </ItemGroup>

</Project>

Initial Program.cs:

using Microsoft.EntityFrameworkCore;

var context = new DataContext();

if (!context.People.Any())
{
    context.People.Add(new Person { Ssn = "12345" });
    context.People.Add(new Person { Ssn = "67890" });
    context.SaveChanges();
}

foreach (var p in context.People)
{
    Console.WriteLine(p);    
}


class DataContext : DbContext
{
    public DataContext() : base(new DbContextOptionsBuilder<DataContext>()
            .UseSqlite("Data Source=data.db")
            .Options)
    {
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        var personBuilder = modelBuilder.Entity<Person>();
        personBuilder.HasKey(p => p.Ssn);
        personBuilder.Property(p => p.Ssn).IsRequired(true);

    }
    public DbSet<Person> People { get; set; }
}

class Person
{
    public string Ssn { get; set; }

    public override string ToString() => $"Ssn = {Ssn}";
}

Initialization on the command line:

PS C:\ConsoleApp2> dotnet ef migrations list
Build started...
Build succeeded.
20241230122009_InitialCreate (Pending)
PS C:\ConsoleApp2> dotnet ef database update
Build started...
Build succeeded.
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Applying migration '20241230122009_InitialCreate'.
Done.
PS C:\ConsoleApp2> dotnet run
Ssn = 12345
Ssn = 67890

Change Program.cs to:

using Microsoft.EntityFrameworkCore;

var context = new DataContext();

if (!context.People.Any())
{
    context.People.Add(new Person { Ssn = "12345" });
    context.People.Add(new Person { Ssn = "67890" });
    context.SaveChanges();
}

foreach (var p in context.People)
{
    Console.WriteLine(p);    
}
class DataContext : DbContext
{
    public DataContext() : base(new DbContextOptionsBuilder<DataContext>()
            .UseSqlite("Data Source=data.db")
            .Options)
    {
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        var personBuilder = modelBuilder.Entity<Person>();
        personBuilder.HasKey(p => p.Id);
        personBuilder.HasIndex(p => p.Ssn).IsUnique();
        personBuilder.Property(p => p.Ssn).IsRequired(true);

    }
    public DbSet<Person> People { get; set; }
}

class Person
{
    public int Id { get; set; }
    public string Ssn { get; set; }

    public override string ToString() => $"Ssn = {Ssn}";
}

...
PS C:\ConsoleApp2> dotnet ef database update --verbose
Using project 'C:\ConsoleApp2\ConsoleApp2.csproj'.
Using startup project 'C:\ConsoleApp2\ConsoleApp2.csproj'.
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\jeroe\AppData\Local\Temp\tmphs3l5m.tmp /verbosity:quiet /nologo C:\ConsoleApp2\ConsoleApp2.csproj
dotnet msbuild /target:GetEFProjectMetadata /property:EFProjectMetadataFile=C:\Users\jeroe\AppData\Local\Temp\tmptvxjls.tmp /verbosity:quiet /nologo C:\ConsoleApp2\ConsoleApp2.csproj
Build started...
dotnet build C:\ConsoleApp2\ConsoleApp2.csproj /verbosity:quiet /nologo /p:PublishAot=false

Build succeeded.
0 Warning(s)
0 Error(s)

Time Elapsed 00:00:01.52

Build succeeded.
dotnet exec --depsfile C:\ConsoleApp2\bin\Debug\net9.0\ConsoleApp2.deps.json --additionalprobingpath C:\Users\jeroe.nuget\packages --additionalprobingpath "C:\Program Files (x86)\Microsoft Visual Studio\Shared\NuGetPackages" --runtimeconfig C:\ConsoleApp2\bin\Debug\net9.0\ConsoleApp2.runtimeconfig.json C:\Users\jeroe.dotnet\tools.store\dotnet-ef\9.0.0\dotnet-ef\9.0.0\tools\net8.0\any\tools\netcoreapp2.0\any\ef.dll database update --assembly C:\ConsoleApp2\bin\Debug\net9.0\ConsoleApp2.dll --project C:\ConsoleApp2\ConsoleApp2.csproj --startup-assembly C:\ConsoleApp2\bin\Debug\net9.0\ConsoleApp2.dll --startup-project C:\ConsoleApp2\ConsoleApp2.csproj --project-dir C:\ConsoleApp2\ --root-namespace ConsoleApp2 --language C# --framework net9.0 --nullable --working-dir C:\ConsoleApp2 --verbose
Using assembly 'ConsoleApp2'.
Using startup assembly 'ConsoleApp2'.
Using application base 'C:\ConsoleApp2\bin\Debug\net9.0'.
Using working directory 'C:\ConsoleApp2'.
Using root namespace 'ConsoleApp2'.
Using project directory 'C:\ConsoleApp2'.
Remaining arguments: .
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Finding DbContext classes in the project...
Found DbContext 'DataContext'.
Finding application service provider in assembly 'ConsoleApp2'...
Finding Microsoft.Extensions.Hosting service provider...
No static method 'CreateHostBuilder(string[])' was found on class 'Program'.
No application service provider was found.
Using context 'DataContext'.
Finding design-time services referenced by assembly 'ConsoleApp2'...
Finding design-time services referenced by assembly 'ConsoleApp2'...
No referenced design-time services were found.
Finding design-time services for provider 'Microsoft.EntityFrameworkCore.Sqlite'...
Using design-time services from provider 'Microsoft.EntityFrameworkCore.Sqlite'.
Finding IDesignTimeServices implementations in assembly 'ConsoleApp2'...
No design-time services were found.
Creating DbConnection.
Created DbConnection. (15ms).
Migrating using database 'main' on server 'data.db'.
Creating DbConnection.
Created DbConnection. (3ms).
Opening connection to database 'main' on server 'data.db'.
Opened connection to database 'main' on server 'C:\ConsoleApp2\data.db'.
Disposing connection to database 'main' on server 'data.db'.
Disposed connection to database 'main' on server 'data.db' (2ms).
Opening connection to database 'main' on server 'data.db'.
Opened connection to database 'main' on server 'C:\ConsoleApp2\data.db'.
Acquiring an exclusive lock for migration application. See https://aka.ms/efcore-docs-migrations-lock for more information if this takes too long.
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (4ms).
Initialized DbCommand for 'ExecuteScalar' (8ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT() FROM "sqlite_master" WHERE "name" = '__EFMigrationsLock' AND "type" = 'table';
Executed DbCommand (17ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT() FROM "sqlite_master" WHERE "name" = '__EFMigrationsLock' AND "type" = 'table';
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (2ms).
Initialized DbCommand for 'ExecuteScalar' (2ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT OR IGNORE INTO "__EFMigrationsLock"("Id", "Timestamp") VALUES(1, '2024-12-30 13:06:02.2638872+00:00');
SELECT changes();
Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT OR IGNORE INTO "__EFMigrationsLock"("Id", "Timestamp") VALUES(1, '2024-12-30 13:06:02.2638872+00:00');
SELECT changes();
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (1ms).
Initialized DbCommand for 'ExecuteNonQuery' (3ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
"MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
"ProductVersion" TEXT NOT NULL
);
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
"MigrationId" TEXT NOT NULL CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY,
"ProductVersion" TEXT NOT NULL
);
Beginning transaction with isolation level 'Unspecified'.
Began transaction with isolation level 'Serializable'.
Creating DbConnection.
Created DbConnection. (2ms).
Opening connection to database 'main' on server 'data.db'.
Opened connection to database 'main' on server 'C:\ConsoleApp2\data.db'.
Disposing connection to database 'main' on server 'data.db'.
Disposed connection to database 'main' on server 'data.db' (0ms).
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (0ms).
Initialized DbCommand for 'ExecuteScalar' (1ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT() FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT() FROM "sqlite_master" WHERE "name" = '__EFMigrationsHistory' AND "type" = 'table';
Creating DbCommand for 'ExecuteReader'.
Created DbCommand for 'ExecuteReader' (0ms).
Initialized DbCommand for 'ExecuteReader' (1ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
Closing data reader to 'main' on server 'C:\ConsoleApp2\data.db'.
A data reader for 'main' on server 'C:\ConsoleApp2\data.db' is being disposed after spending 3ms reading results.
Applying migration '20241230123240_ReplaceKeyByIdentity'.
The migration operation 'PRAGMA foreign_keys = 0;
' from migration 'ReplaceKeyByIdentity' cannot be executed in a transaction. If the app is terminated or an unrecoverable error occurs while this operation is being executed then the migration will be left in a partially applied state and would need to be reverted manually before it can be applied again. Create a separate migration that contains just this operation.
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (1ms).
Initialized DbCommand for 'ExecuteNonQuery' (2ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "People" ADD "Id" INTEGER NOT NULL DEFAULT 0;
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "People" ADD "Id" INTEGER NOT NULL DEFAULT 0;
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Initialized DbCommand for 'ExecuteNonQuery' (1ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE UNIQUE INDEX "IX_People_Ssn" ON "People" ("Ssn");
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE UNIQUE INDEX "IX_People_Ssn" ON "People" ("Ssn");
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Initialized DbCommand for 'ExecuteNonQuery' (1ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "ef_temp_People" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_People" PRIMARY KEY AUTOINCREMENT,
"Ssn" TEXT NOT NULL
);
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "ef_temp_People" (
"Id" INTEGER NOT NULL CONSTRAINT "PK_People" PRIMARY KEY AUTOINCREMENT,
"Ssn" TEXT NOT NULL
);
Creating DbCommand for 'ExecuteNonQuery'.
Created DbCommand for 'ExecuteNonQuery' (0ms).
Initialized DbCommand for 'ExecuteNonQuery' (1ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "ef_temp_People" ("Id", "Ssn")
SELECT "Id", "Ssn"
FROM "People";
Failed executing DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "ef_temp_People" ("Id", "Ssn")
SELECT "Id", "Ssn"
FROM "People";
Disposing transaction.
Creating DbCommand for 'ExecuteScalar'.
Created DbCommand for 'ExecuteScalar' (1ms).
Initialized DbCommand for 'ExecuteScalar' (2ms).
Executing DbCommand [Parameters=[], CommandType='Text', CommandTimeout='30']
DELETE FROM "__EFMigrationsLock";
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DELETE FROM "__EFMigrationsLock";
Closing connection to database 'main' on server 'C:\ConsoleApp2\data.db'.
Closed connection to database 'main' on server 'data.db' (3ms).
'DataContext' disposed.
Disposing connection to database 'main' on server 'data.db'.
Disposed connection to database 'main' on server 'data.db' (0ms).
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 19: 'UNIQUE constraint failed: ef_temp_People.Id'.
at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader()
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.Execute(IReadOnlyList1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean beginTransaction, Boolean commitTransaction, Nullable1 isolationLevel) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.<>c.<ExecuteNonQuery>b__3_1(DbContext _, ValueTuple6 s)
at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IReadOnlyList1 migrationCommands, IRelationalConnection connection, MigrationExecutionState executionState, Boolean commitTransaction, Nullable1 isolationLevel)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.MigrateImplementation(DbContext context, String targetMigration, MigrationExecutionState state, Boolean useTransaction)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.<>c.b__20_1(DbContext c, ValueTuple4 s) at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String connectionString, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
SQLite Error 19: 'UNIQUE constraint failed: ef_temp_People.Id'.

### Include provider and version information

EF Core version:
Database provider: (Microsoft.EntityFrameworkCore.SqLite 9.0.0)
Target framework: (NET 9.0)
Operating system:
IDE: (e.g. Visual Studio 2022 17.13.0 Preview 2.1)