How to deal with cascading changes?

  • Thread starter Thread starter Patrick
  • Start date Start date
P

Patrick

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
 
Try setting enforceconstraints to false before you try to do the updates,
and then back to true once you are done.
 
Marina said:
Try setting enforceconstraints to false before you try to do the updates,
and then back to true once you are done.
Click to expand...

Won't this leave orphaned records in the child tables?
 
I thought you were going to update the keys of both the child and master
tables - your problem was that updating either one of them first would
result in a key violation.
 
Consider that a change in the Primary key is not an UPDATE. It must be
treated as a delete/insert to maintain RI. It is further complicated when
the parent owns children or when there are other more far-reaching RI
ramifications. It is almost suicidal to permit the PK to change IMO.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Though it's doesn't really solve the problem a PK should never change.

In this case I would prefer to let the user use whatever code he prefer (and
change it if he want) and to use an internal pk.

Patrice
 
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
 
Thanks for your reply.

Could you please eloberate on your comment. I don't wanna look like the noob
I really am ;) but I am not getting the remark on delete/insert.

I agree with you that changing PK values is not the wisest thing to do. In
this particular situation however, the PK will in real-life situations never
change except for that very one time that it should be. I just want to cover
that possibility.

I will however rethink it over as I already stumbled across another problem:
The cascade will be performed whenever I call UPDATE on the PK column even
when the old and new values are the same. The Data Adapter Wizard has this
weird tendency to create stored procedures that updates all columns. I now
have the choice to change all these sp's by hand to make them smarter or to
redesign this part of the data model.

Patrick
 
Patrice, thanks for your reply.

I am considering your proposal (see my reply to Bill Vaughn).

This PK update thing gives me a headache and that should be a hint that I
must do it another way.

Patrick
 
Also the interesting case of what happens when you delete a row in the
parent table in the DataSet and then do an update - it should fail because
there are matching child rows in the database table. You may need to do the
added and modified rows in the parent table first, then the child table,
then the deleted rows in the parent table.
 
Back
Top