investigate cascade and inverse in deleting many-to-many relation(Hibernate)

stewchicken

cascade and inverse in deleting many-to-many relation

Purpose:
Try to see Hibernate’s behavior when try to delete Person instance based on different
cascade and inverse ‘s combination in Person.hbm.xml


Table:
Person ,   Department , PersonDepartment.

Relation:
Person: PersonDepartment is 1:n
Department: PersonDepartment is 1:n
Person: Department is m:n

Hibernate class:
Person. java
Department.java

Hibernate mapping file:

Person.hbm.xml
Department.xml

Testing DB:
Mysql
Postgre


Testing Data Population:

Person person = new Person();
Department depart = new Department();

person.setFirstname("san");
person.setLastname("zhang");
person.setPersonid(1);
depart.setDepartmentid(10);
depart.setDepartmentname("IT");
person.setDepartments(new HashSet());
person.getDepartments().add(depart);

session.save(depart);
session.save(person);


Deleting Person instance

After testing data population, deleting testing data by following way

Person p = (Person) session.load(Person.class, 1)
session.delete(p);
                       

1)Deleting Person instance with first cascade and inverse combination case .

<set name="departments" table="persondepartment" lazy="true"
                        cascade="none" inverse="false">
                        <key>
                                <column name="personid" />
                        </key>
                        <many-to-many column="departmentid"
                                class="org.hibernate.test.Department" />
                </set>
Result:
Deleting Person will be successful on both mysql and postgre DB.

SQL output on Mysql:
11:17:45,036 DEBUG SQL:401 - delete from persondepartment where personid=?
Hibernate: delete from persondepartment where personid=?
11:17:45,036 DEBUG SQL:401 - delete from person where personid=?
Hibernate: delete from person where personid=?
Conclusion:
Above setting  is the best solution for many to many (person: department) because of 2 reasons
a) when we delete person. we dont want to delete department, only breaking linking by deleting persondepartment is enough
b) this solution works on mysql and postgre both


2) Deleting Person instance with second cascade and inverse combination case

<set name="departments" table="persondepartment" lazy="true"
                        cascade="all" inverse="false">
                        <key>
                                <column name="personid" />
                        </key>
                        <many-to-many column="departmentid"
                                class="org.hibernate.test.Department" />
                </set>
Result:
Deleting Person will be successful on mysql and fail on postgre.

SQL output on mysql
11:28:00,868 DEBUG SQL:401 - delete from persondepartment where personid=?
11:28:00,868 DEBUG SQL:401 - delete from department where departmentid=?
11:28:00,868 DEBUG SQL:401 - delete from person where personid=?

Conclusion:
Above setting is not good solution for many to many (person: department),
it will try to delete persondepartment, delete department then delete person,
it works for mysql db but fail on postgre db,
It looks like in mysql deleting sequence is correct, it first deletes middletable, then department,  finally person.
mysql can solve deleting sequence correctly,
but on postgre it will fail, because it could not solving deleting sequence correctly.
on postgre,it will first delete department, but department is still refered by child persondepartment table,

I think reason is different db has different interpretation for hibernate's statement.

3) Deleting Person instance with third cascade and inverse combination case

<!-- bi-directional many-to-many association to department -->
                <set name="departments" table="persondepartment" lazy="true"
                        cascade="all" inverse="true">
                        <key>
                                <column name="personid" />
                        </key>
                        <many-to-many column="departmentid"

Result:
Deleting Person will fail  on both mysql and postgre.
Exception logtrace based on mysql DB

Duplicate key or integrity constraint violation,  message from server: "Cannot delete or update a parent row: a foreign key constraint fails (`test/persondepartment`, CONSTRAINT `FK_persondepartment_1` FOREIGN KEY (`departmentid`) REFERENCES `department` (`departmentid`))"
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
                                class="org.hibernate.test.Department" />
                </set>

Conclusion:
It will definately fail, because inverse = true, deleting on persondepartment will never take action, when person is deleted. and cascade is set as all, means when deleting person, it will firstly delete department, but department is referred by persondepartment , which could
not be deleted because of inverse = true, so it will definitely fail on both mysql and postgre.

SQL statement used to create test case on mysql

DROP TABLE IF EXISTS `test`.`department`;
CREATE TABLE  `test`.`department` (
  `departmentid` int(10) unsigned NOT NULL,
  `departmentname` varchar(255) NOT NULL,
  PRIMARY KEY  (`departmentid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `test`.`person`;
CREATE TABLE  `test`.`person` (
  `personid` int(10) unsigned NOT NULL default '0',
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  PRIMARY KEY  (`personid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `test`.`persondepartment`;
CREATE TABLE  `test`.`persondepartment` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `departmentid` int(10) unsigned NOT NULL,
  `personid` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `FK_persondepartment_1` (`departmentid`),
  KEY `FK_persondepartment_2` (`personid`),
  CONSTRAINT `FK_persondepartment_2` FOREIGN KEY (`personid`) REFERENCES `person` (`personid`),
  CONSTRAINT `FK_persondepartment_1` FOREIGN KEY (`departmentid`) REFERENCES `department` (`departmentid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;