Trying to do a hierarchical update results in an error "A foreign key value cannot be inserted"

Question

I'm a bit of a noob with DAO and SQL Server and I'm running into a problem when I'm trying to insert values into two tables that have a relation. The table Photos has a gpsId field which has a foreign key relation with the id field of the GPSLocations table. I want to create a new Photos entry linked to a new GPSLocation, so the code looks something like this:

gpsRow = dataset.GPSLocations.AddGPSLocationsRow("0.0N", "3.2W");
dataset.Photos.AddPhotosRow(@"c:\path\file.jpg", gpsRow);
tableAdapterManager.UpdateAll(dataset); 

However this results in the following error:

A foreign key value cannot be inserted because a corresponding primary key value does not exist. [ Foreign key constraint name = photoToGps ]

I'm using SQL Server CE. Is my understanding correct that the TableAdapterManager should be handling this hierarchical update? I just dragged these tables onto the XSD view and relied on its automatic creation of the wrapper classes. Do I need to change anything about the relation (eg to make it a Foreign Key constraint)? I've noticed that under some circumstances the gps id is positive and sometimes negative, is that relevant?

EDIT: I've also ensured that the update property is set to CASCADE, which results in the same error. Hierarchical updates are set to true and there is a foreign key constraint between the two tables in the designer.

Answer 1

Since the column photoToGps (foreign key) depends on the primary key (id), you cannot add a photoToGps unless there is a corresponding id present. So what you need to is individual updates, instead of doing an UpdateAll. First update the GPSLocations table, and then the other table. That way, you will have an id existing before you add a photoToGPS for it.

Answer 2

Did you enable Hierarchical Updates as described here?

Answer 3

Is there a foreign key constraint between the two tables (there should be a line on the XSD designer connecting them)? Since your fields are named differently it might not have been automatically added when you dragged the tables to the design surface.

Answer 4

It's just the configuration of your data set. Doubleclick the relation beween the tables in the Visual Studio's dataset designer, choose Both Relation And Foreigh Key Constraint option and in the Update Rule field choose Cascade option and that must be it.

Some information about the subject is in MSDN, you can look here http://msdn.microsoft.com/en-us/library/bb629317.aspx and go to the related topics.

Answer 5

I've managed to track down the source of this problem, which boils down to a limitation of SQL Server CE compared with the full SQL Server. It turns out the major hint that something wasn't right was because the ids were negative. The ids are negative in the DataSet before the row is inserted into the database, at which point it gets resolved to a positive index. The fact that it wasn't becoming a positive index happened because the TableAdapterManager normally does a batch statement of INSERT followed by a SELECT to update the id. However, SQL Server CE doesn't support batch statements, so this requires extra code to be written so that we simulate the SELECT step by responding to the RowUpdated event. This MSDN article explains the steps.