Primary Key, knowing what I added

1

On TOF there is this question

I need to be sure of what primary key was just assigned. I know there are easy ways to do this with SQLite, but I need a reliable method that will work on other DBs too.

I would imagine it’s just the highest one in the table, but how would I know if someone else had written one at the same time - between my execute and select?

There is and the technique works in ANY ACID compliant DB
I’ve used it for nearly 30 years
And it makes it possible to support something like “sequences” which are unique per table

It requires a table to persist the last used ID, here called SEQUENCES, that has one column, NEXTID
Then the general process is

begin transaction

update SEQUENCES set NEXTID = NEXTID + 1

select NEXTID from SEQUENCES

commit transaction

// return what was selected

doing this in a transaction and updating FIRST causes every other access to this table to be paused until this simple sequence concludes

to make this per table make the SEQUENCES table have an extra column that is the table name and then update the code to update SEQUENCES where the table name matches & to select from SEQUENCES where the table name matches

And this is nicely portable to any DB that properly implements transactions

Note that some DB’s do NOT support nesting transactions so this can be something that you need to deal with

2

That’s neat Norm. Any reason to not use a UUID instead?

I’ve been generating my own UUID and inserting it with the data.

3

I’ve used this method on Sybase since before it supported sequences, Oracle, mysql sqlite and others

While I understand the reasons for using a UUID it can be a lot of space dedicated to a unique key which is exactly what this is
A lot of DBs have no data type for UUIDS - so you use at least a varchar(36)
If you use a 64 bit int for mine that means you’ve saved 28 bytes PER ROW for every row
And it may save more IF you use a db encoding like UTF16 or some other multibyte encoding

And if you use foreign keys between table then you also use up 28 more bytes per foreign key

About 15K rows & you’re db is 1 MB larger than it needs to be

There are other pro, and cons, to using UUIDs
A google search of something like “db reason uuid” will give you a ton of hits to read through

Personally I dont use uuids for DB’s

2 Likes