I have an sqlite DB. I'm using SQLite3 extension to connect to it in PHP.
Database was from a program I used until now for invoices. I took out sqlite file from that program and wrote own web based inteface.
Everything seems to be ok, except when I created new table to existing database.
Database was created, I could add new entries and lastInsertRowid() gave back proper Last Insert IDs.
However, when I started to select entries from new table, all IDs (Primary key) were empty.
I opened my database with a SQLite Database browser and something is odd.
Maybe first I will give my db schema:
CREATE TABLE IF NOT EXISTS koszty
(
id NUMERIC PRIMARY KEY DESC,
data NUMERIC,
nazwa TEXT,
wartosc_netto NUMERIC UNSIGNED,
podatek NUMERIC UNSIGNED,
typ NUMERIC UNSIGNED,
filename TEXT,
opis TEXT
)
When I opened DB there is strange table sqlite_autoindex_koszty_1 that seems to correspond to table only. Old tables does not have it.
On the other hand There is also sqlite_sequence table that seems to store autoincrement ids.
All tables have their name here and AI value except for koszty.
What could be a reason? How to fix DB. Is it me doing something wrong (maybe Sqlite versions are different? using SQLite3 on Sqlite2 DB?) or is SQLite so inconsistent?
To make one of your columns autoincrementing, you must declare it as INTEGER PRIMARY KEY (not NUMERIC). If you have no such column, the value returned by lastInsertRowid is for the hidden rowid column.
The sqlite_autoindex_ object is not a table but the internal index created for your primary key.
Stupid me, found it. Field definition should be:
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
instead of
id NUMERIC PRIMARY KEY DESC,
Without autoincrement it creates index for Primary key instead of Autoincrement it.
Strange behaviour I must say.