JPA + Hibernate: How to define a constraint having ON DELETE CASCADE

Question

I am just wondering if there's such a way that I can have build my MySQL table as

ALTER TABLE `USERINFO`
  ADD CONSTRAINT `FK_USER_ID` FOREIGN KEY (`USERID`) REFERENCES `USERACCOUNT` (`USERID`) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE;

However, I only got this in my DDL when hibernate ++ jpa starts to build my table having "<property name="hibernate.hbm2ddl.auto" value="create" />"

ALTER TABLE `USERINFO` ADD CONSTRAINT `FK_USER_ID` FOREIGN KEY (`USERID`) REFERENCES `USERACCOUNT` (`USERID`);

In my classes, I have these annotation setup,

// UserAcc.java
@Entity
@Table(name = "USERACC")
public class UserAcc implements Serializable {

private static final long serialVersionUID = -5527566248002296042L;

@Id
@Column(name = "USERID")
@GeneratedValue(strategy=GenerationType.AUTO)
private Integer userId;


@OneToOne(mappedBy = "userAcc")
private UserInfo userInfo;
....


public UserInfo getUserInfo() {
    return userInfo;
}
public void setUserInfo(UserInfo userInfo) {
    this.userInfo = userInfo;
}
...

and,

// UserInfo.java
@Entity
@Table(name = "USERINFO")
public class UserInfo implements Serializable {

private static final long serialVersionUID = 5924361831551833717L;

@Id
@Column(name = "USERINFO_ID", nullable=false)
@GeneratedValue(strategy=GenerationType.AUTO)
private Integer userInfoId;

@OneToOne(cascade = {CascadeType.ALL})
@JoinColumn(name="USERID", nullable=false)
@ForeignKey(name = "FK_USER_ID")
private UserAcc userAcc;


public Integer getUserInfoId() {
    return userInfoId;
}

public void setUserInfoId(Integer userInfoId) {
    this.userInfoId = userInfoId;
}
...

Note that, UserAccount table is the parent/main table here while UserInfo is an extended table normalize to another entity. Any answers would be greatly appreciated. I'm just curious how it's done as I love to work also in MySQL. I am just really used to deleting a record from the parent table (USERACOUNT) which would would also allow me to cascade a delete thru child records dependent on the specific record from a parent/primary table.

Thanks!

Answer 1

JPA does offer possibility to cascade operations (merge, persist, refresh, remove) to associated entities. Logic is in JPA and does not utilize database cascades.

@OneToMany(cascade=CascadeType.REMOVE)

There is no JPA standard compliant way to do cascades with database cascades. If such a cascades are preferred, we have to fall back to Hibernate specific construct: @OnDelete. It works with @OneToMany at least, but there used to be some problems in the past with @OneToOne and @OnDelete.

@OnDelete(action = OnDeleteAction.CASCADE)

Be aware that adding this annotation to an existing constraint will not update it. You may have to manually drop it first to properly update the schema.

Answer 2

There is no clean cut means to do this in JPA. The following will get you what you want... You can use CascadeType.DELETE, however this annotation only applies to the objects in the EntityManager, not the database. You want to be sure that ON DELETE CASCADE is added to the database constraint. To verify, you can configure JPA to generate a ddl file. Take a look at the ddl file, you'll notice that ON DELETE CASCADE is not part of the constraint. Add ON DELETE CASCADE to actual SQL in the ddl file, then update your database schema from the ddl. This will fix your problem .

This link shows how to use ON DELETE CASCADE on for CONSTRAINT in MySQL. You do this on the constraint. You can also do it in a CREATE TABLE or ALTER TABLE statement. It's likely that JPA creates the constraint in an ALTER TABLE statement. Simply add ON DELETE CASCADE to that statement.

Note that some JPA implementors do provide a means for this functionality.

Lastly, Hibernate does supply this functionality using the OnDelete(action = OnDeleteAction.CASCADE) annotation.

Answer 3

You can use Hibernate annotation org.hibernate.annotations@OnDelete(action = OnDeleteAction.CASCADE) on UserAcc.userInfo:

public class UserAcc implements Serializable {

...

@OneToOne(mappedBy = "userAcc")
@OnDelete(action = OnDeleteAction.CASCADE)
private UserInfo userInfo;

...

this will generate DDL ON DELETE CASCADE on foreign key in UserInfo table.

Answer 4

You can define the constraint manually by using @JoinColumn(foreignKey = @ForeignKey(...)) as follows:-

// UserInfo.java
@Entity
@Table(name = "USERINFO")
public class UserInfo implements Serializable {

private static final long serialVersionUID = 5924361831551833717L;

@Id
@Column(name = "USERINFO_ID", nullable=false)
@GeneratedValue(strategy=GenerationType.AUTO)
private Integer userInfoId;

@OneToOne(cascade = {CascadeType.ALL})
@JoinColumn(
        name="USERID", 
        nullable=false,
        foreignKey = @ForeignKey(
                name="FK_USER_ID",
                foreignKeyDefinition = "FOREIGN KEY (USERID) REFERENCES USERACCOUNT(USERID) ON UPDATE CASCADE ON DELETE CASCADE"
        )
)
private UserAcc userAcc;


public Integer getUserInfoId() {
    return userInfoId;
}

public void setUserInfoId(Integer userInfoId) {
    this.userInfoId = userInfoId;
}
...

This will add the constraint during generating DDL.

After the generation process is completed, you can check the DDL of USERINFO table and you will find that the constraint has been added with the cascading actions as shown below:-

create table USERINFO(
    ....,
    ....,
    ....,
    ....,
    constraint FK_USER_ID foreign key (USERID) references USERACCOUNT(USERID) on update cascade on delete cascade
);

Answer 5

please config your mySql alter the table forgein key on update noaction an on delete no action and apply

 @OnDelete(action = OnDeleteAction.NO_ACTION)
    private Set<Course> courses = new HashSet<>();