SQLite - Last Row Insert ID

[simon.schvartzman]

Hi team, I know this has been discussed before but after spending two hours browsing the internet/forum without success please help me with a simple / complete example about how to handle the last inserted row on an SQLite database.

What I want to achieve is:

- An SQLite DB where the first column holds an unique ID
- When I insert a new record, the new ID should be the previous one + 1

And the simplest way to do it would be....

Many thanks in advance!

[D4vidrim]

Hi,
I'm not enough familiar with SQLite, but you should look for sequences.
I've found something like this:

create table Categories (
pk_categoryid integer primary key autoincrement,
category_name text
);

A sequence does exactly what you need.

[Mikey]

You should be reading up on SQLite. This is not a LC issue. The SQLite documentation is very easy to read and clear, and includes flowcharts on how to construct statements. For this case, look up, ROWID, and "primary key". In sqlite the first integer, indexed, autoincrement, not-nullable in a table is an alias for ROWID. In other words, you don't have to have a primary key, if you don't want one.

[AxWald]

Hi,

when working with a database engine it's priceless to have a good database manager software.
Should you use a Mozilla based browser I suggest SQLite Manager. If not, get one ;-)

A quick look there showed me that the definition is (from a working database):

Code: Select all

"ID" INTEGER PRIMARY KEY  NOT NULL

And it also has a nice link to the SQLite documentation ;-)

Have fun!

[simon.schvartzman]

Many thanks to all of you for your help.

I ended up using an alternative solution. As my SQLite DB is linked to a data grid every time I need to get the information I use the "dgNumberOfRecords" parameter which gives me what I was looking for.

Probably is not the best or most elegant solution but it works...

Best!

[rinzwind]

Every table in sqlite already has an unique rowid by design. https://www.sqlite.org/autoinc.html

[MaxV]

Every table in sqlite already has an unique rowid by design. https://www.sqlite.org/autoinc.html

ATTENTION, that rowid changes without notice, it's used by sqlite, never trust on it.

[Mikey]

Really? Cite?

[Mikey]

I'm glad this topic came up. After doing more research on this, it seems that the only time that ROWID will change is if VACUUM is called manually. However, if you use VACUUM, you can mitigate that behavior by assigning a row that is INTEGER PRIMARY KEY. In that case, VACUUM will not reassign rowid's.