title: Can't create a Many-To-One relation with all-delete-orphan cascade. MySQL says a foreign key constraint fails when deleting the parent
body: <p>I started using Hibernate recently and I'm still a newbie, however, the error I'm facing doesn't seem to be simple.</p>
  
  <p>My current environment is:</p>
  
  <ul>
  <li>Windows 7</li>
  <li>MySQL 5.1.49-community  </li>
  <li>mysql-connector-java-5.1.13-bin.jar  </li>
  <li>hibernate-distribution-3.6.0.Beta1</li>
  </ul>
  
  <p>I'm following the "Hibernate in Action" book and did everything exactly the way it suggests.</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>I've found some other questions about alike situation and I still couldn't find a fix for that.
  Can anyone help me?</p>
  
  <p>Thanks very much.</p>
  
  <p>Here is relevant code:</p>
  
  <p>User.java:</p>
  
  <pre><code>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;
      }
  }
  </code></pre>
  
  <p>User.hbm.xml:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt;
  &lt;hibernate-mapping package="domain"&gt;
      &lt;class name="User" table="user"&gt;
          &lt;id
              column="username"
              name="userName"
              type="string"&gt;
              &lt;generator class="assigned"/&gt;
          &lt;/id&gt;
          &lt;property
              column="password"
              name="password"
              not-null="true"
              type="string"/&gt;
          &lt;property column="blocked" name="blocked" type="boolean"/&gt;
          &lt;many-to-one
              name="userClass"
              column="user_class_id"
              class="UserClass"
              not-null="true"/&gt;
      &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>UserClass.java</p>
  
  <pre><code>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);
      }
  }
  </code></pre>
  
  <p>UserClass.hbm.xml:</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt;
  &lt;hibernate-mapping package="domain"&gt;
      &lt;class
          name="UserClass"
          table="userclass"&gt;
          &lt;id
              column="id"
              name="id"&gt;
              &lt;generator class="native"/&gt;
          &lt;/id&gt;
          &lt;property
              column="title"
              name="title"
              not-null="true"
              type="string"/&gt;
          &lt;property
              column="permissions"
              name="permissions"
              not-null="true"
              type="string"/&gt;
          &lt;set
              name="users"
              inverse="true"
              cascade="all-delete-orphan"&gt;
              &lt;key column="user_class_id"/&gt;
              &lt;one-to-many class="User"/&gt;
          &lt;/set&gt;
      &lt;/class&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>DatabaseFactory.java:</p>
  
  <pre><code>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;
      }
  }
  </code></pre>
  
  <p>Main.java (test class):</p>
  
  <pre><code>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();
      }
  }
  </code></pre>
  
  <p>SQL Script to create DB (generated by MySQL Workbench):</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>CONSTRAINT `fk_User_UserClass1`
      FOREIGN KEY (`user_class_id` )
      REFERENCES `LojaTest`.`UserClass` (`id` )
      ON DELETE CASCADE
      ON UPDATE NO ACTION
  </code></pre>
  
  <p>And</p>
  
  <pre><code>CONSTRAINT `fk_User_UserClass1`
      FOREIGN KEY (`user_class_id` )
      REFERENCES `LojaTest`.`UserClass` (`id` )
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
  </code></pre>
  
  <p>None of then worked.</p>
  
  <p>Thanks again.</p>