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

0

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

    
asked by anonymous 24.11.2016 / 14:31

2 answers

0

Use something like this (depends on your code):

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

last_insert_rowid() will give you the last ID you entered. Obviously the% w / o that generates it should come just before this INSERT and everything should be within a single transaction .

You have to be careful not to use in multithreaded environment . It is also important to check if the previous insertion occurred without problems, if it does not occur, what will be picked up is a previously inserted ID, which is not what you want. The way you do this depends on how you are implementing all of this, of the technology used. Maybe it is with a INSERT that returns a success code, perhaps throws an exception, you may have to query to check it out.

    
24.11.2016 / 14:35
0

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

Your query would look only:

SELECT last_insert_rowid()
    
24.11.2016 / 14:44