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

  • 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 '15 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 – MarinD Apr 22 '15 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 '15 at 19:09
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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