Can you get the value of an autoincrement column before it is saved in the database?

Question

I have three tables: preco, estabelecimento and precoXestabelecimento. The id of price is autoincrement and there is a relationship N:N between the first two tables, which is represented by the third table precoXestabelecimento. For a price to be saved you need to have an establishment linked to it, so the moment a price is saved I need to get your ID, along with the establishment ID and save it in the 3rd table. The problem is that I don’t know how to get the price ID once its value is autoincrement and has not been saved in the database yet.

Answer 1

Use something like this (depends on your code):

INSERT INTO precoXestabelecimento (precoID, ...)
    VALUES ((SELECT last_insert_rowid()), ...);

I put in the Github for future reference.

The last_insert_rowid() will give you the last inserted ID. Obviously INSERT that the generates should come just before this INSERT and everything must be inside a single transaction.

You have to take care not to use in environment multithreaded. It is also important to check if the previous insertion occurred smoothly, if it does not occur, what will be picked up is a previously inserted ID, which is not what you want. The way to do this depends on how you’re implementing all of this, the technology used. Maybe it’s with a execute() that returns a code indicated success, maybe throw an exception, maybe have to make a query to confer.

Answer 2

Use last_insert_rowid(). It returns the last INSERT. It is important that you use this function immediately after insertion to avoid picking up the wrong value.

Your query would be only:

SELECT last_insert_rowid()