Cascading deletes

BIZ TMS Aurelius
1

Hi


I generating a test model via TMS Data Modeler of a many to many situation (2 parents and one child). In the database there is a referential integrity relation with cascading deletes between parents and child.

But because I want to work via Code First I copied the generated code into Delphi:

[Entity]
[Table('AppMemberCategory')]
[Id('FCategoryId', TIdGenerator.None)]
[Id('FAppMemberId', TIdGenerator.None)]
TCategoriesInMember = class
private
[Association([TAssociationProp.Lazy, TAssociationProp.Required],
CascadeTypeAll - [TCascadeType.Remove])]
[JoinColumn('AppMemberId', [TColumnProp.Required], 'ID')]
FAppMemberId: Proxy<TAppMember>;

[Association([TAssociationProp.Lazy, TAssociationProp.Required],
CascadeTypeAll - [TCascadeType.Remove])]
[JoinColumn('CategoryId', [TColumnProp.Required], 'ID')]
FCategoryId: Proxy<TCategory>;
function GetAppMemberId: TAppMember;
procedure SetAppMemberId(const Value: TAppMember);
function GetCategoryId: TCategory;
procedure SetCategoryId(const Value: TCategory);
public
property AppMemberId: TAppMember read GetAppMemberId write SetAppMemberId;
property CategoryId: TCategory read GetCategoryId write SetCategoryId;
end;

When I fire up the application the table and relations are generated but WITHOUT the cascading delete setting in the relations!  It does not do a 360.

How can I set this cascading deletes up ?

Thanks, Edward



// Generated SQL

AppControl: CREATE TABLE AppMemberCategory (
  AppMemberId INT NOT NULL,
  CategoryId INT NOT NULL,
  CONSTRAINT PK_AppMemberCategory PRIMARY KEY (CategoryId, AppMemberId))
---------------
AppControl: ALTER TABLE AppMemberCategory ADD CONSTRAINT 
  FK_AppMemberCategory_Categories_CategoryId FOREIGN KEY (CategoryId) REFERENCES Categories (ID)
---------------
AppControl: ALTER TABLE AppMemberCategory ADD CONSTRAINT 
  FK_AppMemberCategory_AppMembers_AppMemberId FOREIGN KEY (AppMemberId) REFERENCES AppMembers (ID)
---------------





2

If you are generating the database using TMS Aurelius UpdateDatabase feature, then indeed the cascades are not included in the foreign key. All the cascades are handled in the application by TMS Aurelius.

3
Database consistency is very important and should be guaranteed and must be on the database level. Which is supported by every serious database. It should never be done on the application level. That's fundamentally just wrong. There are dba administrators and other apps working here.

Is there a way to update the database from code that it creates cascading rule in the database?  
 


4

Database consistency is guaranteed. Data won't be wrong. The foreign key is there. You cannot delete a record if there is another record referencing that. It's just that TMS Aurelius performs the cascade delete itself because it has to control the objects that has been deleted. That's the same for TMS Echo.


You can always update your database manually. 
5

Ok thanks but I dont think I explain it correctly.  


If the application is running and I go with Sql management studio and delete a parent then the childs are still there. 

But I can create it manually. So thats good.


6

That's not true. You won't be able to delete the parent, an error will be raised. You will have to delete all children first.
7
Hi Wagner,

I think there is still a misunderstanding.

Its not true only if in the database itself has the cascading delete rule between the tables implemented.  

Aurelius is handling the consistently itself. So how can can it handles the consistently of other applications (like SQL Studio) ?  It can't.  Guaranteed consistently only works on the database level.

8

Have you tried it?

TMS Aurelius does add the foreign key constraint. You pasted the statement yourself. It just doesn't not add the DELETE CASCADE option.
Again, that means that the data is consistent. There will be no children pointing to deleted parents. The only difference is that while using DELETE CASCADE you can delete a parent and children will be deleted automatically, without DELETE CASCADE you will have to delete children before deleting the parent, otherwise you will not be able to delete the parent.

9

Yes I you are right. children will not be deleted and it is handled on the server side. The consistence is there. 


Because of some systems who do rely on server cascading deletes I can add it myself.