[jira] [Updated] (OPENJPA-2795) generate foreign key indexes for Oracle

["Robert Mayer (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/OPENJPA-2795?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Robert Mayer updated OPENJPA-2795:
----------------------------------
    Attachment: OPENJPA-2795.patch

> generate foreign key indexes for Oracle
> ---------------------------------------
>
>                 Key: OPENJPA-2795
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-2795
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc
>    Affects Versions: 2.4.3, 3.1.0
>            Reporter: Mark Struberg
>            Assignee: Mark Struberg
>            Priority: Major
>         Attachments: OPENJPA-2795.patch
>
>
> When generating a schema SQL via OpenJPA then we do omit the CREATE INDEX if there is a Forein Key CONSTRAINT generated.
> For the following please consider an Entity {{CarLicensePlate}} which has a {{@ManyToOne}} on {{Customer}}.
> Without giving any further generator options we generate the correct CREATE INDEX statement:
> {noformat}
> CREATE TABLE CUSTOMER (ID NUMBER NOT NULL, active VARCHAR(1), name VARCHAR2(255), PRIMARY KEY (ID));
> CREATE TABLE LIC_PLATE (id VARCHAR2(255) NOT NULL, maker VARCHAR2(255), model VARCHAR2(255), optLock NUMBER, CUSTOMER_ID NUMBER, PRIMARY KEY (id));
> CREATE INDEX I_LIC_PLT_CUSTOMER ON LIC_PLATE (CUSTOMER_ID);
> {noformat}
> But once we switch on explicit foreign key constraints via 
> {noformat}
> <MappingDefaults>ForeignKeyDeleteAction=restrict, JoinForeignKeyDeleteAction=restrict</MappingDefaults>
> {noformat}
> then the index is omitted.
> {noformat}
> CREATE TABLE CUSTOMER (ID NUMBER NOT NULL, active VARCHAR(1), name VARCHAR2(255), PRIMARY KEY (ID));
> CREATE TABLE LIC_PLATE (id VARCHAR2(255) NOT NULL, maker VARCHAR2(255), model VARCHAR2(255), optLock NUMBER, CUSTOMER_ID NUMBER, PRIMARY KEY (id));
> ALTER TABLE LIC_PLATE ADD FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (ID) DEFERRABLE;
> {noformat}
> This is ok for most databases as a foreign key constraint they usually automatically internally create an index as well. But sadly this is not the case for Oracle. Here we would actually need both, the constraint and the index.
> There is even an own switch {{IndexLogicalForeignKeys}} in {{MappingDefaults}}. But this doesn't really do what we need right now in this case. It's more a switch to disable index creating even if there is no constraints.
> We could either introduce a new flag in MappingDefaults or change the IndexLogicalForeignKeys from boolean to Boolean or even an enum with a {{FORCED}}.
> Even better would be a DbDictionary specific handling. That way we could create the index automatically for Oracle while skipping it for others.



--
This message was sent by Atlassian Jira
(v8.3.4#803005)