尝试进行分层更新会导致错误“无法插入外键值”
我对 DAO 和 SQL Server 有点菜鸟，当我尝试将值插入到两个有关系的表中时遇到了问题。 Photos 表有一个 gpsId 字段，该字段与 GPSLocations 表的 id 字段具有外键关系。我想创建一个链接到新 GPSLocation 的新照片条目，因此代码如下所示：
gpsRow = dataset.GPSLocations.AddGPSLocationsRow("0.0N", "3.2W");
dataset.Photos.AddPhotosRow(@"c:\path\file.jpg", gpsRow);
tableAdapterManager.UpdateAll(dataset);
但是，这会导致以下错误：
无法插入外键值 因为有对应的主键 值不存在。 [ 外键 约束名称 = photoToGps ]
我正在使用 SQL Server CE。我的理解是否正确，TableAdapterManager 应该处理此分层更新？我只是将这些表拖到 XSD 视图上，并依靠它自动创建包装类。我是否需要更改关系的任何内容（例如，使其成为外键约束）？我注意到在某些情况下 GPS id 为正，有时为负，这是否相关？
编辑： 我还确保更新属性设置为 CASCADE，这会导致相同的错误。分层更新设置为 true，并且设计器中的两个表之间存在外键约束。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
由于 photoToGps 列（外键）取决于主键 (id)，因此除非存在相应的 id，否则无法添加 photoToGps。因此，您需要的是单独更新，而不是进行 UpdateAll。首先更新 GPSLocations 表，然后更新另一个表。这样，在为其添加 photoToGPS 之前，您将拥有一个现有的 id。
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.
两个表之间是否存在外键约束（XSD 设计器上应该有一条线连接它们）？由于您的字段的命名不同，当您将表拖到设计图面时，它可能不会自动添加。
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.
您是否按照此处所述启用了分层更新？
Did you enable Hierarchical Updates as described here?
我已经成功地找到了这个问题的根源，归结为 SQL Server CE 与完整 SQL Server 相比的限制。事实证明，出现问题的主要提示是因为 ID 为负值。在将行插入数据库之前，DataSet 中的 id 为负数，此时它将解析为正索引。它没有成为正索引的事实是因为
TableAdapterManager通常会执行INSERT批处理语句，然后执行SELECT来更新ID。但是，SQL Server CE 不支持批处理语句，因此需要编写额外的代码，以便我们通过响应RowUpdated事件来模拟SELECT步骤。这个 MSDN 文章 解释了这些步骤。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
TableAdapterManagernormally does a batch statement ofINSERTfollowed by aSELECTto update the id. However, SQL Server CE doesn't support batch statements, so this requires extra code to be written so that we simulate theSELECTstep by responding to theRowUpdatedevent. This MSDN article explains the steps.这只是您的数据集的配置。双击 Visual Studio 数据集设计器中表之间的关系，选择关系和外键约束选项，然后在更新规则字段中选择级联选项，一定就是这样。
有关该主题的一些信息位于 MSDN 中，您可以在此处查看 http://msdn。 microsoft.com/en-us/library/bb629317.aspx 并转到相关主题。
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.