Good evening
I’m converting my Access database to SQLite and starting to use it. I’ve noticed several issues with dates that I’m trying to fix and also a problem with IDs. With the Access DB they are auto-incremented, with SQLite the ID field is left blank. How come ? How is the index autoincrement resolved?
In case it’s not obvious from the documentation Ivan linked to, if the Primary Key is a single-column Integer, you don’t need (and normally shouldn’t use) the AUTOINCREMENT keyword. Just make sure you’re not assigning a value to the Primary Key column and SQLite will supply one.
It’s an integer that in Access worked with autoincrement, now, once converted, the table looks like this… I don’t know how to solve it.
The thing is, it doesn’t automatically increment the ID.
I tried reading the documentation and I don’t understand how to fix the problem. I was used to access and sqlite was difficult for me.
How were these tables created? Directly by you, or by some type of translation tool? Because none of the columns have data types, nor primary keys. Now while SQLite allows this, it’s a very bad idea. You need to update or recreate the tables with defined column types (integer, text, etc.). And add Primary Key to the ID column.
1 Like
With this SQLite Database Manager (DB Browser (SQLite) just converted from Access, I was able to modify the table like this:
CREATE TABLE “Reminder” (
“ID” INTEGER,
“Object” TEXT,
“Data” TEXT,
“Message” TEXT,
“PopUpMessage” INTEGER,
PRIMARY KEY(“ID” AUTOINCREMENT)
It seems to work fine.
That is true, however this SQLite feature can reuse the numbers of deleted rows. Using the AUTOINCREMENT keyword, prevents the reuse of ROWIDs, this is the usual behavior on Access and many others.
2 Likes
While true, most people don’t need or rely on this fact, therefore AUTOINCREMENT is not needed. Even though SQLite is very fast, this feature adds a tiny bit of additional overhead, plus it creates a sequence table for each one.
Ok, I use the index as a search field instead.
I am interested in having increasing indexes, and that if I delete an entry, that index is not reusable Ex: ID=1 ID=2 ID=3 ID=4 ID=5
I delete ID=2, it must remain ID=1 ID=3 ID=4 ID=5 and if I add another
it will be ID=1 ID=3 ID=4 ID=5 ID=6.
So, my question is, is it correct as the table is set up?
i don’t get this. you’re using sqlite, not a multithreaded, million-transaction, multiuser dbms. hack the thing into life, and optimize it later, if the critical path includes this table.
Yes