How to get a surrogate key value in order to put a foreign key in and create relationship?

Question

I am trying to create two relationships between three tables, using foreign keys with values of primary keys, which are surrogate keys. But I don't understand how to enter the right value in the foreign keys, since it has no meaning, being a surrogate key.

Here are my tables :

Quote (quoteID PRIMARY KEY, quoteValue, quoteDate, currency)

TermStrucureComponent (TSComponentID PRIMARY KEY, type)

InterestRate (rateID PRIMARY KEY, quote FOREIGN KEY to quoteID, TermStructureComponent FOREIGN KEY to TSComponentID, timeToMaturity)

So I have my InterestRate table that has two relationships: one with Quote, and one with TermStructureComponent. After inserting rows in the two first tables (without any foreign key), I need to insert it in InterestRate. But I don't know how to fill in the quote and TermStructureComponent fields, because I don't know their value in the other tables, since they are surrogate keys.

How should I do that? Thank you for your help

Answer 1

Assuming your surrogate keys are auto-incrementing integers marked as primary keys they will be the "rowid" for that table so you can read the last value added for a single insert using SELECT last_insert_rowid(). This operates like scope_identity in other databases. You would need to read each one after each row insert as each will replace record of the previous one.

sqlite doesn't seem to have an equivalent to the OUTPUT clause found in SQL Server and the RETURNING clause in Postgres & Oracle, so I don't know of a way to read multiple generated ID meaning that you'll have to do this one row at a time.