Primary Key, knowing what I added

Probably a dumb question but here I go…

Solution with a database server where multiple users could be connected.
I have some complex writes where the new primary key from the first will be referenced in a bunch more to follow.
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 must be an easy solution and I’m just to tired to think of it.

PostgreSQL has a RETURING keyword that comes in handy for this. For example:

INSERT INTO my_table (
  a, 
  b, 
  c
) VALUES (
  1, 
  '2', 
  false
) 
RETURNING *;

mySQL: There’s no bulletproof way, but LAST_INSERT_ID() may help.

And I don’t suppose doing that within a transaction will ensure that the value I got back is associated with my last write and not someone else’s who came moments before…

I almost feel like I need to create a separate table for these values and insert with a unique/random value that I can use to query for the primary key added, but that seems like it shouldn’t be needed. I’m sure I’m overlooking the obvious.

Well that’s interesting. I didn’t know that existed.
I do need something that will work with any DB that they may be connecting to over ODBC.

One surefire way is to add a UUID column alongside the primary key and assign it during insert. You can then query for that UUID after the insert to retrieve the inserted record.

2 Likes

I think this is a perfect way of knowing what record is added.

Actually, I ignore (or even exclude) the RowID or any autoincrement functionalities.
I do use a field in both MySQL and SQLite databases with the column-name UUID. I make this my Primary Key.

Like you said, I assign it when creating the record (SQL INSERT). This way LAST_INSERT_ID is never needed. When the INSERT succeeds, I know my Primary Key value, since I generated it.

I simply use a method I called save
Public Function Save( extends db as SqliteDatabase, recordData as Object ) As String

The recordData object is simply a Class with properties. Each property represents a column in the database table. The table-name is the same as the recordData object name, I pass to this save method.
With introspection I iterate through the properties and somehow build my SQL query, based on the public properties inside this class.
Inside this Save function, I look for the UUID property and use a SQL SELECT to see if such a record exists. If so, the Save code will simply update the record. If it doesn’t exist, it will do a SQL INSERT.
When it succeeds, it will return the UUID. If it fails, the Save function will return an empty string.

Works perfectly fine.
I also use Introspection for creating the Database Tables. It saves tons of time to build the queries myself. I simply make a Class with the name of a table I want to create. I give it the properties I need in that table, and run my little introspection function. Done…

LAST_INSERT_ID is associated with your connection only. It will not be affected by other sessions writing to the table.

SqliteDatabase has a LastRowID method.
MysqlCommunityServer has a LastInsertedRowID method.

With the others, you’re on your own.

This is all useful. Thank you.

I do have to find a reliable solution for any database. I think I worked one out melding some of the input on this thread.

Why not just use that UUID column as the primary key?

In DB2 and Oracle, you can create a sequence object, and its NEXTVAL function will return a unique number.

This is true of PGSQL too. Plus, it’s transaction safe. The value gets “used” even if the transaction is aborted, so there is no chance of collision. So you call NextVal before your insert, and explicitly assign the returned value in the insert statement.

There is not a nice way to do this with SQLite. It’s possible to find the next value of an auto increment column, but it doesn’t behave in this same transaction safe manner.

If I had to do this at all, I’d still use an integer primary key just as an easy way to determine order. Our Orm classes expect it too.

But we use Postgres so it’s not needed.

That’s exactly what I did.
Works great. I’ll change it if a better way comes up, but this is fine.