Print Page | Close Window

Foreighn keys when deleting

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2595
Printed Date: 29-Jul-2026 at 5:50pm


Topic: Foreighn keys when deleting
Posted By: tj62
Subject: Foreighn keys when deleting
Date Posted: 30-Mar-2011 at 8:06am
Hi,
Using Ideablade 6.0.9.0 and INformix Dynamic Server 11.7

Let us first define tables for reproducing this problem:

TableA
   id_a serial not null privary key
   name varchar(50) not null

TableB
  id_b serial not null primary key
  id_a int   not null // foreighn key to Table A, cascade on delete
   name varchar(50) not null

TableC
  id_c serial not null primary key
  id_b int   not null // foreighn key to Table B cascaed on delete
   name varchar(50) not null

Let us say we have linked records in all 3 tables. If I via the EntityModel delete the record in TableA and save the results (by calling manager.SaveChangesAsync()) the records are deleted from table A, B an C as expected as the foreighn keys are "cascade on delete"

The strange thing is that if I instead delete the record from TableB, I get this error when I try to save:
Repository persistence operation failed; ERROR [23000] [IBM][IDS/NT32] Missing key in referenced table for referential constraint (informix.fk_tableC_2_tableB). (-111 ISAM error:  no record found.)

fk_tableC_2_tableB is the foreighn key based on id_b from table C to tabl B.

What on earth could be causing this?
Is the Entity Model doing something else than just:
  DELETE FROM TableB WHERE id_c = 22;
At least if I run such a SQL command from ServerStudio the record is deleted from TableB as well as the linked record in TableC.


Replies:
Posted By: tj62
Date Posted: 30-Mar-2011 at 10:08am
After investigating this a little better by putting a debugger breakpoint at  manager.Saving even handler. I notice this status on the records (e.Entities):
record 0: Entity from TableB with Entity state Deleted
record 1: Entity from TableC with Entity state Modified

The later one is with unexpected status. Why is that. Actually it should not be thera at all as it has not been modified.
However it seems to be so as link up property to TableB is "Detatched".

How can this be avoided?

Posted By: tj62
Date Posted: 31-Mar-2011 at 2:28am
Well no reply, talking to my self here :-)
Further investigation lead to that if Parent Entity (TableB) is having IsPendingEntityList = true for the list of child items in TableC everything works as supposed and there is only one record in e.Entities in the manager.Saving event with status Deleted

But as soon as that list is filled totally (IsPendingEntityList = true) things get wrong and the Entity Model deletes the entity in table B but then tries to modify the child entity in TableC in the same transaction. This is totally wrong and is a BAD BAD BUG.

Posted By: tj62
Date Posted: 31-Mar-2011 at 2:30am
I did a small mistake in my last post.  In the second () "IsPendingEntityList = false" of course.

Posted By: sbelini
Date Posted: 31-Mar-2011 at 3:24am
Hi tj,
 
You're not talking to yourself. I'm looking into the issue. I could not reproduce the problem so far.
My cascading deletes work without any problem. I am, however, running Informix 11.5, but this shouldn't make any difference.
I've also seem that you posted additional info in the last hour.
Since I can't reproduce it here, please provide a small solution reproducing the issue.
 
 
Regards,
   Silvio

Posted By: tj62
Date Posted: 31-Mar-2011 at 3:37am
Well it is me that is the BIG BUG here. I don't know how often I have asked my customers to read our online documentation before requesting support. ...Now I have fallen my self into that pit :-(
I should have read this first:
  http://drc.ideablade.com/xwiki/bin/view/Documentation/save-cascaded-deletes

I only had to set the "End1 OnDelte" to "Cascading" in the accociation, and wolla the problem disapeard. I thouhg EM would set this automtically when reading the database information. But obviously it does not and all those Cascading settings in the database have to be repeated  manually in the Associations in the EM.

However out of curiouisity 3 questions:

1. What effect would it have to set "End2 OnDelete" to "Cascading"? I realy can't se the use for this or what effect it might cause.
2. When "End1 OnDelete" is set to "none" and a parent Entity is deleted, what does the EM try to do with the child Entity?
3. The dropdown only has two selections "Cascade" and "None". It would be usefull to have the 3d option "Set to NULL" for for nullable foreighn key fields. Is that possible?

Posted By: sbelini
Date Posted: 01-Apr-2011 at 1:14pm
tj,
 
1) You will get a self explanatory error: "End 'TableCs' on relationship 'NorthwindIBModel.FK_TableBTableC' cannot have operation specified since its multiplicity is '*'. Operations cannot be specified on ends with multiplicity '*'." The error occur in both pure EF and DevForce.
 
2) It will update the FK (to null if 0..1:* or to 0 if 1:*) in the child entities.
 
3) Not necessary because it's already done when set to none.
 


Print Page | Close Window