1

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

Improve this question
3
  • You need to clarify your intent. Are you trying to perform an insert, based on user inputs? Are the two reference tables already populated, and you are trying to fill in the linkage table? Do the users know the values of the surrogate keys (hopefully no)? Are there alternate handles the users can use to specify which quote and interest rate they are talking about?
    – Walter Mitty
    Apr 21, 2015 at 11:04
  • Yes, an insert based on a financial data source provider (Bloomberg). The two references tables will be populated shortly before, by the same way. And of course no, the users does not know the values of the surrogate keys. And my tables are fully described here, so unfortunately no, there is no more handles to identify a quote and an interest rate. That is the problem
    – Sithered
    Apr 22, 2015 at 6:56
  • You need to fix your database design. You can't expect to get useful results if you only have surrogate keys and no alternate real world keys to identify information with. It's like putting your coat into a coat check without getting a ticket for it!
    – nvogel
    Apr 24, 2015 at 19:09

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.