ON CASCADE DELETE on JPA2 many-to-many relationship

Question

I have read a lot of topics regarding cascading and many-to-many associations, but I haven't been able to find an answer to my particular question.

I have a many-to-many relationship between UserProfiles and Roles. When I remove a UserProfile I want the associated records in the join table (userprofile2role) to be removed by the database, so with an actual SQL 'ON DELETE CASCADE' action. Is this possible? Whatever I try, Hibernate always creates the UserProfile table without specifying ON DELETE behaviour.

UserProfile mapping:

@Entity
public class UserProfile {

    private Long id;
    private Set<Role> roles;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public final Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    // Note: CascadeType.ALL doesn't work for many-to-many relationships
    @ManyToMany (fetch = FetchType.EAGER)
    public Set<Role> getRoles() {
        return roles;
    }

    public void setRoles(Set<Role> roles) {
        this.roles = roles;
    }
}

Role mapping:

@Entity
public class Role {

    private Long id;
    private Set<UserProfile> userProfiles = new HashSet<UserProfile>();

    @Id
    @GeneratedValue
    public final Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    // CascadeType.REMOVE doesn't create ON CASCADE DELETE in SQL?
    @ManyToMany(mappedBy = "roles", cascade = CascadeType.REMOVE)
    public Set<UserProfile> getUserProfiles() {
        return userProfiles;
    }

    public void setUserProfiles(Set<UserProfile> userProfiles) {
        this.userProfiles = userProfiles;
    }
}

The SQL for the join table resulting from these mappings doesn't contain an ON CASCADE DELETE part unfortunately. I tried setting the CascadeType.REMOVE behaviour on both the roles collection in UserProfile and on the userprofiles collection in Role (shown here), but to no avail. Your suggestions are most welcome :-)

CREATE TABLE `px_userprofile2role` (
  `userprofile_id` BIGINT(20) NOT NULL,
  `role_id` BIGINT(20) NOT NULL,
  PRIMARY KEY (`userprofile_id`,`role_id`),
  KEY `FK1C82E84191F65C2B` (`userprofile_id`),
  KEY `FK1C82E8416203D3C9` (`role_id`),
  CONSTRAINT `FK1C82E8416203D3C9` FOREIGN KEY (`role_id`) REFERENCES `px_role` (`id`),
  CONSTRAINT `FK1C82E84191F65C2B` FOREIGN KEY (`userprofile_id`) REFERENCES     `px_userprofile` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

Answer 1

There is no support for generating ON DELETE CASCDADE to DDL in JPA. Concept of cascading REMOVE operation is not DDL level construct. Cascades are about cascading lifecycle operations that targets to entity to the related entities. They have quite much nothing to do with cascades in database. In JPA 2.0 specification this is explained as follows:

If X is a new entity, it is ignored by the remove operation. However, the remove operation is cascaded to entities referenced by X, if the relationship from X to these other entities is annotated with the cascade=REMOVE or cascade=ALL annotation element value.

If X is a managed entity, the remove operation causes it to become removed. The remove operation is cascaded to entities referenced by X, if the relationships from X to these other entities is annotated with the cascade=REMOVE or cascade=ALL annotation element value.

Also REMOVE should not be used with @ManyToMany (from JPA 2.0 spec.):

The relationship modeling annotation constrains the use of the cascade=REMOVE specification. The cascade=REMOVE specification should only be applied to associations that are specified as OneToOne or OneToMany. Applications that apply cascade=REMOVE to other associations are not portable.

What it comes to the generating ON DELETE CASCDADE to DDL, there is vendor extension @OnDelete in Hibernate:

@OnDelete(action=OnDeleteAction.CASCADE)