Incorrect foreign key constraint shown on error

dfielder · **Joined:** Sat Jan 07, 2006 5:18 pm **Posts:** 19

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
Hibernate 3.1.1
Hibernate Tools 3.1beta2
Hibernate Annotations 3.1beta8

Mapping documents: Annotations

Full stack trace of any exception that occurs:

Code:

org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:69)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:202)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:230)
   at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:145)
   at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:296)
   at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
   at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1007)
   at org.springframework.orm.hibernate3.HibernateTemplate$25.doInHibernate(HibernateTemplate.java:767)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:365)
   at org.springframework.orm.hibernate3.HibernateTemplate.flush(HibernateTemplate.java:765)
   at com.compudata.daf.HibernateFlusher.flush(HibernateFlusher.java:18)
   at com.compudata.daf.GenericIntegrationTests.flush(GenericIntegrationTests.java:25)
   at com.compudata.daf.business.EnvironmentManagerTests.testDeleteHierarchyType(EnvironmentManagerTests.java:606)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:585)
   at junit.framework.TestCase.runTest(TestCase.java:154)
   at junit.framework.TestCase.runBare(TestCase.java:127)
   at junit.framework.TestResult$1.protect(TestResult.java:106)
   at junit.framework.TestResult.runProtected(TestResult.java:124)
   at junit.framework.TestResult.run(TestResult.java:109)
   at junit.framework.TestCase.run(TestCase.java:118)
   at junit.framework.TestSuite.runTest(TestSuite.java:208)
   at junit.framework.TestSuite.run(TestSuite.java:203)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
   at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.BatchUpdateException: Duplicate key or integrity constraint violation message from server: "Cannot delete or update a parent row: a foreign key constraint fails (`springdaftest/hierarchytype`, CONSTRAINT `FK612BA2EFC86ABCFF` FOREIGN KEY (`parent`) REFERENCES `hierarchytype` (`id`))"
   at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1540)
   at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:294)
   at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:58)
   at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:195)
   ... 26 more

Name and version of the database you are using: MySQL 5.0

I've got a class that uses Annotations to define the mappings. It has a self-contained one-to-many relationship defining a parent and list of children. It also has a one-to-many relationship with another class, Hierarchy, that has a foreign key constraint on the HierarchyType id field.

When I do a delete cascade of the children in HierarchyType, it should fail because there is no cascade defined for the usedInHierarchies relationship. The problem, if it is a problem, is that the constraint shown in the exception isn't the root cause and is misleading.

What appears to be happening is that the children relationship is cascaded, at which point one of the children can't be deleted because a instance of Hierarchy references it in its hierarchyType property. However, this foreign key isn't reported as the cause of the problem, rather it's the foreign key internal to the HierarchyType table that is shown.

Is this just the behavior of the database or is there some better way to show the root cause of a constraint exception?

Here's the code for the HierarchyType class:

Code:

@Entity
public class HierarchyType extends DAFEntityNamed
{
    private List<HierarchyType> children = null;

    private HierarchyType parent = null;

    private List<Hierarchy> usedInHierarchies = null;

    /**
     * @return Returns the children.
     */
    @OneToMany(mappedBy = "parent")
    @OrderBy("name")
    @Cascade( { CascadeType.ALL, CascadeType.DELETE_ORPHAN })
    public List<HierarchyType> getChildren()
    {
        return children;
    }

    /**
     * @return Returns the parent.
     */
    @ManyToOne
    @JoinColumn(name = "parent")
    public HierarchyType getParent()
    {
        return parent;
    }

    /**
     * @return Returns the usedInHierarchies.
     */
    @OneToMany(mappedBy = "hierarchyType")
    public List<Hierarchy> getUsedInHierarchies()
    {
        return usedInHierarchies;
    }

    /**
     * @param children The children to set.
     */
    public void setChildren(List<HierarchyType> children)
    {
        this.children = children;
    }

    /**
     * @param parent The parent to set.
     */
    public void setParent(HierarchyType parent)
    {
        this.parent = parent;
    }

    /**
     * @param usedInHierarchies The usedInHierarchies to set.
     */
    public void setUsedInHierarchies(List<Hierarchy> usedInHierarchies)
    {
        this.usedInHierarchies = usedInHierarchies;
    }
}

Here's the SQL generated by hbm2ddl that defines the tables Hierarchy and HierarchyType and the constraints - as you can see by the constraint code 'FK612BA2EFC86ABCFF' in the exception stack trace, the constraint that is failing isn't on the Hierarchy table where the error actually comes from but on the HierarchyType table.

Code:

create table Hierarchy (id integer not null auto_increment, name varchar(255) not null, repository integer, hierarchyType integer, parent integer, primary key (id), unique (name, parent)) type=InnoDB;
create table HierarchyType (id integer not null auto_increment, name varchar(255) not null, parent integer, primary key (id)) type=InnoDB;
alter table Hierarchy add index FKF584B215355AB344 (hierarchyType), add constraint FKF584B215355AB344 foreign key (hierarchyType) references HierarchyType (id);
alter table HierarchyType add index FK612BA2EFC86ABCFF (parent), add constraint FK612BA2EFC86ABCFF foreign key (parent) references HierarchyType (id);

dfielder · **Joined:** Sat Jan 07, 2006 5:18 pm **Posts:** 19

On a related note, what cascade would I use on the usedInHierarchies property in HierarchyType to make it set the hierarchyType references in Hierarchy to null?

emmanuel · **Posted:** Wed Jan 25, 2006 3:15 pm

Every time Hibernate see Spring and DBCP at the same time in the stacktrace, it switch FK names to confuse user even more, lol
Seriously, this is DB related.

dfielder · **Joined:** Sat Jan 07, 2006 5:18 pm **Posts:** 19

It's a conspiracy!!! Seriously, though, thanks for the response. I figured it was since I see similar messages when hitting key constraints via a MySQL administration program, but wanted to make sure. It's not too hard to figure out if you're only dealing with one or two constraints, but this could be seriously hard to debug if you have a very complex DOM with nested constraints several levels deep.

Regarding my other question, is there some cascadetype that will set manytoone references to null? Right now, I iterate through the usedInHierarchies list and see all Hierarchy.hierarchyType references to null manually - didn't know if there was some better way to cascade that operation. Isn't that just a standard ON DELETE CASCADE SET NULL statement in MySQL?

Also, kind of odd that the @NotNull syntax doesn't work on manytoone relationships - instead you have to use the @JoinColumn(nullable=false) syntax. Is this documented somewhere?

emmanuel · **Posted:** Wed Jan 25, 2006 9:06 pm

no there is not way to nullify many to one by cascade.
What you can do is either use update ...where in HQL or try the @OnDelete
annotaiton to see if it work on MySQL (I can't remember)

@NotNull should work, open a JIRA issue.

dfielder · **Joined:** Sat Jan 07, 2006 5:18 pm **Posts:** 19

Setting @OnDelete(action=OnDeleteAction.CASCADE) on the many-to-one side of the relationship doesn't appear to work. I ended up just putting a prepForDelete() method on my base domain entity that each domain member has to implement. That method removes any associations between the entity to be deleted and the one-to-many side, which solves the problem cleanly.

dfielder · **Joined:** Sat Jan 07, 2006 5:18 pm **Posts:** 19

Ok, I opened a JIRA topic regarding the @NotNull syntax - go easy on me if there are errors, as it's my first bug to report.

http://opensource2.atlassian.com/projects/hibernate/browse/ANN-234