0

I'm working with the sqlite3 library under python 3.5.

I have defined two tables, related through a foreign key, with the following structures:

Table 1 structure:

'Table1_ID' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE
'Name' TEXT

Table 2 structure:

'Table2_ID' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE
other fields
'NameID' INTEGER NOT NULL, FOREIGN KEY(`NameID`) REFERENCES table_1 ( Table1_ID )

Then I want to add a record on Table1, and subsequently add a record on Table2 that references the record just inserted in Table1. The steps would be:

  1. Add record to Table1 using an INSERT query - the database engine will assign a unique Table1_ID integer automatically

  2. Somehow read that Table1_ID value, say Table1_ID=821

  3. Add a record to Table2 using an INSERT query, where I specify that NameID=821

Now, my question is about the most efficient way of implementing step 2. An obvious option is to execute a "SELECT" query to get the cursor over the just inserted record, and then read its Table1_ID value.

But I am wondering whether there is an alternative way of knowing Table1_ID juts after the INSERT record without having to execute a second query.

Dimitris Fasarakis Hilliard
  • 150,925
  • 31
  • 268
  • 253
DrD
  • 419
  • 4
  • 14
  • Thanks for your comment @DeepSpace. That solution has the risk that another transaction may have altered the value of lastrowid. I'd like to avoid this if possible. – DrD Jan 06 '17 at 16:58
  • Forget my previous comment, I just realised that different cursors would avoid this problem. My question is certainly a duplicate of the one you suggested. – DrD Jan 06 '17 at 16:59
  • Thanks for coming to the conclusion that they are! I have no experience in sqllite3 and couldn't decide on my own. You can either delete your question or let it hang around as a flagpost for other users facing a similar problem. I will edit your question and remove the *UPDATE* section now :-) – Dimitris Fasarakis Hilliard Jan 06 '17 at 19:26

0 Answers0