Referential update error with DataSet

[Flomo Togba Kwele]

I get an error when executing the Update method on a dataset. The dataset is a
strongly typed one, with two tables in a parent/child relationship.

The parent has an identity column for its PK, while the child's PK is a
composite one. Its first column is the identity column of the parent and the
other column is a zipcode.

There is a constraint in the child:
ALTER TABLE Child ADD CONSTRAINT FK_Rel FOREIGN KEY(ParentPK)
REFERENCES Parent (ParentPK)

During processing, I allow the user to create a new Parent row and change any
of the child rows so that its new parent is the newly added Parent row. I set
these Child rows's foreign key column to match the value of the identity column
of the newly added Parent row.

I don't know how this value gets assigned to the newly added Parent, but this
value does not exist in the live DB. When I look at the identity columns in
both the Parent and Child rows in the debugger before the Update method is
executed, I see that they have the same value.

When the Update method executes, I receive the following error:
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Rel". The
conflict occurred in database "MailHaus", table "dbo.Parent", column 'ParentPK'.
The statement has been terminated.

What do I need to do to clean this up? I am totally confused. I don't see how
cascading updates would help the situation, since I'm not asking the parent to
propagate its changes to its children - it's the opposite.

Thanks, Flomo

 

[WenYuan Wang [MSFT]]

Hi Flomo,

Accoding to your description, I understand you get the following error
message when you use Update() method to insert datarows into underlying
database. If I misunderstand anything here, please don't hesitate to
correct me.

The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Rel".
The conflict occurred in database "MailHaus", table "dbo.Parent", column
'ParentPK'.The statement has been terminated.

Would you please provide some code snippet about how did you update
dataset? It seems like you update the child table first and then the parent
table. Because there is no related identity in parent table, SQL throw the
exception that conflict with the FOREIGN KEY constraint. If you want to
insert parent-child dataset into database, you would have to insert the
parent rows first and then the child rows. Otherwise, you will receive the
error message such as "conflicted with the FOREIGN KEY constraint"

Hope this helps.Please let me know if you still have anything unclear. I'm
glad to assist you.
Sincerely,
Wen Yuan
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Flomo Togba Kwele]

Wen Yuan,

Thanks for you ideas. It turned out that I needed a quick answer so I got help
directly from MS.

The update methods were done correctly. I did not describe exactly the
relationships.

The Parent row was newly added. Some children rows existed with another parent.
I needed to "re-assign" these children rows with this new parent. The children
rows's PK is a composite, consisting of the PK (identity column) of the parent
and a zipcode column.

What I needed to do was to set the relationship between the parent/children in
the strongly-typed dataset to cascade on update. That way, the dataset would
propagate the temporary PK of the newly-added parent row to the children rows.
The update method on the datatable of the parent would get the permanent
identity column and propagate that to the children as the FK.

Flomo

--

 

[WenYuan Wang [MSFT]]

Hi Flomo,
Thanks for your response. It seems like you have resolved the issue with
CSS now. This is a really good news. By the way, If you meet some issue
next time and it's not very urgent, welcome to newsgroup agagin and I'm
very glad to assist you.

Have a great day,
Sincerely,
Wen Yuan
Microsoft Online Community Support