Microsoft.Data.Sqlite.SqliteException SQLite Error 1: 'AUTOINCREMENT is only allowed on an INTE-CodePudding

I've got an EF Core DbContext (that inherits from IdentityDbContext) and a bunch of migrations. It all works fine in my web app.

I've got a bunch of unit tests which spin up the DbContext against a Sqlite in-memory databse using Microsoft.EntityFrameworkCore.Sqlite.

When I run the unit tests I get the following error:

Microsoft.Data.Sqlite.SqliteException SQLite Error 1: 'AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY'.

How can I tell where this error is being caused?

script-database -Idempotent doesn't produce any instances of AUTOINCREMENT, though I presume it's using T-SQL dialect, and not Sqlite?

How can I track down the root cause of this issue?

Update

Ok, so I put the debugger on it set to break when Sqlite exception is thrown, then loaded symbols for Microsoft.Data.Sqlite, and in SqliteCommand.PrepareAndEnumerateStatements() I can see the _commandText field has the value below, which is generated by EntityFramework from a migration:

CREATE TABLE "ef_temp_AspNetRoleClaims" (
    "Id" int NOT NULL CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY AUTOINCREMENT,
    "ClaimType" nvarchar(1000) NULL,
    "ClaimValue" nvarchar(1000) NULL,
    "RoleId" uniqueidentifier NOT NULL,
    CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
);

CodePudding user response：

How can I tell where this error is being caused?

This will be when the table is being created. You need to change the CREATE SQL; to use AUTOINCREMENT you must use INTEGER not INT. i.e INTEGER PRIMARY KEY AUTOINCREMENT.

So :-

CREATE TABLE "ef_temp_AspNetRoleClaims" (
    "Id" integer NOT NULL CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY AUTOINCREMENT,
    "ClaimType" nvarchar(1000) NULL,
    "ClaimValue" nvarchar(1000) NULL,
    "RoleId" uniqueidentifier NOT NULL,
    CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
)

;

As per :-

With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key". A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly "INTEGER". Other integer type names like "INT" or "BIGINT" or "SHORT INTEGER" or "UNSIGNED INTEGER" causes the primary key column to behave as an ordinary table column with integer affinity and a unique index, not as an alias for the rowid.

CREATE TABLE - 5. ROWIDs and the INTEGER PRIMARY KEY and AUTOINCREMENT

That is AUTOINCREMENT can only be applied to an alias of the rowid.

You may wish to consider the very first paragraph and not use AUTOINCREMENT just use INTEGER PRIMARY KEY :-

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.

That is AUTOINCREMENT does not autoincrement, rather it is a rule/constraint that the value assigned will always be higher (no guarantee that it is 1 higher but typically 1 higher) than any value ever assigned.

It is less efficient as it requires an additional table sqlite_seqeunce, where the highest ever assigned value is stored for the table. The higher of this or the value in the actual table is used when determining a new value to be assigned.

Without AUTOINCREMENT, if no value is specified then the value will be determined using just the highest value in the table. This determination, if the highest allowable value (9223372036854775807) has been reached then attempts are made to use a free/unused lower value (WITH AUTOINCREMENT an SQLite FULL error results rather than using a lower free/unused value).