Re: How to deal with cascading changes?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Patrick (patrick_at_nospam.com)
Date: 09/21/04 

Date: Tue, 21 Sep 2004 09:41:10 +0200

Marina, thanks for your reply.

Disabling constraints does not solve my problem as the problem does not lie
in the dataset.

The problem is this:

I have two tables: Parent and Child. In SQL Server table Parent has a
primary key with ON UPDATE CASCADE.
This primary key is also part of the primary key of table Child.

I have a dataset with also these two tables. I now change the key of Parent.
In the dataset this change will also be cascaded to table Child.

I have two data adapters, one for Parent and one for Child, which I use for
updating the database.

Suppose I call the Update method on the data adapter for the Parent first:
- In the database the primary key will cascade to table Child.
- When calling the data adapter Update method for Child, the keys in the
database will no longer match those of the DataRowVersion.Original version
in the dataset (which are used in the WHERE clause of the UPDATE command).
Therefore the Update method fails as it can no longer find the appropriate
Child rows.

Suppose I call the Update method on the data adapter for the Child first:
- The UPDATE command will try to set the FK of the Child table to a value
that is not yet set in the Parent table. Therefore I get a constraint
violation.

I have found the following workaround for my problem (can't call it a
solution):
- I change the primary key in table Parent in the dataset
- I then update table Parent in the database (not table Child)
- I then call AcceptChanges() on the dataset. This to let the dataset
"synchronize" with the actual values in the database.
- Then I can go about with the changes on other columns.

This workaround costs me an extra round-trip to the database. I hope someone
out there has a better solution as I feel my problem must be a very common
pattern.

Patrick

"Marina" <someone@nospam.com> wrote in message
news:uKfqERxnEHA.1800@TK2MSFTNGP15.phx.gbl...
> Try setting enforceconstraints to false before you try to do the updates,
> and then back to true once you are done.
>
> "Patrick" <patrick@nospam.com> wrote in message
> news:OIokHvunEHA.3460@tk2msftngp13.phx.gbl...
>> I have a situation in where I have a table with a primary key that can be
>> altered (in very rare occasions, but still have to deal with it) and the
>> change is cascaded in SQL Server to many other child tables.
>>
>> The problem is that this does not work in conjunction with datasets.
>>
>> When I first update the master table, the update of the child tables will
>> fail as the key of the child tables has been altered by the cascade and
> the
>> update command still uses the original key from the original row data in
> the
>> dataset.
>> When I first update the child tables, these will fail as the master table
>> does not yet contain the new key and thus this results in a foreign key
>> violation.
>>
>> Has anyone dealt successfully with this problem?
>>
>> Thanks,
>>
>> Patrick
>>
>>
>
>

Relevant Pages

  • Re: Connecting to the Same Table Twice
    ... RelationshipID (primary key) ... RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... you might put spouse names into a separate table linked to this table). ... for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: Connecting to the Same Table Twice
    ... > RelationshipID (primary key) ... > RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... > you might put spouse names into a separate table linked to this table). ... > for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: MS Access
    ... primary key has some blank, ... We can call this our parent or so called "main" table. ... you must declare a new column in this child table. ... In the above you can tell the customer with an id of 2 has a favorite color ...
    (microsoft.public.access.gettingstarted)
  • Re: Using a Resource as a Class Property
    ... In OOP the constructor of the grandest child:) will be called when a class is instantiated as an object. ... By having to call the parent constructor from within the child object, It gives the child better control. ... In the case of a Database object, the main purpose of the object is to perform the communications. ... abstract methods open, close, query ...
    (comp.lang.php)
  • Re: Data Normalization
    ... CustomerID foreign key. ... of the database whether an SSN would be a good candidate for a primary key. ... applicable that relates that given record to it's parent. ... Create the relationship between the child table and my new parent ...
    (microsoft.public.access.queries)