Foreign key reference with cascading delete

[jbr]

Hi all,
I'm trying to model a foreign key reference with cascading delete. I have the Classes User, List and ListEntry. ListEnry belongs to an instance of List and references an Instance of User. User does not know anything about ListEntry.
I want to make sure, that when a User is deleted, all referencing ListEntries will be deleted too. The Other way round, deleting a ListEntry should not have any affect on the referenced User or List.
In classic ER-Modeling I would have used something like this:

create Table user(String id, String name, ...)
create Table ListEntry(
String id,
String user references user.id on delete cascade,
String list references List.id on delete cascade)

create Table List(String id, String name)


Everything would be perfect in this ER world. With EJB3 i would expect to use the following:

@Entity
class User{

@Id
String id

String name

}


@Entity
class ListEntry{

@Id
String id

User user

@ManyToOne(cascade=CascadeType.REMOVE)
User getUser(){
..}

void setUser(User user){
}

}

@Entity
class List{
@Id
String id

List<ListEntry> entries;

@OneToMany(cascade=CascadeType.ALL)
List<ListEntry> getEntries(){
...
}

void setEntries(List<ListEntry> entries){
...
}

}

I tried this but it didn't work quite as I expected. Deleting a ListEntry results in a delete of the Referenced User too...
How would I model the above Problem? I'm puzzled.

Thanks for advice

Juergen

[wolfgangknauf]

Hi Juergen,

In your entity "ListEntry" the cascade should/might look like this:

@OneToMany(cascade={CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
User getUser(){...

If you specifiy "REMOVE" then the user is deleted if the list entry is removed.

Best regards

Wolfgang

[jbr]

Hi Wolfgang,

thanks for your help. I tried your suggestion. You are right as far as the user would survive the remove of a listEntry. But with this configuration i can't delete a user before all referencing listEntries are removed.
In terms of ER-Modelling it creates something like this:

table user(String id, String name, ...)

table listEntry(String id,
 String userId references user.id,
 String listId references list.id on delete cascade)

table list(String id, String name)

But what I need is:

table user(String id, String name, ...)

table listEntry(String id,
 String userId references user.id on delete cascade,
 String listId references list.id on delete cascade)

table list(String id, String name)

That's not exactly what i wanted, as i still need to cleanup the listentries manually before i can delete the user.
Have u successfully implemented something like that before?

Best Regards
Juergen

[wolfgangknauf]

Hi Juergen,

you should change the relation from List to User to a bidirectional one. Something like this:

@OneToMany(cascade=CascadeType.REMOVE)
List getLists(){
...
}

This way, if you delete a user, all Lists are deleted, and also all ListEntries.

Hope this helps

Wolfgang