Can't create a Many-To-One relation with all-delete-orphan cascade. MySQL says a foreign key constraint fails when deleting the parent.

Question

Hi, I started using Hibernate recently and I'm still a newbie, however, the error I'm facing doesn't seem to be simple.

My current environment is:

• Windows 7
• MySQL 5.1.49-community
• mysql-connector-java-5.1.13-bin.jar
• hibernate-distribution-3.6.0.Beta1

I'm following the "Hibernate in Action" book and did everything exactly the way it suggests.

When I try to delete a parent object (UserClass) of a many-to-one relation. I expected that the parent object were deleted and all its child (User) too. However, I got java.sql.BatchUpdateException, like quoted:

Exception in thread "main" org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
        at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
        at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
        at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
        at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1215)
        at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:382)
        at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
        at netbeansproject.Main.testUserClassAndUsers(Main.java:42)
        at netbeansproject.Main.main(Main.java:55)
Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`lojatest`.`user`, CONSTRAINT `fk_User_UserClass1` FOREIGN KEY (`user_class_id`) REFERENCES `userclass` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION)
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2020)
        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1451)
        at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
        at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
        ... 9 more

It seems to be related to MySQL Foreign Key constrants, specially regarding the "ON DELETE NO ACTION ON UPDATE NO ACTION", however, I'm not exactly an MySQL expert.

I've found some other questions about alike situation and I still couldn't find a fix for that. Can anyone help me?

Thanks very much.

Here is relevant code:

User.java:

package domain;

public class User {

    private String userName;
    private String password;
    private Boolean blocked;
    private UserClass userClass;

    public User() {
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Boolean getBlocked() {
        return blocked;
    }

    public void setBlocked(Boolean blocked) {
        this.blocked = blocked;
    }

    public UserClass getUserClass() {
        return this.userClass;
    }

    public void setUserClass(UserClass userClass) {
        this.userClass = userClass;
    }
}

User.hbm.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt;
<hibernate-mapping package="domain">
    <class name="User" table="user">
        <id
            column="username"
            name="userName"
            type="string">
            <generator class="assigned"/>
        </id>
        <property
            column="password"
            name="password"
            not-null="true"
            type="string"/>
        <property column="blocked" name="blocked" type="boolean"/>
        <many-to-one
            name="userClass"
            column="user_class_id"
            class="UserClass"
            not-null="true"/>
    </class>
</hibernate-mapping>

UserClass.java

package domain;

import java.util.HashSet;
import java.util.Set;

public class UserClass {

    private Long id;
    private String title;
    private String permissions;
    private Set users = new HashSet();

    public UserClass() {
    }

    public Long getId() {
        return id;
    }

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

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getPermissions() {
        return permissions;
    }

    public void setPermissions(String permissions) {
        this.permissions = permissions;
    }

    public void setUsers(Set users) {
        this.users = users;
    }

    public Set getUsers() {
        return this.users;
    }

    public void addUser(User user) {
        user.setUserClass(this);
        this.users.add(user);
    }
}

UserClass.hbm.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt;
<hibernate-mapping package="domain">
    <class
        name="UserClass"
        table="userclass">
        <id
            column="id"
            name="id">
            <generator class="native"/>
        </id>
        <property
            column="title"
            name="title"
            not-null="true"
            type="string"/>
        <property
            column="permissions"
            name="permissions"
            not-null="true"
            type="string"/>
        <set
            name="users"
            inverse="true"
            cascade="all-delete-orphan">
            <key column="user_class_id"/>
            <one-to-many class="User"/>
        </set>
    </class>
</hibernate-mapping>

DatabaseFactory.java:

package Database;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class DatabaseFactory {

    private static DatabaseFactory instance = null;

    private SessionFactory sessionFactory;

    public static DatabaseFactory getInstance() {
        if (DatabaseFactory.instance == null) {
            DatabaseFactory.instance = new DatabaseFactory().init();
        }

        return DatabaseFactory.instance;
    }

    public SessionFactory getSessionFactory() {
        return this.sessionFactory;
    }

    public Session getSession() {
        return this.sessionFactory.openSession();
    }

    private DatabaseFactory init() {
        Configuration cfg = new Configuration();

        cfg.addClass(domain.UserClass.class);
        cfg.addClass(domain.User.class);

        cfg.setProperties(System.getProperties());
        cfg.configure();
        SessionFactory sessions = cfg.buildSessionFactory();

        sessionFactory = cfg.configure().buildSessionFactory();
        return this;
    }
}

Main.java (test class):

package netbeansproject;

import Database.DatabaseFactory;
import domain.*;

import java.util.List;
import java.util.Iterator;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

public class Main {
    public void testUserClassAndUsers() {
        System.out.println("Testing Users and UserClasses...");

        Session newSession = DatabaseFactory.getInstance().getSession();

        System.out.println("1 - Creating UserClasses:");
        Transaction t1 = newSession.beginTransaction();
        UserClass uc1 = new UserClass();
        uc1.setTitle("UserClass 1");
        uc1.setPermissions("XYZ");
        newSession.save(uc1);
        t1.commit();

        System.out.println("2 - Creating Users:");
        Transaction t2 = newSession.beginTransaction();
        User u1 = new User();
        u1.setUserName("User 1");
        u1.setPassword("Password 1");
        u1.setBlocked(false);
        u1.setUserClass(uc1);
        newSession.save(u1);

        User u2 = new User();
        u2.setUserName("User 2");
        u2.setPassword("Password 2");
        u2.setBlocked(false);
        u2.setUserClass(uc1);
        newSession.save(u2);
        t2.commit();

        System.out.println("3 - Deleting UserClass (\"UserClass 1\"):");
        Transaction t3 = newSession.beginTransaction();
        newSession.delete(uc1);
        t3.commit();

        newSession.close();
    }

    public static void main(String[] args) {
        Main instance = new Main();

        instance.testUserClassAndUsers();
    }
}

SQL Script to create DB (generated by MySQL Workbench):

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

DROP SCHEMA IF EXISTS `LojaTest` ;
CREATE SCHEMA IF NOT EXISTS `LojaTest` ;
SHOW WARNINGS;
USE `LojaTest` ;

-- -----------------------------------------------------
-- Table `LojaTest`.`UserClass`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `LojaTest`.`UserClass` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `LojaTest`.`UserClass` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(45) NOT NULL ,
  `permissions` VARCHAR(16) NULL ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC) )
ENGINE = InnoDB;

SHOW WARNINGS;

-- -----------------------------------------------------
-- Table `LojaTest`.`User`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `LojaTest`.`User` ;

SHOW WARNINGS;
CREATE  TABLE IF NOT EXISTS `LojaTest`.`User` (
  `username` VARCHAR(10) NOT NULL ,
  `password` VARCHAR(30) NOT NULL ,
  `blocked` TINYINT(1)  NOT NULL DEFAULT false ,
  `user_class_id` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`username`) ,
  UNIQUE INDEX `id_UNIQUE` (`username` ASC)
)
ENGINE = InnoDB;

SHOW WARNINGS;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Note I'm not creating any foreign key... however, after I run my code, a foreign key is created in the User table referencing the UserClass table id. Before this test, I've tried the same SQL script, however with the following options for the User table:

CONSTRAINT `fk_User_UserClass1`
    FOREIGN KEY (`user_class_id` )
    REFERENCES `LojaTest`.`UserClass` (`id` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION

And

CONSTRAINT `fk_User_UserClass1`
    FOREIGN KEY (`user_class_id` )
    REFERENCES `LojaTest`.`UserClass` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION

None of then worked.

Thanks again.

Answer 1

I've changed my approach and started using annotations.

At first, I had the same issue, but then I read a little about JPA's EntityManager and fixed the issue.

The issue was due to removing a UserClass in the same EntityManager's context that it was created.

I'm not getting back to this xml based mapping approach because it is not worthwhile (IMHO). However, I think it may be solved using more than one Hibernate session. One to create the objects, and another to delete an object.

Hope it may help someone. Thanks