Reputation: 289
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.
Upvotes: 1
Views: 1312
Reputation: 35164
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(), ...);
Upvotes: 1