[jira] [Issue Comment Edited] (OPENJPA-1936) A database 'FOREIGN KEY' constraint is not detected by default

["Heath Thomann (Issue Comment Edited) (JIRA)" <ji...@apache.org>]

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

Heath Thomann edited comment on OPENJPA-1936 at 1/11/12 5:43 PM:
-----------------------------------------------------------------

I wanted to add a third option to the other two options mentioned in my description as I realize that adding the SchemaFactory property to each persistent units/persistence.xml files might be a pain.  As such, I wanted to offer another option which will allow a user to set the property globally.  That is, OpenJPA offers an optional resource file named openjpa.xml to set configuration properties, as described here: http://openjpa.apache.org/builds/2.0.0/apache-openjpa-2.0.0/docs/manual/manual.html#ref_guide_conf_specify.  An example of the contents of the openjpa.xml file is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="1.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">

       <persistence-unit name="">
           <properties>
               <property name="openjpa.jdbc.SchemaFactory" value="native(ForeignKeys=true)"/>
           </properties>
       </persistence-unit>
</persistence>

Notice that the persistence-unit name is blank.  This is expected, the name isn't actually used.  As the OpenJPA document listed above points out, this file needs to be on the CLASSPATH.  As an example (but not limited to), a user can place this file in the same location as the persistence.xml file.  Or, a user can place the file in a .jar file and place the .jar file in the lib directory of an ear.

Thanks,

Heath
                
      was (Author: jpaheath):
    I wanted to add a third option to the other two options mentioned in my description as I realize that adding the SchemaFactory property to each persistent units/persistence.xml files might be a pain.  As such, I wanted to offer another option which will allow a user to set the property globally.  That is, OpenJPA offers an optional resource file named openjpa.xml to set configuration properties, as described here: http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_conf_specify.  An example of the contents of the openjpa.xml file is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="1.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">

       <persistence-unit name="">
           <properties>
               <property name="openjpa.jdbc.SchemaFactory" value="native(ForeignKeys=true)"/>
           </properties>
       </persistence-unit>
</persistence>

Notice that the persistence-unit name is blank.  This is expected, the name isn't actually used.  As the OpenJPA document listed above points out, this file needs to be on the CLASSPATH.  As an example (but not limited to), a user can place this file in the same location as the persistence.xml file.  Or, a user can place the file in a .jar file and place the .jar file in the lib directory of an ear.

Thanks,

Heath
                  
> A database 'FOREIGN KEY' constraint is not detected by default
> --------------------------------------------------------------
>
>                 Key: OPENJPA-1936
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-1936
>             Project: OpenJPA
>          Issue Type: Improvement
>    Affects Versions: 2.0.1
>            Reporter: Heath Thomann
>            Assignee: Heath Thomann
>            Priority: Minor
>
> Take the following SQL to create two tables in a database:
> CREATE TABLE PARENT (
>     "ID" INT NOT NULL,
>     PRIMARY KEY ("ID")
>   )
>   
> CREATE TABLE CHILD (
>     "ID" INT NOT NULL,
>     "PARENT_ID" INT,
>     PRIMARY KEY ("ID"),
>     FOREIGN KEY ("PARENT_ID") REFERENCES "PARENT" ("ID")
>   )
> Take the following two entities:
> public class Parent implements Serializable {
>   @Id
>   private int id;
>   @OneToMany(mappedBy = "parent", cascade = CascadeType.ALL, fetch = FetchType.EAGER, orphanRemoval = true)
>   private Collection<Child> childs;
> .........
> public class Child implements Serializable {
>   @Id
>   private int id;
>   @ManyToOne
>   private Parent parent;
> .........
> If a scenario is executed in which an existing Parent is removed, an existing Child(s) associated with the Parent will also be removed given the definition of the @OneToMany relationship.  However, when OpenJPA executes the SQL to remove the Parent and Child, the SQL to remove the Parent will be executed first.  Given the 'FOREIGN KEY' constraint on the Child table, a database will throw some kind of 'constraint violation' exception when a Parent is removed before its Child (if it were not for the 'FOREIGN KEY' constraint on the Child table, the SQL order would be fine).  In this case, OpenJPA should execute the SQL to remove the Child first, then the Parent.  However, by default, OpenJPA knows nothing about the 'FOREIGN KEY' constraint on the Child table and OpenJPA never assumes that there are database constraints for relationships.  As a result, OpenJPA does not  take them into account when executing SQL.  To tell OpenJPA that there are database level constraints, and thus to effect the order of the SQL in this case, a user can perform one of the following options:
> 1) Use the @ForeignKey annotation (org.apache.openjpa.persistence.jdbc.ForeignKey) in entities (on the ToOne fields).
> 2) Have OpenJPA read in the table definitions from the database by adding the following property:
>   <property name="openjpa.jdbc.SchemaFactory" value="native(ForeignKeys=true)"/>
> While either of these two options will properly handle the above scenario, it can be argued that OpenJPA should detect the 'FOREIGN KEY' constraint, and not require a user to add an annotation to their code or set a property.  This JIRA will be used to investigate possible solutions to change the way the constraints are detected.
> Thanks,
> Heath

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira