3 Replies Latest reply on Oct 20, 2006 7:11 AM by Wolfgang Knauf

    Foreign key reference with cascading delete

    Jürgen Brardt Newbie

      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

        • 1. Re: Foreign key reference with cascading delete
          Wolfgang Knauf Master

          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

          • 2. Re: Foreign key reference with cascading delete
            Jürgen Brardt Newbie

            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

            • 3. Re: Foreign key reference with cascading delete
              Wolfgang Knauf Master

              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