[
https://issues.apache.org/jira/browse/OPENJPA-2795?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17187669#comment-17187669
]
Mark Struberg commented on OPENJPA-2795:
----------------------------------------
Hi Robert! looks quite good. Do you mind shipping a patch with {{git
format-patch}}? That way we can preserve your user info. txs!
> 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)
