CODEWITHSUNDEEP
sqlsqlitesql-insertweak-entity
jficz
jficz

Reputation: 289

SQLite: Insert into weak entity table (or how to get the just-inserted defining entity's ID)

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

Answers (1)

Stephan Lechner
Stephan Lechner

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

Related Questions