1

I'm struggling with inserting proper key value to weak entity together with inserting the defining strong entity.

These two entities:

CREATE TABLE 'key' (
'id' INTEGER NOT NULL  PRIMARY KEY AUTOINCREMENT,
...

CREATE TABLE 'key_data_rsa' (
'id_key' INTEGER NOT NULL  PRIMARY KEY REFERENCES 'key' ('id'),
...

I need to Insert key metadata to the table 'key' and corresponding key data to the table 'key_data_rsa', preferably in one query.

As I understand the problem, the entry 'key' must be inserted before the 'key_data_rsa' entry is inserted because the 'id' of the 'key' must first exist.

The problem is that if I do this in two steps I lose track of the 'key' entry - the only guaranteed unique key of the 'key' is its 'id' and as soon as it is inserted I won't be able to select it again in order to get the 'id'.

How do I insert data to the weak entity in this scenario in order to keep proper connection to the strong entity?

Please refrain from commenting or questioning the scheme and use case unless there is something seriously wrong with them. Thanks.

-m.

jficz
  • 271
  • 3
  • 8
  • What language are you using to access the database? – CL. Dec 11 '16 at 21:50
  • Perl DBI. How's that relevant? – jficz Dec 11 '16 at 21:56
  • 1
    Check http://stackoverflow.com/questions/6242756/how-to-retrieve-inserted-id-after-inserting-row-in-sqlite-using-python, see if you can apply the same logic in Perl (which makes @CL. quesiton relevant) – David דודו Markovitz Dec 11 '16 at 22:28
  • It's not perl-relevant for me as I don't use this kind of progammatic approach but in the comments there I found the actual SQLite-relevant solution - the last_insert_rowid() function. In the meantime Stephan Lechner below made the same point. So thanks, the comment was useful even though probably not as intended :) – jficz Dec 11 '16 at 22:48
  • The point **was** last insert rowid but as an attribute of the cursor – David דודו Markovitz Dec 12 '16 at 08:18

1 Answers1

1

function last_insert_rowid() should help. It returns the rowid of the most recent successful insert into a table with rowid; in your case, column id is of type INTEGER PRIMARY KEY and is therefore treated as an alias for the rowid (cf. sqlite core functions)

Referring to a strong entity, which has just been inserted, from a weak entity should work as follows: 

insert into key values (null, ...);
insert into key_data_rsa values (last_insert_rowid(), ...);
Stephan Lechner
  • 34,891
  • 4
  • 35
  • 58
  • yes, exactly what I was looking for :) Just found the very same solution by following the hint Dudu Markovitz made in comments below the question. – jficz Dec 11 '16 at 22:45