SQLite Forum

Can't migrate auto-increment bigint columns
Login

Can't migrate auto-increment bigint columns

(1) By Mel Grubb (melgrubb) on 2021-01-14 16:08:51 [link] [source]

I work on a database that has several bigint (Int64) columns as Ids. We are accessing this through EFCore, and using Sqlite as our in-memory database when running integration tests. Our initial table creation works just fine, but when we make alterations to a table, EF wants to do the whole "make a new table, move the data, drop the old table, rename the new table" dance. which results in the following DDL (Anonymized):

CREATE TABLE "ef_temp_Thing" (
    "Id" bigint NOT NULL CONSTRAINT "PK_Thing" PRIMARY KEY AUTOINCREMENT,
    "Name" nvarchar(100) NOT NULL,
    "Notes" nvarchar(2000) NULL
);

When running the resulting migration, we get the error "SQLite Error 1: 'AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY'" Since this code was auto-generated by EFCore migrations, there's not much I can do about it in general, although I might be able to fix individual migrations one at a time.

If I blow away the database and all migrations, and then create a new migration up from nothing, it will work because this step won't be required. This obviously won't work long-term though, and especially not after v1.0 hits production. I'm also confused by the fact that Sqlite didn't seem to have any problem with the source table being created with an auto-incrementing bigint column in it, so why does it suddenly care when trying to run this migration?

(2) By Larry Brasfield (LarryBrasfield) on 2021-01-14 16:29:44 in reply to 1 [link] [source]

Two solutions, either of which will (likely) work:

  1. Say 'integer' rather than 'bigint'. You will still get an integer primary key up to 64 bits (as needed for its actual value.)

  2. Do not say 'AUTOINCREMENT'. That is useless except in special cases which yours is probably not. Unless you require strictly ascending key values or must permanently retire key values, AUTOINCREMENT can be eliminated without adversely impacting the automatic selection of new key values.

(3) By Mel Grubb (melgrubb) on 2021-01-14 16:37:35 in reply to 2 [link] [source]

Since the DDL is created by EFCore at runtime as part of a migration, it's not really up to us. Editing the generated code isn't possible that I know of. EFCore just generates the most verbose, specific code that it can at runtime to describe the differences in the model. I don't think it should be generating an error just for including optional words that should amount to the same thing as the defaults though.

(4) By Tim Streater (Clothears) on 2021-01-14 17:25:08 in reply to 3 [link] [source]

Saying 'bigint' will get you an integer. See:

https://www.sqlite.org/datatype3.html

and look at sections 2 and 3.1.

(6) By Keith Medcalf (kmedcalf) on 2021-01-14 17:35:09 in reply to 4 [source]

And what exactly, pray tell, does that have to do with anything?

AUTOINCREMENT can only be used with INTEGER PRIMARY KEY as it modifies the INTEGER PRIMARY KEY (rowid) selection algorithm for rowid tables.

BIGINT PRIMARY KEY is not an explicit declaration of the rowid so therefore the AUTOINCREMENT keyword cannot be used in that context.

(5) By Richard Damon (RichardDamon) on 2021-01-14 17:33:49 in reply to 3 [link] [source]

The problem is that SQLite doesn't support AUTOOINCREMENT the same as many other databases, but only I n a very specific case, and its creates direct behavior that makes it not just an 'optional' word.

It sounds like EFCore either doesn't really support SQLite or has a bug in its support for it. I would suggest filing a bug report there.

Note that Adding Autoincrement isn't just a do-nothing option, by adding it then SQLite promises that it will NEVER automatically create a duplicate value from anything it has previously generated.

(9) By Larry Brasfield (LarryBrasfield) on 2021-01-14 21:10:09 in reply to 3 [link] [source]

Since the DDL is created by EFCore at runtime as part of a migration, it's not really up to us. ...

When I advised, "Say 'integer' rather than 'bigint'", I was not referring to the generator of that DDL. You had said, "I work on a database that has several bigint (Int64) columns as Ids." I am sure that is where that troublesome 'bigint' type arose, merely replicated by the EFCore. In SQLite, the column type incantation for an up-to-64-bit integer that serves as a primary key and the rowid is 'INTEGER PRIMARY KEY'. If that went into your migration, you would not be getting that problematic column definition for your "ef_temp_Thing" table's ID column.

(7) By anonymous on 2021-01-14 18:54:55 in reply to 1 [link] [source]

Unless your table(s) contain more than 4,294,967,295 records, why not use int instead of bigint?

(8) By Tim Streater (Clothears) on 2021-01-14 20:49:30 in reply to 7 [link] [source]

If you read the refernce I gave, you will understand why.

(10) By John Gelm (gelmjw) on 2021-01-15 19:03:47 in reply to 8 [link] [source]

sqlite> create table x (i averybigint, j alittleint, k doyouseetheint, l interesting); sqlite> create table y as select * from x; sqlite> .schema CREATE TABLE x (i averybigint, j alittleint, k doyouseetheint, l interesting); CREATE TABLE y(i INT,j INT,k INT,l INT); sqlite>

There is nothing special about 'bigint'. It simply contains 'int'.

(11) By David Raymond (dvdraymond) on 2021-01-15 19:36:35 in reply to 10 [link] [source]

There is nothing special about 'bigint'. It simply contains 'int'.

The problem is when it's combined with AUTOINCREMENT. That's where it needs a specific type, and "averybitint", or "bigint" won't cut it and it must be "integer".

(12) By anonymous on 2023-04-14 10:40:15 in reply to 1 [link] [source]

Hi, we ran into the same issue. Is there any solution to this problem?

(13) By anonymous on 2023-04-14 14:18:01 in reply to 12 [link] [source]

For future readers, we have employed the strategy mentioned here as a workaround: https://jasonwatmore.com/post/2022/01/31/net-6-database-migrations-to-different-db-per-environment-sqlite-in-dev-sql-server-in-prod

whenever we make changes to the database model we need to create two sets of migrations: once for SQLite database which is used during testing, and once for our actual production database system.

(14) By Keith Medcalf (kmedcalf) on 2023-04-14 14:55:21 in reply to 12 [link] [source]

Yes.

Do not do that. Ie, if you wish to incant "INTEGER PRIMARY KEY AUTOINCREMENT" then you must spell it that way. "FATASSINT PRIMARY KEY AUTOINCREMENT" is a misspelling and will generate an error.