Wondering how I deal with foreign keys during updates.
I understand how Identity/AutoInc type fields work, assigning negative local values until updates are posted to the server, which then returns the actual IDs from the database engine.
I also get how this works automatically with master/detail hierarchies, so child records can use the temporary, negative values for their parent columns and this all works fine too.
I can’t figure out this scenario though:
I have a master table with two detail tables linked to it. Of the two detail tables, one can potentially link to a record in the other via a foreign key. What I want to do is add a record to the first detail table, then add a record to the second which refers to the record in the first. I then want to post both in the same update by calling ApplyUpdates on the master table.
This doesn’t work. Using the temporary negative ID from the first detail table as the foreign key value in the second detail table causes a violation as the framework obviously isn’t working its magic on this value.
Is there any way around this or am I going to have to apply updates after adding the first detail record to get the actual ID, then add the record to the second detail table?