[jira] [Commented] (OPENJPA-2270) Wrong SQL reordering on DELETE causing foreign key constraint violations if relationships are nullified

["Gunnar Grim (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-2270?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13820061#comment-13820061 ] 

Gunnar Grim commented on OPENJPA-2270:
--------------------------------------

I think this bug report is a bit too narrow. In my experience, cascade delete will never work when foreign keys are used in the database. OpenJPA seems to always delete the referenced (parent) row before the referencing (child).

We have an application will several cascade delete relationships. It works fine in GlassFish and JBoss but not in Geronimo. We don't want to use OpenJPA-specific annotations since it must be portable.

> Wrong SQL reordering on DELETE causing foreign key constraint violations if relationships are nullified
> -------------------------------------------------------------------------------------------------------
>
>                 Key: OPENJPA-2270
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2270
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: kernel
>    Affects Versions: 2.1.1, 2.2.0
>            Reporter: Givanildo Dantas Alves
>
> A Parent has a bi-directional one-to-many relationship to Child. A foreign key is added to ensure consistency between PARENT and CHILD tables
> @Entity
> public class Parent {
>     @OneToMany(mappedBy = "parent", cascade = CascadeType.ALL)
>     private Set<Child> childList = new HashSet<Child>(); 
> ...
> }
> @Entity
> public class Child {
>     @ManyToOne
>     @JoinColumn(name="PARENT_ID", nullable=false)
>     @ForeignKey(deleteAction=ForeignKeyAction.RESTRICT)
>     private Parent parent;
> ...
> }
> For a given Parent instance previously persisted in the database, I nullify the relationships to its associated Child instances (i.e remove all Child's from Parent collection and have those Child's no longer point to any Parent) and call EntityManager API to remove the Parent and the Child's
> for (Child c : parent.getChildren()) {
>        parent.removeChild(c);
>        entityManager.remove(c);
> }
> entityManager.remove(parent);
> However, OpenJPA attempts to execute the SQL DELETE statement for PARENT table prior to deleting the CHILD records, which causes a foreign key constraint violation:
> DB2 SQL Error: SQLCODE=-532, SQLSTATE=23504, SQLERRMC=ADMINISTRATOR.CHILD.SQL120927163610030, DRIVER=3.61.75 {prepstmnt 1505188279 DELETE FROM ADMINISTRATOR.Parent WHERE PARENT_ID = ? [params=(long) 301]} [code=-532, state=23504]SQLCA OUTPUT[Errp=SQLRI079, Errd=-2145779603, 0, 0, 0, -95, 0]
> DB2 SQL Error: SQLCODE=-532, SQLSTATE=23504, SQLERRMC=ADMINISTRATOR.CHILD.SQL120927163610030, DRIVER=3.61.75
> FailedObject: com.ibm.dis.api.Parent-301
> The error happens regardless of the value configured in openjpa.jdbc.UpdateManager property
> I can only make deletion work fine if the relationships are not nullified



--
This message was sent by Atlassian JIRA
(v6.1#6144)